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 indexes, non-unique indexes, function indexes, columnstore indexes, and spatial indexes. When creating an index, you can use the STORING(column_name,...) clause 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 if the primary table does not have a primary key). Storing additional columns in the index provides 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 classification 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 based on which to sort. Boolean expressions are not allowed, for example, c1=c1. OceanBase Database currently does not allow the creation of 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 order. Valid values:

  • empty: Specifies that the position of NULL values is not specified. By default, the database management system will follow its default behavior.

  • NULLS LAST: Specifies that NULL values appear after non-NULL values in the sorted result.

  • NULLS FIRST: Specifies that NULL values appear before non-NULL values in the sorted result.

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

For example, create an index named IDX_TBL1_C1 on the c1 column of the TEST_TBL1 table, specifying that the index sorts the c1 column in ascending order and that NULL values appear after non-NULL values in the sorted 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: 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 used by 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 parallelism of the index.

  • PARALLEL [=] integer: Specifies the level of parallel execution. 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 redundant index for both rowstore and columnstore.
• 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 for 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 for 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

• Drop an index