Show TOC

Background documentationCREATE INDEX Statement (create_index_statement)

 

The CREATE INDEX statement (create_index_statement) defines an index for a base table.

Structure

Syntax Syntax

  1. <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]
End of the code.
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. A CREATE INDEX statement is only executed if no locks are held in the current transaction. If locks are held the system displays an error message. Every CREATE INDEX statement is terminated with an implicit COMMIT.

  • 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 LOB column. All of the column name pairs must be different.

  • The current database user must have the INDEX privilege for the columns.

  • If no database function name dbfunction_name is specified, 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.

  • While an index is being created, the specified table is usually protected from DML statements by means of an exclusive lock. If no locks are held in the current transaction and the index to be created with CREATE INDEX is not UNIQUE, however, only a brief exclusive lock is held for the table. This generally means that other DML statements can also be carried out for this table when the index is created.

Database Function Name dbfunction_name

Specifying a database function name dbfunction_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. The database function identified by the database function name (dbfunction_name) must fulfill the following conditions:

  • The current database user must have the EXECUTE privilege for the database function.

  • The database function may 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.

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 (ASC) or descending (DESC) order. If the specification of ASC or DESC is omitted, ASC is implicitly assumed.

More Information

UNIQUE Definition (unique_definition)

Privileges: Overview

Transactions

Restrictions for SQL Statements