Large deletes (Help)

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

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. It's especially bad when I have to break down my deletes to alleviate ROLLBACK SPACE problems.

Any help would be most appreciated.

Dean. Received on Tue Aug 31 1993 - 22:31:42 CEST