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 ')'
                              |
                              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 two types of indexes: secondary and search indexes. The following table describes the parameters of the ALTER INDEX syntax supported by secondary indexes and search indexes.

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

Parameters

ADD COLUMNS

You cannot add columns to secondary indexes.

When you add one or more columns to a search index, you can configure attributes for the columns. For example, you can specify the ADD COLUMNS(c1,c3,p1(type=text,analyzer=ik)) clause to add three columns c1, c3, and p1 to a search index and specify that the IK analyzer is used for the p1 column. 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);

Disable an index

Disable index idx1.

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));

Note

Historical data is not automatically synchronized to the new index columns. If you want to index the historical data of these columns, rebuild the search index. For more information, see Rebuild a search index.

Verify the result

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

Rebuild a search index

You can synchronize full data to the corresponding search index.

Synchronous search index building: The ALTER INDEX statement is returned only after the index is built.
```
ALTER INDEX IF EXISTS idx2 ON test REBUILD;
```
Asynchronous search index building: The ALTER INDEX statement is returned immediately regardless of whether the index is built.
```
ALTER INDEX IF EXISTS idx2 ON test REBUILD ASYNC;
```