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

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:442029737684>http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:442029737684

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).