Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Delete statement is very slow !!
Madhu
If I understand your posting, you are deleting a million rows? Yes, that will always be slow. One of the reasons Oracle is faster than its competitors is that the designers figured out that people do a lot more inserts than deletes. So inserts run really fast, while deletes are not so fast. One solution that has been posted here (sorry I can't recall who) is to copy the records you want to keep to a new table, then drop the original table and rename the new table. For example, if you wanted to delete 1 million rows from a table containing 10 million rows, you could probably copy the 9 million rows much faster.
Dennis Williams
DBA, 80%OCP, 100% DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com
-----Original Message-----
Sent: Sunday, August 03, 2003 12:34 PM
To: Multiple recipients of list ORACLE-L
Can someone explian me why this delete statement is very slow ?? any
suggestions !!!
pl find the details below !!
Thanks
Madhu
Statement:
++++++++
delete from <tabke_name> where
eow_date<ADD_MONTHS(TO_DATE(:b0,'YYYYMMDD'),((-1)* :b1));
PLAN:
+++++
Plan Table
Session_IO:
+++++++++
11:27:49 SQL> @session_top_sql
OS User DB User SID # of UndoBlk LOG IO PHY IO HASH VALUE SQL ADDRESS ------------------------------ ------------------------------ ---- ------------ ---------- ---------- ---------- ---------------- batchusr RTKBATCH 77 52349 6223101 987505 1253119663 C0000000BB3BC2B8
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Reddy, Madhusudana INET: Madhusudana.Reddy_at_bestbuy.com 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: 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: DWILLIAMS_at_LIFETOUCH.COM 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: 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 Mon Aug 04 2003 - 10:19:23 CDT
![]() |
![]() |