Optimzer Oddities

From: Matt <mccmx_at_hotmail.com>
Date: 29 Apr 2004 01:37:01 -0700
Message-ID: <cfee5bcf.0404290037.674304ae@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 Received on Thu Apr 29 2004 - 03:37:01 CDT