Delete from empty table taking forver

Posted by Will on Stack Overflow See other posts from Stack Overflow or by Will
Published on 2010-03-22T19:51:51Z Indexed on 2010/03/22 20:11 UTC
Read the original article Hit count: 126

Filed under:
|
|
|

Hello,

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  physical 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!!

© Stack Overflow or respective owner

Related posts about Oracle

Related posts about Performance