Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

Versions available for this page: CUBRID 8.3.0 |  CUBRID 8.3.1 |  CUBRID 8.4.0 |  CUBRID 8.4.1 |  CUBRID 8.4.3 |  CUBRID 9.0.0 | 

CREATE INDEX

Description

Use the CREATE INDEX statement to create an index in the specified table.

Syntax

CREATE [ REVERSE ] [ UNIQUE ] INDEX [ index_name ]

ON table_name ( 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.
  • 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.  
  • 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 is an example of creating 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);

Example 2

The following is an example of creating a reverse index.

CREATE REVERSE INDEX old_index ON participant(gold);

Example 3

The following is an example of creating a multiple column index.

CREATE INDEX name_nation_idx ON athlete(name, nation_code);