RE: IN vs. EXISTS

From: dcutrone <dcutrone_at_hotpop.com>
Date: Fri, 18 Oct 2002 14:43:51 -0800
Message-ID: <F001.004EDE5D.20021018144351@fatcity.com>

Hello Rick,

I think that if you use EXISTS instead of IN the optimizer will have to do a FTS on the big table because he can't use any avaiable index on it. And it's a big table....

While if you use IN and you have an index in the parent table (the BIG one) this index CAN be used (here, field1 must be indexed),and I think it'll be much faster. Of course it depends on the index's selectivity as well.

Also, remember that with the IN operator the subquery is executed just once, and with EXISTS it's executed once by each parent row (so it must execute very efficiently) And that IN can use parent indexes (when avaiable and some conditions are met) and can't use any indexes to resolve the subquery, while EXISTS can't use the parent query indexes and CAN use indexes on the subquery.

Please correct me if I'm wrong.

HTH
Greetings
Diego Cutrone

Hi All,

I have 2 tables

BIG - 100 million records
SMALL - 1 million records.

I want to delete all the records in BIG that are in small. There is a PK on field1.
Which of the below methods would you choose and why?

DELETE FROM big
WHERE field1 IN (SELECT field1 FROM small);

DELETE FROM big a
WHERE EXISTS (SELECT 1 FROM small B

                                    WHERE b.field1 = a.field1);

Thanks
Rick

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author:
  INET: Rick_Cale_at_teamhealth.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
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: dcutrone
  INET: dcutrone_at_hotpop.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). Received on Fri Oct 18 2002 - 17:43:51 CDT