Re: Large deletes (Help)

In article <HHXGBBTH_at_gwdu03.gwdg.de> plewe_at_dl.mpi-dortmund.mpg.de (J.Plewe) writes:

   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.

Yes, this is true. But only for access using the index. The table may still be "slow" during full-table-scans also. This is because every table-segment contains a "high-water-mark" pointing to the highest block-address, which has ever contained data. Full-table-scans continue to read up to the high- -mark regardless if the table contains any rows or not. Even if you deleted every row in the table, every block up to the high-water-mark would be inspected during a full-table-scan. When you truncate a table, you are just moving the high-water-mark pointer to the beginning of the segment. That's why it's so fast. You need to reorganize the table in order to compress all rows towards the beginning of the segment (and use each block effectively), and move the high-water-mark. You can do this with export/import or by creating a new table with the rows you want to keep (instead of deleting the ones you want to get rid of), dropping the old table, and renaming.

cheers,

Magnus Lonnroth
Oracle Sweden

[My opinions are not supported] Received on Sat Sep 04 1993 - 03:49:57 CEST