Improving the performance of offline table reorganization

The performance of an offline table reorganization is largely determined by the characteristics of the database environment.

There is almost no difference in performance between a reorg operation that is running in ALLOW NO ACCESS mode and one that is running in ALLOW READ ACCESS mode. The difference is that during a reorg operation in ALLOW READ ACCESS mode, the utility might have to wait for other applications to complete their scans and release their locks before replacing the table. The table is unavailable during the index rebuild phase of a reorg operation that is running in either mode.

Tips for improving performance

If there is enough space to do so, use the same table space for both the original table and the reorganized copy of the table, instead of using a temporary table space. This saves the time that is needed to copy the reorganized table from the temporary table space.
Consider dropping unnecessary indexes before reorganizing a table so that fewer indexes need to be maintained during the reorg operation.
Ensure that the prefetch size of the table spaces on which the reorganized table resides is set properly.
Tune the sortheap and sheapthres database configuration parameters to control the space that is available for sorts. Because each processor will perform a private sort, the value of sheapthres should be at least sortheap x number-of-processors.
Adjust the number of page cleaners to ensure that dirty index pages in the buffer pool are cleaned as soon as possible.