Creating indexes
Indexes can be created for many reasons, including: 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; 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.
Procedure
To create an index from the command line, use the CREATE
INDEX statement.
For example:
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
What to do next
When a row is deleted or updated, the index keys are marked as deleted and are not physically removed from a page until cleanup is done some time after the deletion or update is committed. These keys are referred to as pseudo-deleted keys. Such a cleanup 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 by using the CLEANUP ONLY ALL parameter in the REORG INDEXES command.