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