DB2 Version 9.7 for Linux, UNIX, and Windows

Reducing the need to reorganize tables and indexes

You can use different strategies to reduce the need for (and the costs associated with) table and index reorganization.

Reducing the need to reorganize tables

To reduce the need for table reorganization:

Use multi-partition tables.
Create multidimensional clustering (MDC) tables. For MDC tables, clustering is maintained on the columns that you specify with the ORGANIZE BY DIMENSIONS clause of the CREATE TABLE statement. However, the reorgchk utility might still recommend reorganization of an MDC table if it determines that there are too many unused blocks or that blocks should be compacted.
Enable the APPEND mode on your tables. If the index key values for new rows are always new high key values, for example, the clustering attribute of the table will attempt to place them at the end of the table. In this case, enabling the APPEND mode might be a better choice than using a clustering index.

To further reduce the need for table reorganization, perform these tasks after you create a table:

Alter the table to specify the percentage of each page that is to be left as free space during a load or a table reorganization operation (PCTFREE)
Create a clustering index, specifying the PCTFREE option
Sort the data before loading it into the table

After you have performed these tasks, the clustering index and the PCTFREE setting on the table help to preserve the original sorted order. If there is enough space on the table pages, new data can be inserted on the correct pages to maintain the clustering characteristics of the index. However, as more data is inserted and the table pages become full, records are appended to the end of the table, which gradually becomes unclustered.

If you perform a table reorg operation or a sort and load operation after you create a clustering index, the index attempts to maintain the order of the data, which improves the CLUSTERRATIO or CLUSTERFACTOR statistics that are collected by the runstats utility.

Reducing the need to reorganize indexes

To reduce the need for index reorganization:

Create clustering indexes, specifying the PCTFREE or the LEVEL2 PCTFREE option.
Create indexes with the MINPCTUSED option. Alternatively, consider using the CLEANUP ONLY ALL option of the REORG INDEXES command to merge leaf pages.