Re: Is space released after deletes?

Although the analyze command will give you an accurate row count, when it counts up the used blocks, any block that has been used will be counted as used even if it is currently empty.

If you delete all rows in a table that once used 100Megs, it will still be 100% used according to ANALYZE. Truncating resets the 'high water mark' pointer back to the empty status. An ANALYZE would then indicate 0% used.

This is important when doing full table scans on tables that fluctuate in size. The table scan will run right through every used block, even if it is empty. Snapshot MLOG tables behave in this way for example.

Similarly, extents are not released by deletes. They are all marked as used. A TRUNCATE can optionally return these extents to freespace or leave them be as you desire.

Wayne Linton Received on Tue Sep 10 1996 - 00:00:00 CEST