|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...
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...