You can use the ALTER INDEX statement to specify that additional
columns be appended to the set of index key columns of a unique index.
About this task
Restriction: You cannot add columns to IBM®-defined indexes on the DB2® catalog.
If
you want to add a column to a unique index to allow index-only access
of the data, you first must determine whether existing indexes on
a unique table are being used to query the table. You can use the
RUNSTATS utility, real-time statistics, or the EXPLAIN statement to
find this information. Those indexes with the unique constraint in
common are candidates for consolidation. Other non-unique indexes
might be candidates for consolidation, depending on their frequency
of use.
Procedure
To specify that additional columns be appended to the
set of index key columns of a unique index:
- Issue the ALTER INDEX statement with the INCLUDE clause. Any column that is included with the INCLUDE clause is not used
to enforce uniqueness. These included columns might improve the performance
of some queries through index only access. Using this option might
eliminate the need to access data pages for more queries and might
eliminate redundant indexes.
- Commit the alter procedure. As a result of this
alter procedure, the index is placed into page set REBUILD-pending
(PSRBD) status, because the additional columns preexisted in the table.
- To remove the PSRBD status from the index, complete one
of the following options:
- Run the REBUILD INDEX utility on the index that you ran the
alter procedure on.
- Run the REORG TABLESPACE utility on the index that you ran
the alter procedure on, or you can wait to run the alter procedure
until just before the REORG TABLESPACE utility is scheduled to run.
- Run the RUNSTATS utility. The results will be
used after the next step.
- Perform REBIND on the static plans and packages.
- Run the EXPLAIN statement to verify that the optimizer
is choosing the index with the included columns.
- Drop the indexes that are consolidated and no longer needed.
- Verify that the new index is satisfying your query needs
by using the RUNSTATS utility, real-time statistics, or the EXPLAIN
statement.
