CREATE INDEX
Valid in: SQL, ESQL, OpenAPI, ODBC, JDBC, .NET
The CREATE INDEX statement creates an index on an existing table.
The CREATE INDEX statement has the following format:
[EXEC SQL] CREATE [UNIQUE] INDEX [schema.]index_name
ON table_name
(column_name {, column_name})[UNIQUE]
[WITH with-clause]
index_name
Defines the name of the index. This must be a valid object name.
table_name
Specifies the table on which the index is to be created.
column_name
Specifies a list of columns from the table to be included in the index.
UNIQUE
Prevents the index from accepting duplicate values in key fields.
WITH with-clause
Specifies Enterprise Access product-specific options. For details,see your Enterprise Access product guide.For an overview of the Enterprise Access product WITH clause, see the chapter "OpenSQL Features."
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.
Last modified date: 01/30/2023