| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: IN vs. EXISTS
Hi Diego,
After performing quite a few test you are 100% correct. I appreciate your
insight.
It is conclusive using "IN" is the best approach.
Rick
dcutrone
<dcutrone_at_hotp To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
op.com> cc:
Sent by: Subject: RE: IN vs. EXISTS
root_at_fatcity.c
om
10/18/02 06:43
PM
Please respond
to ORACLE-L
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 servicesto: 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
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting servicesto: 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).
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
--
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 servicesto: 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 Oct 19 2002 - 10:24:40 CDT
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
 |
 |