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

Home -> Community -> Mailing Lists -> Oracle-L -> antijoin

antijoin

From: Sai Selvaganesan <ssaisundar_at_sbcglobal.net>
Date: Sat, 16 Aug 2003 13:39:22 -0800
Message-ID: <F001.005CB1E4.20030816133922@fatcity.com>


hi  

i am trying to figure out which is a more efficient method to do an antijoin between two tables. The version is 9i and optimiser is choose.stats are the lates.  

here is the query with not in and not exitsts: case 1:
select distinct a.id from tab a
where a.id not in (select b.id from b)
/

case 2:
select distinct a.id from tab a
where not exists (select b.id from b where .id=a.id)
/
 

the subquery returns 4.8 mill rows in the first case and the o/p never comes out in the first case. the second case works better and gives me a result. but what is interesting is the explain plan is the same.  

can some explain this to me? the other question i have will the second query behave the same way if the sub-query returns 4.8 mill records. is there a better way to write this code. it is find distinct ids in tab which dont exist in b.bot tables are bound to grow. at this moment tab has 10000 rows and b 4.8 million rows. thanks
sai  

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Sai Selvaganesan
  INET: ssaisundar_at_sbcglobal.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 Sat Aug 16 2003 - 16:39:22 CDT

Original text of this message

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