Compacting indexes

Database Administration

Maintaining and Monitoring Your Database : Managing Performance : Index use : Compacting indexes

Compacting indexes

When space utilization of an index is reduced to 60 percent or less as indicated by the PROUTIL IDXANALYS utility, 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:

proutil db-name -C idxcompact [ owner-name.]table-name.index-name [n]

Note: For the complete syntax description see PROUTILutility syntax

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 and working toward the leaf level

Phase 3 — The leaf level is compacted

The _UserStatus virtual system table displays the utility's progress. For more information, see VirtualSystem Tables

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.