Delete from empty table taking forver

I have an empty table that previously had a large amount of rows.

The table has about 10 columns and indexes on many of them, as well as indexes on multiple columns.

DELETE FROM item WHERE 1=1

This takes approximately 40 seconds to complete

SELECT * FROM item

this takes 4 seconds.

The execution plan of SELECT * FROM ITEM shows the following;

SQL> select * from midas_item;

no rows selected

Elapsed: 00:00:04.29

Execution Plan
----------------------------------------------------------
 0      SELECT STATEMENT Optimizer=CHOOSE (Cost=19 Card=123 Bytes=73
      80)

1    0   TABLE ACCESS (FULL) OF 'MIDAS_ITEM' (Cost=19 Card=123 Byte
      s=7380)





Statistics
----------------------------------------------------------
      0  recursive calls
      0  db block gets
   5263  consistent gets
   5252  physic开发者_运维问答al reads
      0  redo size
   1030  bytes sent via SQL*Net to client
    372  bytes received via SQL*Net from client
      1  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      0  rows processed

any idea why these would be taking so long and how to fix it would be greatly appreciated!!

One possibility is a lock. That is there was a row in the table which had been committed and locked by another delete. Your delete sat and waited on the lock. When the locking transaction was committed, your delete was then able to finish.

A second possibility is that you ran the delete first, which fetched the blocks from disk into the cache (which took time). When the select ran, the data was in the cache and so ran quicker. I think this is less likely as you select stats indicated "5252 physical reads", so it wasn't getting them from the SGA cache. It is possible that a disk cache was involved though.

A third possibility is that there is a BEFORE/AFTER DELETE trigger (not FOR EACH ROW) which did something.

A fourth possibility is that the DELETE resulted in a delayed block cleanout. When the rows were actually deleted, if they were written to disk prior to being committed they'd still have the lock/transaction info. Your delete comes along, reads the blocks, sees the now outdated transaction info, removes it and re-writes the block.

A fifth possibility is contention. Maybe there was just more happening at the same time as the delete.

Lots of possibilities. If you can reproduce it, then do a trace with wait events and run it through TKPROF.

If your table previously was filled with a large amount of data, then Oracle will scan it up to the high water mark, even if it has no data NOW. You may use TRUNCATE statement to reset HWM.

Also on AskTom

Select is just doing a full table scan. Delete on the other hand (in Oracle ) will have to store the whole deleted rows in the rollback segments in order to allow you to undo the changes later (so it can be slower even than the insert).

You can find a very long and useful discussion related to that on Ask Tom forum. Depending on your business case maybe you can apply more techniques.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2345591157689

If you have many indexes on the table then a delete is going to take time because each entry in the index needs removing as well. Indexes speed up read processes and slows down write or other modification processes (hence the speedy select).

-- EDIT: Sorry didn't read the questions fully. If the table is empty then it can't be an index issue. or I'll be surprised if it was --

If you are regularly deleting all rows from a table, 'truncate table XXX' is typically much faster than a mass-delete. Maybe try that and see how much faster you can get it.