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 helps reduce the negative impact that low density
indexes have on performance.
- The index develops too many levels. In this case, the index should
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 recreate the index.
- Use the REORG TABLE command
with options that allow you to 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, because it 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 using 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, using the REORG INDEXES ALL command
on a data partitioned table and specifying a partition with the ON
DATA PARTITION clause reorganizes the partitioned indexes
for 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 issuing 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 not supported for spatial indexes or multidimensional
clustering (MDC) and insert time clustering (ITC) tables.
Restriction: Online reorganization with the REBUILD option
is not supported in DB2 pureScale® environments.
Any attempts to perform an online reorganization with the REBUILD option
in DB2 pureScale environments
will fail with SQL1419N.