CREATE INDEX

Description

Creates an index to a specified table by using the CREATE INDEX statement. For how to write index name, Identifier.

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]} ...] ) [ WHERE <filter_predicate> ]

    | (function_name (argument_list) )

• 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.
• 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 as the number of characters 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.
• <filter_predicate>: Defines the conditions to create filtered indexes. When there are several comparison conditions between a column and a constant, filtering is available only when the conditions are connected by using AND.
• function_name (argument_list): Defines the conditions to create function-based indexes.

Note In versions lower than CUBRID 9.0, index names can be deleted; however, from the CUBRID 9.0 version, it is no longer supported.

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 idx_game_nation_code ON game(nation_code(1));