Re: Tuning views

Anjalee_at_my-dejanews.com wrote:

> Hi !
> I've been reading a book on tuning _Oracle Performance Tuning_ ,Gurry &
> Corrigan where its mentioned that one should avoid specifying views of views
> .I've been trying to get some information on how exactly Oracle handles views
> and how a view on a view will be detrimental to performance...but no luck !
> Would somebody be nice and tell ?
>
> -Anjali

I've been writing views of views of views and never seen much performance impact above the extra parse time (usually less than a second). If you query is likely to last 10s or more then the impact is comparatively small. If, however,
your query is in a TP environment and needs to run 100s of times a second than an extra 1s to parse is way overboard !

You pays your money and takes your choice.

The way that Oracle manages views is that it merges the view (or views) into a single execution plan with you SQL that selects from the view. This may result in an execution plan that bears no resemblance to the view at all. On the other hand if your view does a group by or such like, then the view may remain intact in the execution plan. Generally, as the optimiser improves through Oracle versions
it is more likely that the view get re-worked.

In some ways the view can be thought of as a simple macro which oracle inserts into a query at run-time and then optimises the result. (i know this isn't strictly true, but it's near enough for most purposes)

Simon. Received on Thu Jun 25 1998 - 17:07:58 CDT