Index keys

The usefulness of an index depends on the design of its key, which you define at the time that you create the index.

An index key is a column, an ordered collection of columns, or an expression on which you define an index. Db2 uses an index key to determine the order of index entries. Good candidates for index keys are columns or expressions that you use frequently in operations that select, join, group, and order data.

All index keys do not need to be unique. For example, an index on the SALARY column of the sample EMP table allows duplicates because several employees can earn the same salary.

A composite key is an index key that is built on 2 or more columns. An index key can contain up to 64 columns.

Begin general-use programming interface information.
For example, the following SQL statement creates a unique index on the EMPPROJACT table. A composite key is defined on two columns, PROJNO and STDATE. 
CREATE UNIQUE INDEX XPROJAC1
  ON EMPPROJACT
     (PROJNO ASC,
          STDATE ASC)
⋮
This composite key is useful when you need to find project information by start date. Consider a SELECT statement that has the following WHERE clause: 
WHERE PROJNO='MA2100' AND STDATE='2004-01-01'

This SELECT statement can execute more efficiently than if separate indexes are defined on PROJNO and on STDATE.

End general-use programming interface information.

In general, try to create an index that is selective, because the more selective an index is, the more efficient it is. An efficient index contains multiple columns, is ordered in the same sequence as the SQL statement, and is used often in SQL statements. To create an efficient index, consider the following recommendations when you create an index and define the index keys:

  • Define as few indexes as possible on a column that is updated frequently because every change to the column data must be reflected in each index.
  • Consider using a composite key, which might be more useful than a key on a single column when the comparison is for equality. A single multicolumn index is more efficient when the comparison is for equality and the initial columns are available. However, for more general comparisons, such as A > value AND B > value, multiple indexes might be more efficient.