Creates an index.

Syntax

CREATE [ UNIQUE ] INDEX name ON table
  ( { column | ( expression ) } )
  [ TABLESPACE tablespace ]

Description

CREATE INDEX constructs an index (name) on the specified table. Indexes are used to improve database performance. However, inappropriate use can result in unfavorable performance.

The key fields for the index are specified as column names or expressions written in parentheses. Multiple fields can be specified to create multicolumn indexes.

An index field can be an expression computed from the values of one or more columns of a table row. This function can be used for quick data access based on some conversions of the basic data. For example, an index computed on UPPER(col) allows the WHERE UPPER(col) = 'JIM' clause to use an index.

PolarDB databases compatible with Oracle provide the B-tree index method. The B-tree index method is an implementation of Lehman-Yao high-concurrency B-trees.

Indexes are not used for IS NULL clauses by default.

All functions and operators used in an index definition must be immutable. Their results must depend only on their arguments and never on external influence such as the contents of another table or the current time. This restriction ensures that the behavior of the index is properly defined. To use a user-defined function in an index expression, you must mark the function as immutable when you create it.

If you create an index on a partition table, the CREATE INDEX command propagates indexes to the partitions of the table.

Note You can specify up to 32 fields in a multicolumn index.

Parameters

Parameter Description
UNIQUE Causes the system to check for duplicate values in the table when the index is created if data already exists and each time data is added. If an attempt to insert or update data results in duplicate entries, an error is generated.
name The name of the index to be created. The index name cannot contain a schema name. The index is always created in the same schema as its parent table.
table The name of the table to be indexed. The name can be schema-qualified.
column The name of a column in the table.
expression An expression based on one or more columns of the table. The expression is enclosed in parentheses in most cases, as shown in the syntax. However, if the expression has the form of a function call, the parentheses can be omitted.
tablespace The tablespace in which to create the index. If this parameter is not specified, default_tablespace is used. If default_tablespace is an empty string, the default tablespace of the database is used.

Example

Create a B-tree index on the ename column in the emp table:

CREATE INDEX name_idx ON emp (ename);

Create an index that is the same as the preceding one, but place it in the index_tblspc tablespace:

CREATE INDEX name_idx ON emp (ename) TABLESPACE index_tblspc;