How do I use the ALTER INDEX syntax to change indexes - Lindorm

You can use the ALTER INDEX syntax to change the columns in a search index and change the status of a search index or secondary index. If you need to import or update large amounts of data, we recommend that you use the ALTER INDEX syntax to disable indexes in advance to ensure import efficiency or accelerate the update.

Syntax

alter_index_statement  ::=  ALTER INDEX [IF EXISTS] index_identifier
                            ON table_identifier
                            {
                              ADD COLUMNS '(' alter_key_expression ')'
                              |
                              DROP COLUMNS '(' alter_key_expression ')'
                              |
                              alter_state_expression  
                            }
                            [ASYNC]
alter_key_expression   ::=  index_key_definition [ (',' index_key_definition)* ]
index_key_definition   ::=  {
                                  column_identifier [ DESC ]
                                  | column_identifier'(' column_options ')' 
                            }
alter_state_expression ::=  { ACTIVE | INACTIVE }

Important

For a secondary index, you can use the ALTER INDEX syntax only to change the status of the index.
For a search index, you can use the ALTER INDEX syntax to add index columns with their attributes specified.

Supported parameters

LindormTable supports the following types of indexes: secondary indexes, search indexes, and columnar indexes. The following table describes the parameters of the ALTER INDEX syntax supported by secondary indexes, search indexes, and columnar indexes.

Parameter	Secondary index	Search indexe	Columnar index
ADD COLUMNS	️️️️️️✖️	〇	〇
alter_state_expression	〇	〇	️️️️️️✖️

Parameters

ADD COLUMNS

You cannot add columns to secondary indexes.

Search index: You can add one or more columns and index key attributes for the new columns. For example, you can use ADD COLUMNS(c1,c3,p1(type=text,analyzer=ik)) to add index columns c1, c3, and p1 and specify the IK analyzer for column p1. For information about the index key attributes that you can add, go to the Index key attributes for search indexes (option_definition) tab in the Index key expression (index_key_expression) section of the CREATE INDEX topic.

alter_state_expression

This parameter specifies the statement used to change the index status. The following table describes the supported index status.

State	Description
ACTIVE	The index is active.
INACTIVE	The index is inactive.
DISABLED	The index is disabled. If you use the syntax to change an index in this state, the state of the index becomes INACTIVE.

Examples

In the following examples, the base table and the index are created by executing the following statements:

-- Create the base table.
CREATE TABLE test (
  p1 VARCHAR NOT NULL,
  p2 INTEGER NOT NULL,
  c1 BIGINT,
  c2 DOUBLE,
  c3 VARCHAR,
  c4 TIMESTAMP,
  PRIMARY KEY(p1, p2)
) WITH (CONSISTENCY = 'strong', MUTABILITY='MUTABLE_LATEST');

-- Create a secondary index named idx1.
CREATE INDEX idx1 ON test(c1 desc) include(c3,c4) WITH (COMPRESSION='ZSTD');

-- Create a search index named idx2.
CREATE INDEX IF NOT EXISTS idx2 USING SEARCH ON test(p1, p2, c2);

-- Create a columnar index named idx3.
CREATE INDEX idx3 USING COLUMNAR ON test(p1, p2, c1) 
PARTITION BY ENUMERABLE (p2, bucket(16,p1)) 
WITH (
  `lindorm_columnar.user.index.database` = 'default',
  `lindorm_columnar.user.index.table` = 'test');

Disable an index

You can execute the following statement to disable the index idx1. After idx1 is disabled, it still updates with the data changes in the base table to ensure that idx1 can be immediately used when it is enabled again.

ALTER INDEX idx1 ON test DISABLED;

Verify the result

You can execute the SHOW INDEX FROM test; statement to verify the change result.

Enable a disabled index

ALTER INDEX IF EXISTS idx1 ON test ACTIVE;

Verify the result

You can execute the SHOW INDEX FROM test; statement to check the index status.

Add columns to a search index

You can execute the following statement to add the c1 and c3 columns to the search index idx2 and configure the attributes of the c3 column.

ALTER INDEX IF EXISTS idx2 ON test ADD COLUMNS(c1,c3(type=text,analyzer=ik));

Verify the result

You can execute the SHOW INDEX FROM test; statement to view the index columns.