Description
The CREATE INDEX statement creates an index on an existing base table. The index contains the columns specified. Any number of indexes can be created for a table, but each index can contain no more than 32 columns. The contents of indexes are sorted in ascending order by default.
Indexes can improve query processing. To obtain the greatest benefit, create indexes that contain all of the columns that are generally queried. The index must be keyed on a subset of those columns.
By default, the index is keyed on the columns in the column list, in the order they are specified. If the key option is specified, the index is keyed on the columns specified in the key list, in the order specified. For example, if you issue the statement:
create index nameidx on employee
(last, first, phone);
you create an index called, nameidx, on the employee table that is keyed on the columns last, first, and phone in that order.
However, if you issue the statement:
create index nameidx on employee
(last, first, phone)
with key = (last, first);
the index is keyed only on the two columns, last and first.
The columns specified in the key column list must be a subset of the columns in the main column list. A long varchar column cannot be specified as part of a key.
Last modified date: 11/09/2022