Re: Views - performance

On 07.09.2006 08:02, lucky wrote:
> Jeff
> according to me using inline view is more faster in terms of
> performance than using stored object views
> when u use stored object views oracle has to read the data dictionary
> to find out the existence as well as the validity of the view then it
> will search the dictionary for the syntax(query) of the view .it will
> then execute the query
> this includes lots of steps for execution
> whereas in inline views oracle just has to check to syntax and
> semantics of the query and fires it
> moreover for debugging the query you will have to query user_views for
> all the views in the query and then combine them with the original
> query

While this sounds plausible I'd like to question that. For example, if you use statements with bind variables this overhead is paid only during hard parsing. This will be rather seldom depending on the usage pattern. Even in other circumstances Oracle might detect similarity of statements and reuse the plan.

> so it also becomes a headache to debug
> hence i would suggest you to go for inline views

*I* get a headache when I have to debug complex select statements containing multiple inline views. While there is usage for them I don't subscribe to the general rule of transforming views into inline views. Also, with views you can more easily "extract" parts of a query and test them independently.

One major advantage of views is that they can abstract away a certain concept / data set which can be reused in multiple places. Also you can decouple queries from the table layout.

A disadvantage of views which I sometimes encounter is the fact that a join in the view will always be performed - even if you do not query columns from that table. This can sometimes decrease performance if the join does not change the set of data from the other table (e.g. if there is a n:1 relationship between a fact table and a dimension table in DWH applications).

Kind regards

        robert Received on Thu Sep 07 2006 - 02:35:53 CDT