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 columns if no primary key exists). Storing additional columns in the index provides the database optimizer with more options.

Limitations and considerations

• If all indexed columns are deleted from a table, the indexes created on the table become invalid.
• Full-text indexes can be created only on columns of the CHAR, VARCHAR, and TEXT data types.
• Only local (LOCAL) full-text indexes can be created in the current version.
• The UNIQUE keyword cannot be specified when creating a full-text index.
• When creating a full-text index that involves multiple columns, ensure that the columns have the same character set.
• The WITH PARSER {SPACE | NGRAM | BENG | IK} option can be specified only 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 the privileges of OceanBase Database, see Privilege types in MySQL-compatible 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
    | STORAGE_CACHE_POLICY(storage_cache_policy_option)

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

storage_cache_policy_option:
    GLOBAL = {"hot"|"auto"|"none"}
    | timeline_strategy_list

timeline_strategy_list:
    BOUNDARY_COLUMN = column_name
    [, BOUNDARY_COLUMN_UNIT = {"s"|"ms"}]
    [, HOT_RETENTION = intnum retention_time_unit]


retention_time_unit:
    YEAR
    | MONTH
    | WEEK
    | DAY
    | HOUR
    | MINUTE

Parameters

index_type

• SPATIAL: specifies the creation of a spatial index.
• UNIQUE: specifies the creation of a unique index.
• FULLTEXT: specifies the creation of a full-text index.

For more information about creating indexes, see Create an index.

sort_column_key

• column_name [(integer)] [ASC] [ID id]: specifies a column name as a 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 sort the index in ascending order.

  CREATE INDEX index3 ON t3 (c1 ASC);
  

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

  • (index_expr): the index expression, which can be a boolean expression, such as c1=c1. OceanBase Database does not support creating function indexes on generated columns. For information about the expressions supported by function indexes, 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 sort the index in ascending order.

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

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

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

tokenizer_option

• SPACE: the default value. Specifies the use of space-based tokenization. You can specify the following parameters:

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

• NGRAM: specifies the use of N-gram-based tokenization for Chinese text. You can specify the following parameters:

  Parameter	Value range
  ngram_token_size	[1, 10]

• NGRAM2: specifies the use of N-gram-based tokenization for text. You can specify the following parameters:

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

• BENG: specifies the use of basic English-based tokenization. You can specify the following parameters:

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

• IK: specifies the use of IK-based tokenization for Chinese text. Only the utf-8 character set is supported. You can specify the following parameter:

  Parameter	Value range
  ik_mode	smart max_word

You can use the TOKENIZE function to view the tokenization results of text based on the specified tokenizer and JSON-formatted parameters.

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 tokenization accuracy. The boundaries of dictionary words are prioritized, which may reduce unnecessary extensions.
  • max_word: the dictionary is used to identify words, but it does not affect the maximum extension of tokenization. Even if the dictionary defines words, the max_word mode will still attempt 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 properties using PARSER_PROPERTIES.

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

index_option

• GLOBAL: specifies to create a global index.

• LOCAL: specifies to create a local index.

• BLOCK_SIZE integer: specifies the size of an index block, that is, the number of bytes in each index block.

• COMMENT STRING_VALUE: specifies a comment for the index.

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

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

• 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): specifies to create a rowstore columnstore redundant index.
• WITH COLUMN GROUP(each column): specifies to create a columnstore index.

storage_cache_policy

No action is required at this time.

Examples

Example 1

Run the following SQL statements to create a columnstore index for 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. Run the following SQL statement to create a columnstore index named idx1_test_tbl1 for the test_tbl1 table, and specify the col1 column as the reference column.

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

References

• Index overview

• Create an index

• View indexes

• Delete an index