Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: view based on view

Re: view based on view

From: P Jeromel <pjeromel_at_hotmail.com>
Date: Tue, 05 Oct 1999 07:47:23 +0930
Message-ID: <37F92772.BB49E735@hotmail.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US