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 index 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. The additional columns stored in indexes can provide more choices for the database optimizer.

Required privileges

To create indexes on a table by using the CREATE INDEX statement, you must have the INDEX privilege on the table. 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: the sort column or expression, such as c1=c1. Boolean expressions are not allowed. You cannot create function-based indexes on generated columns in the current version of OceanBase Database. For more information about the expressions supported by function-based indexes, see System functions supported for function-based indexes.

• ASC: specifies to sort column values in ascending order. The descending order is not supported. This option is optional.

• 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: the ID of the sort key. This option is optional.

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, namely 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 stored in the index.

• WITH ROWID: creates 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 maximum partition ID 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 hybrid rowstore-columnstore 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. Create a table named test_tbl1.

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

2. On the test_tbl1 table, create a columnstore index named idx1_test_tbl1 that references 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