Re: Q: efficiency of clause in view vs select

Since the answer can be instance specific ( depending on optimizer settings, db_block buffers, etc) you can test it by creating the views and then running your query after using the
set autotrace on
command in SqlPlus which will return the results and show you the access methods and timings . These should give you the data for a decision as to the way to go. ( You should analyse the tables first to be sure the optimizer has the needed info) hth,  

yf110_at_vtn1.victoria.tc.ca (Malcolm Dew-Jones) wrote:

>
>(in SQL &_O_RELEASE gives version 801070101 )
>
>Am am going to create a view and then select some data from the view
>filtered on two columns. I am considering the option of putting one of
>the column where clauses in the view, and wondering if I should anticipate
>it making any difference to the efficiency of the final query.
>
>
>e.g. -1-
>
> create view V1 as
> select * from table1,table2
> where table1.x=table2.x ;
>
> -- final select
> select * from V1 where col1 = 'A' and col2 = 'B';
>
>e.g. -2-
>
> create view V2 as
> select * from table1,table2
> where table1.x=table2.x
> and col1 = 'A';
>
> -- final select
> select * from V2 where col2 = 'B';
>
>
>Both final select statements get the same data from the same underlying
>tables, but can I assume that the two techniques will be about equal in
>terms of efficiency? I can see that my two final queries _might_ end up
>being identical in terms of what oracle does to get the data, but on the
>other hand I do not know this aspect of oracle in depth so perhaps there's
>some hidden gotcha that I have yet to learn.

-----------== Posted via Newsfeed.Com - Uncensored Usenet News ==----------

   http://www.newsfeed.com The #1 Newsgroup Service in the World! -----= Over 100,000 Newsgroups - Unlimited Fast Downloads - 19 Servers =----- Received on Mon Aug 19 2002 - 08:32:22 CDT