Modify an index by using ALTER INDEX-Lindorm(Lindorm)-阿里云帮助中心

The ALTER INDEX statement modifies columns in a search index or changes the index status of a secondary or search index. When importing or updating large amounts of data, disable the index with ALTER INDEX to improve performance.

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 | DISABLED }

Important

If you modify a secondary index, ALTER INDEX only supports modifying the index status.
You can specify properties for new columns only when you modify a search index.

Compatibility

LindormTable supports three types of indexes: secondary index, search index, and columnar index. The following table describes the compatibility of each index type with the clauses of the ALTER INDEX statement.

Syntax element	Secondary index	Search index	Columnar index
Add columns	✖️	〇	〇
alter_state_expression	〇	〇	✖️

Usage

Add columns

You can add columns only to a search index.

Search index: You can add index key properties to new columns when you add one or more columns. For example, ADD COLUMNS(c1,c3,p1(type=text,analyzer=ik)) adds three index columns c1, c3, and p1, and specifies that the p1 column uses the ik analyzer. For information about the index key properties that you can add, see Search index key properties (option_definition).
Note
Historical data is not automatically synchronized to new index columns. To index historical data for these columns, rebuild the index. For instructions, see Rebuild the search index.
Columnar index: You can add regular columns or static mapping rules for JSON columns to a columnar index. For more information, see Add columns to a columnar table (Public Preview).

alter_state_expression

You can set the index status to one of the following states:

Parameter	Description
ACTIVE	The index is available and can be used in queries.
INACTIVE	The index is unavailable and ignored by the query optimizer, but continues to receive data updates from the base table.
DISABLED	The index is disabled, ignored by the query optimizer, and no longer receives data updates from the base table. Important Changing the status of a secondary index directly from `DISABLED` to `ACTIVE` can cause data loss. You must rebuild the index before re-enabling it.

Examples

The following statements create the base table and indexes used in the examples:

-- Create a 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

Disable the index idx1.

ALTER INDEX idx1 ON test DISABLED;

Verify the result

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

Enable an index

ALTER INDEX IF EXISTS idx1 ON test ACTIVE;

Verify the result

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

Add columns to a search index

Add columns c1 and c3 to the search index idx2, and specify index key properties for 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 newly added index columns. You must rebuild the index to include this historical data. For more information, see Rebuild the search index.

Verify the result

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

Add columns to a columnar index

ALTER INDEX IF EXISTS idx3 ON test ADD COLUMNS(c3);

For more information about the syntax, see Add columns to a columnar table (Public Preview).

Rebuild a search index

This action synchronizes all data from the base table to the search index.

Synchronous build of a search index: The ALTER INDEX statement returns only after the index build is complete.
```
ALTER INDEX IF EXISTS idx2 ON test REBUILD;
```
Asynchronous search index build: The ALTER INDEX statement returns immediately, regardless of whether the index is successfully built.
```
ALTER INDEX IF EXISTS idx2 ON test REBUILD ASYNC;
```