Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Another Outer Join Question

Re: Another Outer Join Question

From: Andrew Forsyth <arf001DELETETHIS_at_yahoo.Kom>
Date: Fri, 4 Dec 1998 08:10:19 -0000
Message-ID: <u$OlV71H#GA.307@nih2naab.prod2.compuserve.com>

mikkro_at_hbsi.com wrote in message <745hsq$cue$1_at_nnrp1.dejanews.com>...
>In article <#TByMzUH#GA.287_at_nih2naaa.prod2.compuserve.com>,
> "Andrew Forsyth" <arf001DELETETHIS_at_yahoo.Kom> wrote:
>Yes, it will work. No you should not worry about the optimizer.
>By the vary definition, both factors need to be true.
>
>Alternative ideas.
>
>
> select
> x.rowid
> from
> x
> where
> x.y_key in ( select distinct y_key from X where y_key is not null
> minus
> select distinct y_key from y where y_key is not null)
>
>assuming you want to actually do something with the table x. This is a
little
>slower, but could allow for other manipulations.

Hmm ... looks interesting. I'll have a look at the plan, see what my old mate the Optimiser makes of it.

Thanks to those of you who responded to my original query. It has been most helpful and has given me more food for thought.

I'm probably going to press ahead with my initial design, but no-one has completely put my mind at rest about the ambiguity of the initially phrased SQL. One last question, can anybody suggest a hint which will get rid of the ambiguity? Something along the lines of:

  select /*+ Dear Optimiser, please do the outer join of x to y

                   before you evaluate any of the
                  other predicates.  Thanks */
        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;

of course what I really want is:

select row_id
from
  (select

        x.rowid row_id
       ,x.y_key x_y_key
       ,y.y_key y_y_key
    from
        x,y
    where
        x.y_key = y.y_key (+)

  )
where

      x_y_key is not null
and y_y_key is null;

But that doesnt work inside a PL/SQL cursor on 7.3.

Thanks again. Received on Fri Dec 04 1998 - 02:10:19 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US