DB2 10.5 for Linux, UNIX, and Windows

Reduce the need for table and index reorganization

You can use different strategies to reduce the need for, and the costs that are associated with, table and index reorganization.

Reduce the need for table reorganization

You can reduce the need for table reorganization with the following tables and techniques:

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 suggest reorganization of an MDC table if it determines that there are too many unused blocks or that blocks can be compacted.
In cases where space reuse of empty areas of a table is a motivating factor for the reorganization, create insert time clustering (ITC) tables. For ITC tables, if you have a cyclical access pattern you can release that space back to the system. An example of a cyclical access pattern is deleting all data that was inserted at similar times. In such cases, you can reduce the need for a table reorganization with the RECLAIM EXTENTS table clause of the REORG command. The REORG command with the RECLAIM EXTENTS table clause reclaims free space on which the table is found. The command also consolidates sparse extents implicitly. This consolidation leads to more space reclamation, but a longer duration for utility execution when compared to DB2® Version 10.1.
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 attempts 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, follow 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 it is loaded into the table.

After you follow these tasks, the clustering index and the PCTFREE setting on the table help 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 run 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. This action improves the CLUSTERRATIO or CLUSTERFACTOR statistics that are collected by the RUNSTATS utility.

Note: If readahead prefetching is enabled, the table might not require reorganization for clustering purposes even if formula F4 of the REORGCHK command states otherwise.

Reduce index rebuild requirements

You can reduce index rebuild requirements with index reorganization:

Create indexes that specify the PCTFREE or the LEVEL2 PCTFREE option.
Create indexes with the MINPCTUSED option. Alternatively, consider running the REORG INDEXES command with the CLEANUP ALL option to merge leaf pages.
Use the RECLAIM EXTENTS option of the REORG INDEXES command to release space back to the table space in an online fashion. This operation provides space reclaim without the need for a full rebuild of the indexes.

Note: If readahead prefetching is enabled it helps reduce index rebuilds with index reorganization, even if formula F4 of the REORGCHK command states otherwise.