CREATE INDEX

Purpose

You can use this statement to create an index. An index is a database structure created for a table to sort data in one or more columns of the table in a specific order. It improves the query speed and reduces the performance overhead of database systems.

In the current version of OceanBase Database, indexes are classified into unique indexes and non-unique indexes. When you create an index, you can use the STORING(column_name,...) clause to specify the redundant columns in the indexed table. The redundant columns of an index table include the columns that you specified and the primary key columns if the table has a primary key or hidden primary key columns if the table does not have a primary key. Storing additional columns in the index can provide the database optimizer with more options.

Required privileges

To execute the CREATE INDEX statement, you must have the INDEX privilege on the corresponding objects. 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...])
    [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: the sort column or expression, such as c1=c1. Boolean expressions are not allowed. Currently, you cannot create function-based indexes on generated columns in OceanBase Database. For more information about the expressions supported by function-based indexes, see System functions supported for function-based indexes.

• ASC: Optional. Specifies the ascending order. The descending order is not supported.

• opt_null_pos: The position of NULL values after sorting. Valid values:

  • empty: The position is not specified, and the default behavior of the database management system is performed.

  • NULLS LAST: Specifies to sort NULL values after non-NULL values.

  • NULLS FIRST: Specifies to sort NULL values before non-NULL values.

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

The following sample statement creates an index named index3 on the t3 table, sorts the index by the c1 column in ascending order, and specifies to sort NULL values after non-NULL values.

CREATE INDEX index3 ON t3 (c1 ASC NULLS LAST);

index_option

• GLOBAL: specifies to create a global index.

• LOCAL: specifies to create a local index.

• BLOCK_SIZE [=] integer: the size of an index block. In other words, the number of bytes in each index block.

• COMMENT STRING_VALUE: adds a comment to the index.

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

• WITH ROWID: specifies to create an index that contains the row ID.

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

• index_using_algorithm: the algorithm used for the index.

• visibility_option: the visibility of the index.

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

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

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

• physical_attributes_option: the physical attributes of the index.

• parallel_option: the degree of parallelism (DOP) of the index.

  • PARALLEL [=] integer: the DOP of the execution, which is an integer.

  • NOPARALLEL: disables 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

Create a columnstore index for a table.

1. Use the following SQL statement to create a table named test_tbl1.

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

2. Create a columnstore index named idx1_test_tbl1 on the test_tbl1 table and reference the col1 column.

   CREATE INDEX idx1_test_tbl1 ON test_tbl1 (col1) WITH COLUMN GROUP(each column);
   

References

• Overview

• Create an index

• Query indexes

• Drop an index