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