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).
