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@LIFE To: Multiple recipients of list ORACLE-L
<[EMAIL PROTECTED]>
TOUCH.COM> cc:
Sent by: Subject: RE: IN vs. EXISTS
[EMAIL PROTECTED]
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@LIFE To: Multiple recipients of
list ORACLE-L <[EMAIL PROTECTED]>
TOUCH.COM> cc:
Sent by: Subject: RE: IN vs. EXISTS
[EMAIL PROTECTED]
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.
[EMAIL PROTECTED]
-----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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: DENNIS WILLIAMS
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: DENNIS WILLIAMS
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
