Re: Optimzer Oddities

If you want someone to predict what the
execution path will be, then you need to supply a lot more information. If you just want a clue about how the statement could be "legal" or "manageable" -

Join A to B on emplid, recording the values for a.name, b.location, a.effdt, a.emplid b.start_time, a.sal where a.sal > 1000

For each row in the join result, query C for:

    max(effdt)
    where c.emplid = {constant 1}
    and c.start_time > {constant 2}

except that constant 1 is the a.emplid
from the join row and constant 2 is
b.startime from the join.

If the result of the subquery matches the a.effdt from the join row, report the join row.

Of course, Oracle may rewrite the query to look completely different, and the mechanics may therefore be nothing like my description - but logically, what I've described is what has to happen.

-- 
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

April 2004 Iceland  http://www.index.is/oracleday.php
June  2004      UK - Optimising Oracle Seminar
July 2004 USA West Coast, Optimising Oracle Seminar
August 2004 Charlotte NC, Optimising Oracle Seminar
September 2004 USA East Coast, Optimising Oracle Seminar
September2004 UK - Optimising Oracle Seminar

"Matt" <mccmx_at_hotmail.com> wrote in message
news:cfee5bcf.0404290037.674304ae_at_posting.google.com...


> I have a correlated subquery which joins 3 tables together and I'm not

> exactly sure how Oracle will resolve the query...

>

> For example...

>

> select a.name, b.location

> from emp a, contract b

> where a.emplid = b.emplid

> and a.effdt =

> ( select max(effdt) from emp c

> where c.emplid = a.emplid

> and c.start_time > b.start_time )

> and a.sal > 10000;

>

> Don't worry about the logic of the statement (its a quick example to

> illustrate). My question just relates to whether or not a three table

> combination is a valid path, and what Oracle will do to resolve the

> query in this situation.

>

> In a 'normal' correlated subquery the optimizer will run the sub once

> for every row returned in the parent... However with the extra join

> above (i.e. C > B), its not as simple as that.

>

> Any comments welcome...

>

> Thanks, Matt