Another Outer Join Question

Hi all,

Another outer join question. I have done a DejaNews search and read some interesting stuff but I'm still not 100% certain. If this is a perennial question on this ng then my apologies, and a pointer in the right direction would be appreciated.

Consider tables X and Y. X has an opitional foreign key (Y_KEY) to table Y. This is not enforced by any constraint.

I am looking for the rowids of X records which have invalid foreign keys to Y, ie they have a non-null value of Y_key and there is no corresponding record in Y with that value of Y_KEY.

  select

        x.rowid
  from

        x,y
  where

        x.y_key = y.y_key (+)
  and x.y_key is not null
  and y.y_key is null;

This works, I've tried it, I've seen other examples which do the same thing, but I am still nervous. Can I be absolutely certain that the optimiser is not going to use my "y.y_key is null" criterion as a filter on y BEFORE doing the join? This would return rowids of all my x rows with non-null Y_KEYs, regardless of the validity of these fields.

Am I making sense? Do you see why I am so worried? Is Oracle going to come up with a completely dumb plan the minute I turn my back? It would be entirely logical (if completely wrong from my pov) for Oracle to start with my last criterion and regard Y as an empty table 'cos there are no rows in Y where Y key is null and THEN proceed to join it (the filtered, empty Y) to X.

What are the rules for precedence of expressions in such a case? (If your answer to this is "RTFM", then could you give me a reference of which "FM" I should "R" please?)

I know I could push the whole query down a level into an inline view and check the null-ness of y.y_key in the result set of that, but the 7.3 version I am using won't allow me to return rowids from an inline view in a PL/SQL cursor so this alternative approach becomes less efficient (as I can't access the bad rows by rowid in my procedure).

Thanks in advance.

---ARF--- Received on Tue Dec 01 1998 - 11:04:19 CST