You can create indexes to allow queries to run more efficiently,
to order the rows of a table in ascending or descending sequence
according to the values in a column, or to enforce constraints such
as uniqueness on index keys. You can use the CREATE INDEX statement,
the DB2® Design Advisor, or the db2advis Design
Advisor command to create the indexes.
About this task
This task assumes that you are creating
an index on a nonpartitioned table.
To create an index using
the CREATE INDEX statement from the command line, enter:
CREATE UNIQUE INDEX EMP_IX
ON EMPLOYEE(EMPNO)
INCLUDE(FIRSTNAME, JOB)
The INCLUDE clause, applicable
only on unique indexes, specifies additional columns to be appended
to the set of index key columns. Any columns included with this clause
are not used to enforce uniqueness. These included columns can improve
the performance of some queries through index only access. This option
might:
- Eliminate the need to access data pages for more queries
- Eliminate redundant indexes
If SELECT EMPNO, FIRSTNAME, JOB FROM EMPLOYEE is issued to the
table on which this index resides, all of the required data can be
retrieved from the index without reading data pages. This improves
performance.
Note: When a row is deleted
or updated, the index keys are marked as deleted and are not physically
removed from a page until clean up is done some time after the deletion
or update is committed. These keys are referred to as pseudo-deleted
keys. Such a clean up might be done by a subsequent transaction which
is changing the page where the key is marked deleted. Clean up of
pseudo-deleted keys can be explicitly triggered using the CLEANUP
ONLY ALL option of the REORG INDEXES utility.
Note: On Solaris platforms, patch 122300-11 on Solaris 9
or 125100-07 on Solaris 10 is required to create indexes with RAW
devices. Without this patch, the CREATE INDEX statement will hang
if a RAW device is used.
DB2 Version 9.7 for Linux, UNIX, and Windows