Creating indexes

Use the CREATE INDEX statement to create indexes. The following example creates an index over the column LASTNAME in the CORPDATA.EMPLOYEE table:

  CREATE INDEX CORPDATA.INX1 ON CORPDATA.EMPLOYEE (LASTNAME)

You can also create an index that does not exactly match the data for a column in a table. For example, you can create an index that uses the uppercase version of an employee name:

  CREATE INDEX CORPDATA.INX2 ON CORPDATA.EMPLOYEE (UPPER(LASTNAME))

Most expressions allowed by SQL can be used in the definition of the key columns.

You can create any number of indexes. However, because the indexes are maintained by the system, a large number of indexes can adversely affect performance. One type of index, the encoded vector index (EVI), allows for faster scans that can be more easily processed in parallel.

If an index is created that has exactly the same attributes as an existing index, the new index shares the existing indexes' binary tree. Otherwise, another binary tree is created. If the attributes of the new index are exactly the same as another index, except that the new index has fewer columns, another binary tree is still created. It is still created because the extra columns prevent the index from being used by cursors or UPDATE statements that update those extra columns.

Indexes are created with the sort sequence in effect at the time the CREATE INDEX statement is run. The sort sequence applies to all SBCS character fields, or UCS-2 or UTF-16 graphic fields of the index.