Re: Large deletes (Help)

Dean R Money (dmoney_at_magnus.acs.ohio-state.edu) wrote:
: At the end of the year, it is necessary for me to delete A LOT of rows
: from certain Oracle tables (up to a million). I notice that even if I
: delete every row, the now empty table is very slow, as if something
: wasn't completely deleted. If I TRUNCATE the table, this problem is
: solved, but, unfortunately, I don't want to delete every row in the
: table... but well over half of them. After doing a huge delete, is
: there a way of bringing the table "up to speed"?

One reason for this behaviour is that your index contains to much deleted rows in the index-leafs which slows down index access. You should use the VALIDATE INDEX <indexname> command and observe the columns DEL_LF_ROWS in comparison to LF_ROWS in the table INDEX_STATS. If the numbers are of same
order, you should reorganize (DROP INDEX...) the index.

: Also, as far as doing large end-of-year deletes go, is there a faster
: way to delete selectively than DELETE FROM MY_TABLE WHERE...? Even
: with an index, deleting hundreds of thousands of rows takes a long
: time.

Two things:
1.) Each index on a table slows down the deletion of rows, because the index has to updated and in some cases reorganized.

2.) If you want to delete half of a table, you should avoid using any index for the selection, because Oracle will do with much better performance using a full-table-scan than accessing each row by index. V7 will not use an index in these cases.

Together with the above, it seems to make sense just to drop ALL indexes, do the deletion and recreate the indexes.

• Joerg Plewe, MPI Dortmund, Germany