Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: view based on view
Hi Daud,
In my experience, having views based on other views can be a bad thing to do.
Bad in terms of :
1) Speed - The underlying view needs to be processed completely before it
can be
used as a basis for the higher view.
2) Labour intensive - Every time I try to do something like this, a simple
structure query which you would assume would be simple to get data for
actually ends up hogging resources and slowing the machine down. (If
someone knows how to limit CPU usage per session within Oracle then please
tell me).
With most views you can get pretty fast results if you can use limiting
criteria. The problem with using underlying views is that you can't pass
the limiting criteria from the
upper view to the lower view. So the lower view still needs to be fully
processed.
Even with optimizer hints and the like, you can still get slow, resource hungry queries.
If you must do this, then I would try to break the sub views into simple views (ones that don't need to do too much). And use optimizer hints where ever possible, sometimes they do make a difference.
Hope this helps.
Paul
daud11_at_hotmail.com wrote:
> Hi
>
> I am thinking of creating a view based on another view. On top of that
> I still need to create another view. My question is what impact does it
> have in terms of performance when I have this kind of many "nested"
> views.
>
> Regards
> Daud
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Mon Oct 04 1999 - 17:17:23 CDT