| 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
Dennis,
Thanks for the reply and congratulations but a little late. I have benn
well above that "notch" for many years. Testing a single test case is not
always conclusive. Certainly combing through countless documentation can
prove to be somewhat futile. It took me quite a while to find what I did.
The use of IN vs EXISTS should be very conclusive. Since we have so many
"experts" on this list I was hoping to rely on that expertise/experience.
Sometimes it's Ok to just get opinions which are hopefully based on fact.
In this case I have gotten about a 50-50 split on best approach.
Thanks for your comments.
Rick
DENNIS WILLIAMS
<DWILLIAMS_at_LIFE To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
TOUCH.COM> cc:
Sent by: Subject: RE: IN vs. EXISTS
root_at_fatcity.co
m
10/18/02 05:14
PM
Please respond
to ORACLE-L
Rick - Well, you've just confirmed the advice of many of the experts on
this
list, namely "test it". Congratulations on moving up a notch.
-----Original Message-----
Sent: Friday, October 18, 2002 3:24 PM
To: Multiple recipients of list ORACLE-L
Dennis,
I have read about NOT IN. Oracle doc says the following but not conclusive
"In certain circumstances, it is better to use IN rather than EXISTS. In general, if the selective predicate is in the subquery, then use IN. If the selective predicate is in the parent query, then use EXISTS. "
It appears in my ex. using exists that Oracle will evaluate all 100 million
records to see if it gets deleted
Whereas the WHERE IN will use the PK index to locate the rows to be deleted
Thanks
Rick
DENNIS WILLIAMS
<DWILLIAMS_at_LIFE To: Multiple recipients of
list ORACLE-L <ORACLE-L_at_fatcity.com>
TOUCH.COM> cc:
Sent by: Subject: RE: IN vs. EXISTS
root_at_fatcity.co
m
10/18/2002
03:53 PM
Please respond
to ORACLE-L
Rick - I would check the EXPLAIN PLAN for both queries, and see whether Oracle uses a different query plan. Usually the prescription for better SQL performance is to avoid NOT IN, as the following snippet that I snagged off the 'net explains.
Use NOT EXISTS in Place of NOT IN
In sub-query statements such as the following, the NOT IN clause causes an
internal sort/merge. The NOT IN clause is the all-time slowest test
possible
as it forces a full read of the table in the sub-query SELECT. Avoid using
NOT IN clause either by replacing it with Outer Joins or with a NOT EXISTS
clause
Dennis Williams
DBA, 40%OCP
Lifetouch, Inc.
dwilliams_at_lifetouch.com
-----Original Message-----
Sent: Friday, October 18, 2002 1:32 PM
To: Multiple recipients of list ORACLE-L
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 (like subscribing).
---------------------------------------------------------------------
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).
---------------------------------------------------------------------
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:18:31 CDT
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
 |
 |