Re: Views - performance

Robert Klemme wrote:
> On 07.09.2006 08:02, lucky wrote:
> > Jeff
> > according to me (...)
>
> 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.

Subscribed!! I hate nothing more than 4 page long queries ... brrrr :)

> Also, with views you can more easily "extract" parts of a query and test
> them independently.
>

You name it.

> 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).
>

I was under the impression that the xplan Oracle comes up with will be _exactly_ the same regardless of having a view or an inline view (of course only if both i-v. an v. are exactly the same) ... ?

best,
Martin Received on Thu Sep 07 2006 - 04:15:23 CDT