Re: Question about efficiency of Views

From: WolfAlpha <wolfalpha_spamguard_at_home.com>
Date: Wed, 07 Jul 1999 22:09:39 GMT
Message-ID: <DeQg3.12780$5i7.7788@news.rdc1.va.home.com>

Generally the optimizer is smart enough to parse the statement in a single pass. Performance usually isn't a problem as long as one of your statements is properly tuned. The only real time you see performance problems with views is when you imbed views--select from a view that is a select from a view that is a select from another view (and sometimes views that access tables across a database link can sometimes be a headache). One other thing with views is that if you want to use a specific OPTIMIZER HINT with it, you would have to modify the view (using the hint in the SQL that goes against the view has no effect). If you need to be able to use different hints on a view, I would suggest using an in-line view(in case you're not familiar, here's a quick rundown):

select vw.table
from (select distinct table_name "table"

          from all_tab_columns
          where owner = '????') vw,
          all_tables

where all_tables.table_name = vw.table;

Where the statement in the parenthesis in the FROM clause is acting exactly as a view would. In this format, you can add optimizer hints as needed (but you loose the ability to change the VIEW from a single point). I hope some of this helps.

Jeff S

PMG <peteg_at_garlic.com> wrote in message news:3782E072.49372FE1_at_garlic.com...
> Hi there,
>
> Suppose I have created a view, and then perform a select statement
> against the view, with some kind of a WHERE clause.
>
> My question is this: does the Oracle engine first perform the entire
> query as delineated in the view script, and then perform a sub-select
> from the view as indicated by the WHERE clause, or is the engine smart
> enough to substitue the WHERE condition immediately?
>
> IOW, is there one pass or two passes at the data? Or are they equivalent
> in terms of efficiency?
>
> TIA
>
> Pete
>
Received on Wed Jul 07 1999 - 17:09:39 CDT