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 index types: unique index, non-unique index, function index, columnstore index, and spatial index. When creating an index, you can use STORING(column_name,...) 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 key table (or the hidden primary key columns if there is no primary key table). Storing additional columns in the index can provide the database optimizer with more options.

Privilege requirements

To create an index by executing the CREATE INDEX statement, the current user must have at least the INDEX privilege on the corresponding object. For more information about OceanBase Database privileges, see Privilege classification in Oracle-compatible 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 Supported system functions for 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 sort result. Valid values:

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

  • NULLS LAST: Specifies that null values appear after non-null values in the sort result.

  • NULLS FIRST: Specifies that null values appear before non-null values in the sort result.

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

For example, create an index named IDX_TBL1_C1 on the TEST_TBL1 table, specify that the index sorts the values in the c1 column in ascending order, and specify that null values appear after non-null values in the sort result.

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: Creates 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 partition used by the index.

• physical_attributes_option: Defines the physical attributes of the index.

• parallel_option: Specifies the parallel execution settings for the index.

  • PARALLEL [=] integer: Specifies the parallel execution 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 and 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. Execute the following SQL statement to create a table named TEST_TBL2.

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

2. 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

• Overview of indexes

• Create an index

• View indexes

• Delete an index