CREATE INDEX Statement (create_index_statement) 

The CREATE INDEX statement (create_index_statement) creates an index of a base table.

Syntax

<create_index_statement> ::=
  CREATE [UNIQUE] INDEX <index_name> ON <table_name> (<index_spec>)

<index_spec> ::=
  <index_column_name>,...
| <dbfunction_name>(<column_name>,...) [ASC|DESC]

<index_column_name> ::=
  <column_name> [ASC|DESC]

Examples

SQL Tutorial, Indexes

Explanation

Indexes provide access to the table data via non-key columns. Maintaining these indexes, however, can be quite complex in the case of an INSERT, UPDATE, or DELETE statement.

Specifying a database function name defines a function index. These indexes can accelerate queries that contain the database function in their search condition, or that sort by database function.

If no database function name is entered, the index is generated using the specified columns of the table. In this case, the secondary key consists of the specified columns of the table, in the specified order.

●     If a schema is not specified in the table name, the current schema is accepted implicitly.

●     The specified table must be an existing base table, and not a temporary table. The index name must not be identical with an existing index name of the table.

●     The column defined by the column name must be a column in the specified table. This column must not be a LONG column. All of the column name pairs must be different.

●     The current user must have the INDEX privilege for the columns.

●     The database function identified by the database function name must fulfill the following conditions:
The current user must have the EXECUTE privilege for the database function.
The database function must not contain any SQL statements.
The type of the specified column names must be compatible with the type of the function parameters.
The result of the database function must be deterministic, that is, repeated execution of the function with the same parameters always produces the same result.

Restrictions for SQL Statements

UNIQUE

If UNIQUE is specified, the database system ensures that no two rows of the specified table have the same values in the indexed columns. In this way, if two rows both contain the NULL value for all columns of an index, the two index values are not considered to be identical. If at least one column does not contain the NULL value, two rows that have the same value in all non-NULL columns are considered to be identical.

ASC | DESC

The index values are stored in ascending or descending order. If the specification of ASC or DESC is omitted, ASC is implicitly assumed.

See also:

UNIQUE Definition (unique_definition)