RE: Delete statement is very slow !!

From: DENNIS WILLIAMS <DWILLIAMS_at_lifetouch.com>
Date: Mon, 04 Aug 2003 07:19:23 -0800
Message-ID: <F001.005C8D1A.20030804071923@fatcity.com>

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

---

---

| Operation | Name | Rows | Bytes| Cost | Pstart|
Pstop |

---

---

| DELETE STATEMENT | | 1M| 20M| 66106 | |
|
| DELETE |WIN_STORE | | | | |
|
| TABLE ACCESS BY GLOBAL I|WIN_STORE | 1M| 20M| 66106 | ROWID | ROW
L |
| INDEX RANGE SCAN |WIN_STORE | 1M| | 2616 | |
|

---

---

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