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 MySQL mode.

Syntax

CREATE [hint_options] [SPATIAL | UNIQUE] INDEX [IF NOT EXISTS] index_name
    [USING BTREE] ON table_name (sort_column_key [, sort_column_key... ])
    [index_option...] [partition_option] [index_column_group_option]

sort_column_key:
      column_name [(integer)] [ASC] [ID id]
    | (index_expr) [ASC] [ID id]

index_option:  
      Global
    | LOCAL
    | BLOCK_SIZE integer
    | COMMENT STRING_VALUE
    | STORING (column_name [, column_name...])
    | WITH_ROWID
    | WITH PARSER STRING_VALUE
    | index_using_algorithm
    | visibility_option
    | DATA_TABLE_ID data_table_id
    | INDEX_TABLE_ID index_table_id
    | VIRTUAL_COLUMN_ID virtual_column_id
    | MAX_USED_PART_ID used_part_id

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

Parameters

sort_column_key

• column_name [(integer)] [ASC] [ID id]: specifies a column as the sort key.

  • column_name: the name of the column to sort.

  • integer: the length limit of the sort key. This option is optional.

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

  • 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 and sorts the index by the c1 column in ascending order.

  CREATE INDEX index3 ON t3 (c1 ASC);
  

• (index_expr) [ASC] [ID id]: specifies to use an index expression as the sort key. You can define an index expression by using expressions or functions. The index expression setting contains the following options:

  • (index_expr): the index expression, which can be a Boolean expression, such as c1=c1. 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.

  • ID id: the ID of the sort key. This option is optional.

  The following example creates an index named index4 on the t4 table, uses c1+c2 as the index expression, and sorts the index in ascending order.

  CREATE INDEX index4 ON t4 ((c1 + c2) ASC);
  

When you create an index, you can specify multiple columns as the sort key and separate them with commas (,). The following example creates an index named index5 on the t5 table and uses the c1 column and the c2+c3 expression as the index sort key.

CREATE INDEX index5 ON t5 (c1, (c2+c3));

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, that is, the number of bytes in each index block.

• COMMENT STRING_VALUE: adds a comment to the index.

• STORING (column_name [, column_name...]): the columns to be stored in the index. Separate multiple columns with commas (,).

• 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. You can specify USING BTREE or USING HASH. You cannot use the USING HASH clause when you create multi-valued indexes.

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

• VIRTUAL_COLUMN_ID virtual_column_id: the ID of the virtual column.

• MAX_USED_PART_ID used_part_id: the maximum partition ID allowed for the index.

index_column_group_option

• WITH COLUMN GROUP(all columns, each column): specifies to create a hybrid rowstore-columnstore 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 INT, col2 VARCHAR(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