| 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: poor query performance
Bill,
Try this:
Delete
>From T1
Where F1 In
(
Select F1
From T1
Minus
Select PK
From T2
);
This will delete T1 rows for which there's no matching PK in T2 and will most likely be quicker than the Not Exists query, though you never can say for sure! ;-)
Oh, and the Cost that Explain Plan reports is purely relative and can't be used for comparison across different queries.
Jack
-----Original Message-----
Bill
Sent: Thursday, February 28, 2002 3:24 PM
To: Multiple recipients of list ORACLE-L
I have a query that deletes rows from a table with 57K rows, as follows
delete from T1 where not exists
(select T2.PK from T2
where T2.PK = T1.F1);
T2.PK is the Primary Key on Table T2
T1.F1 is an indexed field on table T1
Explain plan shows a low cost (80) but the delete takes about 10+ minutes.
T1 has about 57K rows
T2 has about 29500 rows
other queries with costs in the 700 range are pretty quick - this one's a dog
any ideas?
thanks
Bill Magaliff
Framework, Inc.
914-631-2322
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite INET: japplewhite_at_inetprofit.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: 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 Thu Feb 28 2002 - 16:31:31 CST
 |
 |