Re: minus vs. where not exists, vs. where not in

if you handle for nulls with an 'nvl' then 'not exists' appears to return the same answer as not in.

or am I wrong?
----- Original Message -----
To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Sunday, September 21, 2003 3:24 PM

> First off, the three are not equivalent, not substitutes for each other.
> Well not in and minus would be, but they are different from not exists.
> "not in"/"minus" and "not exists" can return different results. See
>

<http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:44202973 7684>http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:4420 29737684
>
>
> for examples and explanation.
>
> I have not done any performance comparisons but I personally routinely use
> minus and I am quite happy with it, especially across a db link.
>
> At 09:59 AM 9/21/2003 -0800, you wrote:
> >Do any of you have any cases when minus is superior in performance? Ive
> >found 'not in' with a hash_aj to be the best option if the sub-query is
> >significantly less 'costly' then the outer query. I dont mean cost in
> >terms of the Explain Plan, I mean the work Oracle has to do to find a
> >result set. It also depends on a proper hash_area_size
> >
> >I find 'where not exists' to be best if the subquery is relatively close
> >in cost to the outer query. Yes I know you cant make broad
> >generalizations, but there has to be some 'narrow' generalizations you
can
> >make. Such as certain cases, etc...
> >
> >what have you seen? There seems to be very little work in this area in
the
> >literature.
> >
> >Does 'where not exist' need more or less sort_area_size space than minus?
>
> Wolfgang Breitling
> Oracle7, 8, 8i, 9i OCP DBA
> Centrex Consulting Corporation
> http://www.centrexcc.com
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Wolfgang Breitling
> INET: breitliw_at_centrexcc.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Ryan
  INET: rgaffuri_at_cox.net

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).