Lindorm:CREATE INDEX

Applicable engines

The CREATE INDEX syntax is applicable only to LindormTable.

Syntax

create_index_statement   ::=  CREATE INDEX [IF NOT EXISTS] [ index_identifier ]
                              [ USING index_method_definition ]
                              ON table_identifier '(' index_key_expression ')'
                              [ INCLUDE include_expression]
                              [ PARTITION BY partition_definition ]
                              [ { ASYNC | SYNC} ]
                              [ WITH '(' index_options  ')' ]
index_method_definition  ::=  { KV | SEARCH | COLUMNAR }
index_key_expression     ::=  '('
                                  index_key_definition 
                                  |wildcard_string_literal                                 
                              ')'

index_key_definition     ::= {
                                  column_identifier [ DESC ]
                                  | column_identifier'(' column_options ')'
                                  | function_expression
                             }
                             [ (',' index_key_definition)* ]
column_options    ::=  '(' 
                           option_definition (',' option_definition )* 
                       ')'
function_expression      ::=  function_identifier 
                              '(' 
                                  column_identifer ( ',' column_identifer )* 
                              ')'
option_definition        ::=  option_identifer '=' string_literal
include_expression       ::=  '(' 
                                  column_identifier( ',' column_identifier )*
                              ')'
partition_definition     ::= {
                                {RANGE TIME} 
                                     '(' 
                                          column_identifer 
                                     ')' [ PARTITIONS number_literal ]
                                |
                                HASH '(' 
                                          column_identifer 
                                          ( ',' column_identifer )*
                                     ')' [ PARTITIONS number_literal ]
                                |
                                ENUMERABLE 
                                     '(' 
                                          column_identifer 
                                          ( ',' column_identifer )*
index_options            ::=  '(' 
                                  option_definition (',' option_definition )* 
                              ')'

Supported parameters

LindormTable allows you to use the CREATE INDEX syntax to create two types of indexes: secondary indexes and search indexes.

The following table describes the parameters that are supported when you create each type of indexes.

Parameters

You can create up to three secondary indexes, one search index for a wide table.

Index type (index_method_definition)

You can specify the type of an index by using the USING keyword when you create the index. The following table describes the values that you can configure for this parameter when you create the three types of indexes supported by LindormTable.

Index key expression (index_key_expression)

You can define one column or multiple columns as an index key. An index that consists of multiple index keys is also referred as a federated index.

Index key definition (index_key_definition)

You can use this parameter to add attributes for the index key of a search index. You can also use this parameter to specify an index key as a function expression.

Wildcard constant (wildcard_string_literal)

Only search indexes support the wildcard constant (*).

The wildcard constant (*) can be used to create an index for all columns including columns that are added to the table after the index is created. Example: CREATE INDEX IF NOT EXISTS idx5 USING SEARCH ON test(*);

Redundancy column (include_expression)

You can use the INCLUDE keyword to configure included columns in secondary indexes and search indexes. In this case, the included columns cannot be dynamic columns in the base table.

Included columns are index columns that are the same as those in the base table. Indexes with Included columns are also referred as covering indexes. A covering index can provide all data required for a query that hits the index without the need to access the base table. This way, the query performance can be ensured.

Index partitioning (partition_definition)

Only search indexes support index partitioning.

Index partitioning is an index management policy based on which the server automatically splits and stores data into different partitions and prunes partitions when the data is queried.

Search indexes support range partitioning and hash partitioning. For more information, see Partition index.

Index creation method (ASYNC|SYNC)

When you use the CREATE INDEX syntax to create an index, you can specify the ASYNC or SYNC keyword to specify whether to create the index synchronously or asynchronously.

• ASYNC: creates the index asynchronously. If you use this method, the index starts to be created immediately after the CREATE INDEX statement is executed. The response to the CREATE INDEX statement is immediately returned regardless of whether the index is successfully created.

• SYNC: creates the index synchronously. If you use this method, the index starts to be created immediately after the CREATE INDEX statement is executed. No results are returned for the CREATE INDEX statement until the index is successfully created.

The following table shows the creation methods supported by secondary indexes, search indexes, and columnar indexes.

Index attributes (index_options)

When you use the CREATE INDEX syntax to create an index, you can use the WITH keyword to specify attributes for the index. The following tables describe the attributes supported by different types of indexes.

Secondary index

Search index

Examples

In the following examples, a base table named test is created by executing the following statement:

CREATE TABLE test (
  p1 VARCHAR NOT NULL,
  p2 INTEGER NOT NULL,
  c1 BIGINT,
  c2 DOUBLE,
  c3 VARCHAR,
  c4 TIMESTAMP,
  c5 GEOMETRY(POINT),
  PRIMARY KEY(p1, p2)
) WITH (CONSISTENCY = 'strong', MUTABILITY='MUTABLE_LATEST');

CREATE INDEX idx1 ON test(c1 desc) include(c3,c4) WITH (COMPRESSION='ZSTD');

CREATE INDEX idx1 ON test(c1, c2, c3) include(c4) SYNC WITH ( COMPRESSION ='ZSTD');

CREATE INDEX idx1 ON test(c4 desc) WITH (INDEX_COVERED_TYPE ='COVERED_ALL_COLUMNS_IN_SCHEMA');

CREATE INDEX idx1 ON test(c4 desc) WITH (INDEX_COVERED_TYPE='COVERED_DYNAMIC_COLUMNS');

CREATE INDEX idx1 ON test(c4 desc) include(c5,c6)  WITH (NUMREGIONS ='32');

CREATE INDEX idx1 ON test(c3 desc) include(c5,c6) WITH (NUMREGIONS ='32', STARTKEY ='11111111', ENDKEY = '9999999');

CREATE INDEX idx1 ON test(Z-ORDER(c5));

CREATE INDEX idx1 ON test(CAST(c3 AS INTEGER));

CREATE INDEX IF NOT EXISTS idx2 USING SEARCH ON test(p1, p2, c1, c2, c3);

CREATE INDEX IF NOT EXISTS idx2 USING SEARCH ON test('*');

CREATE INDEX IF NOT EXISTS idx2 USING SEARCH ON test('*',c3(type=text,analyzer=ik,indexed=true));

CREATE INDEX IF NOT EXISTS idx2 USING SEARCH ON test(c1, c3(type=text,analyzer=ik)) WITH (indexState=ACTIVE,numShards=4);

CREATE INDEX IF NOT EXISTS idx2 USING SEARCH ON test (c1, c2, c3, c4)
PARTITION BY RANGE TIME(c4) PARTITIONS 16
WITH (indexState=ACTIVE, RANGE_TIME_PARTITION_START='30', RANGE_TIME_PARTITION_INTERVAL='7', RANGE_TIME_PARTITION_TTL='90', RANGE_TIME_PARTITION_MAX_OVERLAP='90');