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; 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 there are no remaining rows, reorg reclaim_space deallocates the page.

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

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.

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.