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.
- Create insert time clustering (ITC)
tables. For ITC tables, if you have a cyclical access pattern, for
example you delete all data that was inserted at similar times, you
can release that space back to the system. In such cases, you can
reduce the need for a table reorganization with the REORG RECLAIM
EXTENTS command that frees space.
- 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.
Note: If
readahead prefetching is enabled it helps reduce the need to reorganize
tables even if formula F4 of the REORGCHK command
states otherwise.
Reducing the need to rebuild indexes
To reduce the need to rebuild indexes with
index reorganization:
- Create indexes specifying the PCTFREE
or the LEVEL2 PCTFREE option.
- Create indexes with the MINPCTUSED option.
Alternatively, consider using the CLEANUP ALL option of the REORG
INDEXES command 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 the need to rebuild
indexes with index reorganization, even if formula F4 of the REORGCHK command
states otherwise.
DB2 Version 10.1 for Linux, UNIX, and Windows