As tables are updated, index performance can degrade.
- 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.
- 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.
- 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.
- 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.
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.
Online index reorganization in ALLOW WRITE ACCESS mode, with or without the CLEANUP option, is supported for spatial indexes or MDC and ITC tables.