Performance hit of nested Oracle views

I am creating a .NET application running off of a Oracle 9.2 database.  I would like to give the users the ability to create views, based either on tables or existing views, through this program.  I have read a bit about the possible performance hits of nesting views and I'm interested in any insight.

Thanks.
LVL 3
mistaeverlyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mark GeerlingsDatabase AdministratorCommented:
The performance hit of nesting views is one thing (and usually not so bad).  The performance hit from ad-hoc views that users can create on-the-fly may be *HUGE*, whether they are nested or not!  If you have no controls on the type of views that get created, then you have no way to predict the performance impact.  There are two big performance issues with ad-hoc views:
1. the parsing overhead for these unique SQL statements
2. the fact that many of these view could be based on non-indexed columns

If you want predictable performance, you will have to control the application, that is: control the SQL statements that get created and executed.  If you want to maximize flexibility though, and give users the ability to create views on-the-fly, forget about predictable performance!

Sorry, but you can't get maximum performance plus maximum flexibility from the same application/database.
0
sathyagiriCommented:
If the views that the users will create is going to be temporary u wd be okay.

But if it's going to be retained in the database, then you might run into some nested views which might have some complex joins and unions after a while which may not make any sense.
0
actonwangCommented:
Views are essentially sqls. The reason why nested views create a performance hit is NOT because "NESTED" itself that because nested view will easily transfer to more queries with bad performance if they are introduced to generate other ad-hoc queries.

You need to think about advantange and disadvange before you use a vew:

view would:
1.You would expose a subset of data via view.
2.To hide complex query and let user focus on the meaning of view
3.maybe provde more meaningful names for columns
4.achieve some abstraction

but in the same time some inappropriate use of views would make a query slow and hard to tune.

Acton



0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mistaeverlyAuthor Commented:
Thanks guys.  I plan on restricting what views are created through some to-be-created interface.  Hopefully I can use this to allow the flexibility of nested views while forcing intelligently-constructed joins.  Easier said than done, I'm sure.  

Some of the things I read on the web made it seem that there was some inherent performance hit with even well-constructed views, so I appreciate the explanations.

Chad
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.