Optimzer Oddities

Giganews Newsgroups
Subject: Optimzer Oddities
Posted by:  Matt (mcc…@hotmail.com)
Date: 29 Apr 2004

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