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

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.

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.