When the DBANALYS utility indicates that space utilization of an index is reduced to 60 percent or less, use the PROUTIL IDXCOMPACT utility to perform index compaction online. Performing index compaction increases space utilization of the index block to the compacting percentage specified. For example:
Performing index compaction reduces the number of blocks in the B-tree and possibly the number of B-tree levels, which improves query performance.
The index compacting utility operates in phases:
Phase 1 — If the index is a unique index, the delete chain is scanned and the index blocks are cleaned up by removing deleted entries.
Phase 2 — The nonleaf levels of the B-tree are compacted starting at the root working toward the leaf level.
Phase 3 — The leaf level is compacted.
The _UserStatus virtual system table displays the utility's progress.
Note: Because index compacting is performed online, other users can use the index simultaneously for read or write operation with no restrictions. Index compacting only locks one to three index blocks at a time, for a short time. This allows full concurrency.
Phase 1 — If the index is a unique index, the delete chain is scanned and the index blocks are cleaned up by removing deleted entries.