Reclaiming unused space from deletions and updates

When a task performs a delete operation or an update that shortens row length, the empty space is preserved in case the transaction is rolled back. If a table is subject to frequent deletions and row-shortening updates, unreclaimed space may accumulate to the point that it impairs performance.

reorg reclaim_space reclaims unused space left by deletions and updates. On each page that has space resulting from committed deletion or row-shortening updates, reorg reclaim_space rewrites the remaining rows contiguously, leaving all the unused space at the end of the page. If all rows have been deleted and there are no remaining rows, reorg reclaim_space deallocates the page.

If the table extends over a partition, or several partitions, you can reclaim any available space on the partition by specifying the partition_name parameter.

You can display statistics on the number of unreclaimed row deletions in a table from the systabstats table and by using the optdiag utility. There is no direct measure of how much unused space there is as a result of row-shortening updates.

The syntax for reorg reclaim_space is:

reorg reclaim_space table_name [index_name] 
	partition partition_name with {resume, time = no_of_minutes}]

If you specify only a table name, only the table’s data pages are reorganized to reclaim unused space; in other words, indexes are not affected. If you specify an index name, only the pages of the index are reorganized. If you specify a partition, only the part of the table that resides on that partition is affected.