CREATE INDEX

Purpose

This statement is used to create an index. An index is a structure created on a table that sorts the values of one or more columns in the database table. Its main purpose is to improve query speed and reduce the performance overhead of the database system.

OceanBase Database currently supports the following types of indexes: unique indexes, non-unique indexes, function indexes, columnstore indexes, and spatial indexes. When creating an index, you can use the STORING(column_name,...) option to specify redundant columns in the index table. In addition to the columns specified by the user, the index table will automatically include the primary key columns of the primary table (or the hidden primary key columns if the primary table does not have a primary key). Storing additional columns in the index can provide the database optimizer with more options.

Privilege requirements

To execute the CREATE INDEX statement, the current user must have at least the INDEX privilege on the corresponding object. For more information about privileges in OceanBase Database, see Privilege types in Oracle mode.

Syntax

CREATE [hint_options]  [UNIQUE] INDEX index_name
    [USING BTREE] ON table_name (sort_column_key [, sort_column_key...])
    [INDEXTYPE IS MDSYS.SPATIAL_INDEX] [index_option...] [partition_option] [index_column_group_option]

sort_column_key:
    index_expr [ASC] opt_null_pos [ID id]

opt_null_pos:
      empty
    | NULLS LAST
    | NULLS FIRST

index_option:
      GLOBAL
    | LOCAL
    | BLOCK_SIZE [=] integer
    | COMMENT STRING_VALUE
    | STORING (column_name_list)
    | WITH ROWID
    | WITH PARSER STRING_VALUE
    | index_using_algorithm
    | visibility_option
    | DATA_TABLE_ID [=] data_table_id
    | INDEX_TABLE_ID [=] index_table_id
    | MAX_USED_PART_ID [=] used_part_id
    | physical_attributes_option
    | parallel_option

parallel_option:
      PARALLEL [=] integer
    | NOPARALLEL

index_column_group_option:
    WITH COLUMN GROUP([all columns, ]each column)

Parameters

sort_column_key

• index_expr: Specifies the column or expression on which to sort. Boolean expressions are not allowed, such as c1=c1. OceanBase Database currently does not support creating function indexes on generated columns. For more information about the expressions supported by function indexes, see List of system functions supported by function indexes.

• ASC: Optional. Specifies to sort in ascending order. Descending (DESC) order is not supported.

• opt_null_pos: Specifies the position of NULL values in the sorting results. Valid values: empty, NULLS LAST, and NULLS FIRST.

  • empty: Specifies not to specify the position of NULL values. By default, the database management system will handle this.

  • NULLS LAST: Specifies to place NULL values after non-NULL values in the sorting results.

  • NULLS FIRST: Specifies to place NULL values before non-NULL values in the sorting results.

• ID id: Optional. Specifies the ID of the sorting key.

For example, create an index named IDX_TBL1_C1 on the c1 column of the TEST_TBL1 table in ascending order and specify that NULL values appear after non-NULL values in the sorting results.

obclient> CREATE TABLE TEST_TBL1 (
    id NUMBER PRIMARY KEY,
    c1 VARCHAR2(100),
    c2 NUMBER,
    c3 DATE
);

obclient> CREATE INDEX IDX_TEST_TBL1_C1 ON TEST_TBL1 (c1 ASC NULLS LAST);

index_option

• GLOBAL: Specifies to create a global index.

• LOCAL: Specifies to create a local index.

• BLOCK_SIZE [=] integer: Specifies the size of the index block, that is, the number of bytes in each index block.

• COMMENT STRING_VALUE: Adds a comment to the index.

• STORING (column_name_list): Specifies the columns to be stored in the index.

• WITH ROWID: Specifies to create an index that contains row identifiers.

• WITH PARSER STRING_VALUE: Specifies the parser required for the index.

• index_using_algorithm: Specifies the algorithm to be used for the index.

• visibility_option: Specifies the visibility of the index.

• DATA_TABLE_ID [=] data_table_id: Specifies the ID of the data table to which the index belongs.

• INDEX_TABLE_ID [=] index_table_id: Specifies the ID of the index table.

• MAX_USED_PART_ID [=] used_part_id: Specifies the maximum ID of the used partition for the index.

• physical_attributes_option: Specifies the physical attributes of the index.

• parallel_option: Specifies the parallelism of the index.

  • PARALLEL [=] integer: Specifies the parallelism level. integer is an integer that indicates the degree of parallelism.

  • NOPARALLEL: Specifies to disable parallel execution.

index_column_group_option

• WITH COLUMN GROUP(all columns, each column): Specifies to create a rowstore columnstore redundant index.
• WITH COLUMN GROUP(all columns): Specifies to create a rowstore index.
• WITH COLUMN GROUP(each column): Specifies to create a columnstore index.

Examples

Use the following SQL statement to create a columnstore index on a table.

1. Run the following SQL statement to create a table named TEST_TBL2.

   CREATE TABLE TEST_TBL2 (col1 NUMBER, col2 VARCHAR2(50));
   

2. Run the following SQL statement to create a columnstore index named IDX_TEST_TBL2_COL1 on the TEST_TBL2 table, which references the col1 column.

   CREATE INDEX IDX_TEST_TBL2_COL1 ON TEST_TBL2 (col1) WITH COLUMN GROUP(each column);
   

References

• Index overview

• Create an index

• View indexes

• Delete an index