CREATE INDEX

Description

How to write an index name, see Identifier. Creates an index to a specified table by using the CREATE INDEX statement. For USING INDEX clause, descending index, covering index, ORDER BY optimization, and GROUP BY OPTIMIZATION, see Using Indexes.

Syntax

CREATE [ REVERSE ] [ UNIQUE ] INDEX [ index_name ]

ON table_name <index_col_desc>

<index_col_desc> ::= ( column_name[(prefix_length)] [ASC | DESC] [ {, column_name[(prefix_length)] [ASC | DESC]} ...] ) [ ; ]

• REVERSE: Creates an index in the reverse order. A reverse index helps to increase sorting speed in descending order.
• UNIQUE: Creates an index with unique values.
• index_name: Specifies the name of the index to be created. The index name must be unique in the table. If omitted, a name is automatically assigned as i_<table_name>_<column_names>.
• prefix_length: When you specify an index for character- or bit string-type column, you can create an index by specifying the beginning part of the column name as a prefix. You can specify the length of the prefix in bytes in parentheses next to the column name. You cannot specify prefix_length in a multiple column index or a UNIQUE index. It is impossible to create an index by specifying prefix_length as a host variable. If you want to guarantee the query result order in the index in which prefix_length is specified, you must specify the ORDER BY clause.
• table_name: Specifies the name of the table where the index is to be created.
• column_name: Specifies the name of the column where the index is to be applied. To create a composite index, specify two or more column names.
• ASC | DESC: Specifies the sorting order of columns. In case of a REVERSE index, ASC is ignored and DESC is applied.

Example 1

The following example shows how to create a reverse index.

CREATE REVERSE INDEX gold_index ON participant(gold);

Example 2

The following example shows how to create a multiple column index.

CREATE INDEX name_nation_idx ON athlete(name, nation_code);

Example 3

The following example shows how to create a single column index. In this example, 1-byte long prefix is specified for the nation_code column when creating an index.

CREATE INDEX ON game(nation_code(1));

CREATE INDEX game_date_idx ON game(game_date);