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

From: Ryan <rgaffuri_at_cox.net>
Date: Sun, 21 Sep 2003 09:59:43 -0800
Message-ID: <F001.005D09FB.20030921095943@fatcity.com>

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?

--

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). Received on Sun Sep 21 2003 - 12:59:43 CDT