ALTER INDEX

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 two types of indexes: secondary index, search 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
Add columns	✖️	〇
alter_state_expression	〇	〇

Usage

Add columns

You cannot add columns to a secondary index.

You can add one or more columns and specify index key properties for the new 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 more information about the available index key properties, see Search index key properties (option_definition).

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

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.

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