The CREATE INDEX statement creates an index on an existing base table. The index contains the columns specified and is keyed on those columns, in the order they are specified.

Indexes can improve query processing. If data is retrieved from a table based on an indexed column, the DBMS uses indexes, if available, to accelerate query processing. To obtain the greatest benefit, create indexes that contain all of the columns that are generally queried and keyed on some subset of those columns.

Any number of indexes can be created for a table, but, for portability, each index can contain no more than 16 columns.

To prevent the index from accepting duplicate values in key fields, specify the UNIQUE option. If the base table on which the index is being created has duplicate values for the index’s key fields, then the create index statement will fail. Similarly, if you attempt an insert or update that violates the uniqueness constraint of an index created on the table, then the insert or update will fail. This is true for an UPDATE statement that updates multiple rows: the UPDATE statement will fail as soon as it attempts to write a row that update violates the uniqueness constraint.

Particular Enterprise Access products may support extensions to the CREATE INDEX statement (using the WITH clause).

To ensure application portability, follow each CREATE INDEX statement with a COMMIT statement.

An index cannot be updated directly. When a table is changed, the DBMS updates indexes as required. To destroy an index, use the DROP statement. All indexes on a table are destroyed when the table is dropped.