DB2 10.5 for Linux, UNIX, and Windows

Index reorganization

As tables are updated, index performance can degrade.

The degradation can occur in the following ways:

Leaf pages become fragmented. When leaf pages are fragmented, I/O costs increase because more leaf pages must be read to fetch table pages.
The physical index page order no longer matches the sequence of keys on those pages, resulting in low density indexes. When leaf pages have a low density, sequential prefetching is inefficient and the number of I/O waits increases. However, if smart index prefetching is enabled, the query optimizer switches to readahead prefetching if low density indexes exist. This action helps reduce the negative impact that low density indexes have on performance.
The index develops too many levels. In this case, the index might be reorganized.

Index reorganization requires:

SYSADM, SYSMAINT, SYSCTRL, DBADM, or SQLADM authority, or CONTROL privilege on the table and its indexes
When the REBUILD option with the ALLOW READ or WRITE ACCESS options are chosen, an amount of free space in the table space where the indexes are stored is required. This space must be equal to the current size of the indexes. Consider placing indexes in a large table space when you issue the CREATE TABLE statement.
Additional log space. The index REORG utility logs its activities.

If you specify the MINPCTUSED option on the CREATE INDEX statement, the database server automatically merges index leaf pages if a key is deleted and the free space becomes less than the specified value. This process is called online index defragmentation.

To restore index clustering, free up space, and reduce leaf levels, you can use one of the following methods:

Drop and re-create the index.
Use the REORG TABLE command with options that reorganize the table and rebuild its indexes offline.
Use the REORG INDEXES command with the REBUILD option to reorganize indexes online or offline. You might choose online reorganization in a production environment. Online reorganization allows users to read from or write to the table while its indexes are being rebuilt.

If your primary objective is to free up space, consider the CLEANUP and RECLAIM EXTENTS options of the REORG command. See the related links for more details.

In IBM® Data Studio Version 3.1 or later, you can use the task assistant for reorganizing indexes. Task assistants can guide you through the process of setting options, reviewing the automatically generated commands to perform the task, and running these commands. For more details, see Administering databases with task assistants.

With DB2® V9.7 Fix Pack 1 and later releases, if you specify a partition with the ON DATA PARTITION clause, the REORG INDEXES ALL command that is run on a data partitioned table reorganizes the partitioned indexes for the single data partition. During index reorganization, the unaffected partitions remain read and write accessible access is restricted only to the affected partition.

REORG TABLE commands and REORG INDEXES ALL commands can be issued on a data partitioned table to concurrently reorganize different data partitions or partitioned indexes on a partition. When concurrently reorganizing data partitions or the partitioned indexes on a partition, users can access the unaffected partitions. All the following criteria must be met to issue REORG commands that operate concurrently on the same table:

Each REORG command must specify a different partition with the ON DATA PARTITION clause.
Each REORG command must use the ALLOW NO ACCESS mode to restrict access to the data partitions.
The partitioned table must have only partitioned indexes if you run REORG TABLE commands. No nonpartitioned indexes (except system-generated XML path indexes) can be defined on the table.

Note: The output from the REORGCHK command contains statistics and recommendations for reorganizing indexes. For a partitioned table, the output contains statistics and recommendations for reorganizing partitioned and nonpartitioned indexes. Alternatively, if the objective is to reclaim space, the RECLAIMABLE_SPACE output of the ADMIN_GET_INDEX_INFO function shows how much space is reclaimable. Use the REORG INDEXES command with the RECLAIM EXTENTS option to free this reclaimable space.

Online index reorganization

When you use the REORG INDEXES command with the ALLOW READ/WRITE ACCESS and REBUILD options, a shadow copy of the index object is built while the original index object remains available as read or write access to the table continues. If write access is allowed, then during reorganization, any changes to the underlying table that would affect the indexes are logged. The reorg operation processes these logged changes while rebuilding the indexes.

Changes to the underlying table that would affect the indexes are also written to an internal memory buffer, if such space is available for use. The internal buffer is a designated memory area that is allocated on demand from the utility heap. The use of a memory buffer enables the index reorg utility to process the changes by reading directly from memory first, and then reading through the logs, if necessary, but at a much later time. The allocated memory is freed after the reorg operation completes.

Extra storage space is required in the index tablespace to hold the shadow copy of the index. Once the shadow copy of the index is built and all logs affecting the shadow copy have been processed, then a super-exclusive lock is taken on the table and the original index is discarded. The space that was occupied by the original copy of the index object is free to be reused by any object in the same tablespace, however it is not automatically returned to the filesystem.

Online index reorganization in ALLOW WRITE ACCESS mode, with or without the CLEANUP option, is supported for spatial indexes or MDC and ITC tables.