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 unique and non-unique 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 table has no explicit primary key). Storing additional columns in the index provides the database optimizer with more options for query optimization.

Limitations and considerations

• If all indexed columns are deleted from the table, the created indexes become invalid.
• Full-text indexes can only be applied to columns of type CHAR, VARCHAR, and TEXT.
• The current version only supports creating local (LOCAL) full-text indexes.
• The UNIQUE keyword cannot be specified when creating a full-text index.
• If you want to create a full-text index involving multiple columns, ensure that these columns have the same character set.
• The WITH PARSER {SPACE | NGRAM | BENG | IK} clause can only be used when creating a full-text index.

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 OceanBase Database privileges, see Privilege types in MySQL mode.

Syntax

CREATE [hint_options] [index_type] INDEX [IF NOT EXISTS] index_name
    [USING BTREE] ON table_name (sort_column_key [, sort_column_key... ])
    [WITH PARSER tokenizer_option]
    [PARSER_PROPERTIES[=](parser_properties_list)]
    [index_option...] [partition_option] [index_column_group_option]

index_type:
    SPATIAL
    | UNIQUE
    | FULLTEXT

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

tokenizer_option:
    SPACE
    | NGRAM
    | BENG
    | IK
    | NGRAM2

parser_properties_list:
    parser_properties, [parser_properties]

parser_properties:
    min_token_size = int_value
    | max_token_size = int_value
    | ngram_token_size = int_value
    | ik_mode = 'char_value'
    | min_ngram_size = int_value
    | max_ngram_size = int_value

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

index_type

• SPATIAL: specifies that a spatial index is created.
• UNIQUE: specifies that a unique index is created.
• FULLTEXT: specifies that a full-text index is created.

For more information about how to create indexes of different types, see Create an index.

sort_column_key

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

  • column_name: the name of the column to be sorted.

  • integer: an optional parameter that specifies the maximum length of the sort key.

  • ASC: an optional parameter that specifies ascending order. Descending order (DESC) is not supported.

  • ID id: an optional parameter that specifies the ID of the sort key.

  For example, create an index named index3 on the c1 column of the t3 table and specify that the index is sorted in ascending order.

  CREATE INDEX index3 ON t3 (c1 ASC);
  

• (index_expr) [ASC] [ID id]: specifies an index expression as the sort key. An index expression can be defined by using an expression or a function. It can contain the following options:

  • (index_expr): the index expression, which can be a boolean expression, such as c1=c1. OceanBase Database does not allow you to create a function index on a generated column. For more information about the expressions supported by a function index, see Supported system functions for function indexes.

  • ASC: an optional parameter that specifies ascending order. Descending order (DESC) is not supported.

  • ID id: an optional parameter that specifies the ID of the sort key.

  For example, create an index named index4 on the t4 table, specify c1+c2 as the index expression, and specify that the index is sorted in ascending order.

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

You can specify multiple sort columns when you create an index. Separate the sort columns with commas (,). For example, create an index named index5 on the t5 table, specify c1 and c2+c3 as the sort keys, and specify that the index is sorted in ascending order.

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

tokenizer_option

• SPACE: the default value. Specifies that the text is tokenized by using spaces. You can specify the following parameters:

  Parameter	Value range
  min_token_size	[1, 16]
  max_token_size	[10, 84]

• NGRAM: specifies that the text is tokenized by using the N-Gram method. You can specify the following parameters:

  Parameter	Value range
  ngram_token_size	[1, 10]

• NGRAM2: specifies that the text is tokenized by using the N-Gram2 method. You can specify the following parameters:

  Parameter	Value range
  min_ngram_size	[1, 16]
  max_ngram_size	[1, 16]

  Note

  For OceanBase Database V4.3.5, the NGRAM2 tokenizer is supported starting from V4.3.5 BP2.

• BENG: specifies that the text is tokenized by using the Beng method. You can specify the following parameters:

  Parameter	Value range
  min_token_size	[1, 16]
  max_token_size	[10, 84]

• IK: specifies that the text is tokenized by using the IK method. Only the utf-8 character set is supported. You can specify the following parameter:

  Parameter	Value range
  ik_mode	smart max_word

  Note

  For OceanBase Database V4.3.5, the IK tokenizer is supported starting from V4.3.5 BP1.

You can use the TOKENIZE function to view the tokenization result of the text in the specified tokenizer and JSON format.

parser_properties

• min_token_size: the minimum token length. Default value: 3. Value range: 1 to 16.

• max_token_size: the maximum token length. Default value: 84. Value range: 10 to 84.

• ngram_token_size: the token length for the NGRAM tokenizer. This parameter is valid only for the NGRAM tokenizer. Default value: 2. Value range: 1 to 10.

• ik_mode: the tokenization mode for the IK tokenizer. Valid values:

  • smart: the default value. The dictionary is used to improve the accuracy of tokenization. The boundaries of the dictionary words are prioritized, which may reduce unnecessary expansions.
  • max_word: the dictionary words are identified, but the maximum expansion of the text is not affected. Even if the dictionary contains the words, the max_word mode still tries to split the text into more words.

Here is an example:

1. Create a table named tbl1.

   CREATE TABLE tbl1 (col1 VARCHAR(200), col2 TEXT);
   

2. Create a full-text index on the col2 column of the tbl1 table. Specify the IK tokenizer and set the tokenizer parameters by using the PARSER_PROPERTIES option.

   CREATE FULLTEXT INDEX fidx_tbl1 ON tbl1(col2) WITH PARSER IK PARSER_PROPERTIES=(ik_mode='max_word');
   

index_option

• GLOBAL: creates a global index.

• LOCAL: creates a local index.

• BLOCK_SIZE integer: specifies the size of an index block, in bytes.

• COMMENT STRING_VALUE: adds a comment to the index.

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

• 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 used for the index. You can specify USING BTREE or USING HASH. However, you cannot create a multi-value index by using USING HASH.

• visibility_option: specifies the visibility of the index.

• DATA_TABLE_ID data_table_id: specifies the data table ID to which the index belongs.

• INDEX_TABLE_ID index_table_id: specifies the index table ID.

• VIRTUAL_COLUMN_ID virtual_column_id: specifies the virtual column ID.

• MAX_USED_PART_ID used_part_id: specifies the maximum partition ID used by the index.

index_column_group_option

• WITH COLUMN GROUP(all columns, each column): creates a rowstore columnstore redundant index.
• WITH COLUMN GROUP(each column): creates a columnstore index.

Examples

Use the following SQL statement to create a columnstore index on a table.

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

   CREATE TABLE test_tbl1 (col1 INT, col2 VARCHAR(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 of indexes

• Create an index

• View indexes

• Delete an index