Re: Another Outer Join Question

Alan D. Mills wrote in message <7437gs$ok2$2_at_schbbs.mot.com>...
>I think you have a point in your analysis of the SQL statement. However,
>y.y_key is your primary key on table y otherwise you wouldn;t be joining to
>it. As a oprimary key this shouldn;t ever be NULL so your predicate AND
>y.y_key IS NULL should only ever be true if it's a case of the no
>corresponding Y records for your X record.
>

Or, looking at it another way, Mr Oprimiser could just say "Ah ha! I see a short-cut. I'll select all the records from y where y_key is null before I do anything else. Then I'll join the resultant table (call it Y-prime) to X." This of course would be an empty set. So off it would go, and lo and behold ALL of my X.Y_KEY would be considered invalid, regardless of whether they really are, because I'd be joining to the empty table Y-prime for when looking matches.

>If you're still not satisfied then write the SQL in another way.
>

:-( boo hoo

>You want all records in X with no corresponding record in Y. Try something
>like..
>
>SELECT x.rowid
> FROM x
>where not exists (select 1 from Y where Y.y_key = x.y_key);
>

EEEK! Thanks but no thanks. I haven't done an explain plan on this but I suspect the exists predicate would get evaluated repeatedly, once for every row in x, in the very worst case tablescanning the whole thing. I have some big tables here and performance is an issue.

If only 7.3 would allow me to return a rowid from an inline view in a PL/SQL cursor I could code round it. I know there is a fix for this 'cos Ive been on a 7.something site and it worked. I'm sorry if I sound bitter but I get so frustrated at the bugginess of Oracle. I'm sure it's out to get me.

Thanks for your help, but I'm still worried. If anyone has an authoritative book or manual reference on this subject I'd be really grateful. Received on Wed Dec 02 1998 - 14:52:00 CST