Re: in-line view

Cobra__77 wrote:
>
> If I use in-lines views....and then join those views later in the where
> clause...will oracle create the inline view once for each join...or just
> once for all joins....ie perform the joins and store data for that view in
> memory....
>
> eg
>
> select a.x, b.y, c.z
> from (select id,x
> from tab1
> where id > 100) a,
> (select id,y
> from tab2
> where id > 1000) b,
> (select id,z
> from tab3
> where id > 30) c
> where a.id = b.id and a.id = c.id
>
> for the above joins will oracle just create 'a' once and store data in
> memory and then use it for both joins...
> a.id = b.id and a.id = c.id ...or create 'a' twice ...for both joins...to do
> a.id = b.id and do the other part a.id = c.id ....
> Please remember that the above example is very simplified....my query is
> very very complex than the above...and I have tables with more than 1000000
> rows...in each in-line views...so determining this will help me a lot in
> tuning my queries.....
>
> any suggestions....

Depends...Oracle can merge various bits and pieces of the SQL and thus not need to run them multiple times. Typically if the inline needs to be resolved first eg grouping/distinct etc, then you may be out of luck.

HTH

-- 
===========================================
Connor McDonald
http://www.oracledba.co.uk

We are born naked, wet and hungry...then things get worse