Manage Search & Secondary Indexes with ALTER INDEX - Lindorm

Modifies an existing index on a LindormTable table. Use ALTER INDEX to add columns to a search index, change the status of a search index or secondary index, or rebuild a search index.

To maximize write throughput during bulk data imports or updates, disable the affected indexes before the operation, then re-enable them afterward.

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 }

Supported operations

The following table shows which operations are available for each index type.

Operation	Secondary index	Search index
`ADD COLUMNS`	Not supported	Supported
Change index status	Supported	Supported

Parameters

ADD COLUMNS

Adds one or more columns to a search index. You can configure search-specific attributes for each column inline.

Example: ADD COLUMNS(c1, c3, p1(type=text, analyzer=ik)) adds three columns and applies the IK analyzer to p1.

For the full list of configurable attributes, see the index key attributes for search indexes in the CREATE INDEX topic.

Important

ADD COLUMNS is not supported for secondary indexes.

Note

Historical data is not automatically synchronized to newly added index columns. To index existing data, rebuild the search index after adding columns.

alter_state_expression

Changes the status of an index. Use this to enable or disable an index.

State	Description
`ACTIVE`	The index is active.
`INACTIVE`	The index is inactive.
`DISABLED`	The index is disabled. Running `ALTER INDEX` on an index in this state transitions it to `INACTIVE`.

Examples

All examples use the following base table and indexes:

-- Create the primary 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 the secondary index idx1
CREATE INDEX idx1 ON test(c1 desc) include(c3,c4) WITH (COMPRESSION='ZSTD');

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

Disable an index

ALTER INDEX idx1 ON test DISABLED;

Verify the result

SHOW INDEX FROM test;

Check that idx1 shows status DISABLED in the output.

Enable a disabled index

ALTER INDEX IF EXISTS idx1 ON test ACTIVE;

Verify the result

SHOW INDEX FROM test;

Check that idx1 shows status ACTIVE in the output.

Add columns to a search index

The following statement adds columns c1 and c3 to search index idx2, and configures c3 to use full-text search with the IK analyzer.

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

Important

Historical data in c1 and c3 is not automatically indexed. To include existing data, rebuild the search index after this operation.

Verify the result

SHOW INDEX FROM test;

Check that c1 and c3 appear in the index column list for idx2.

Rebuild a search index

Rebuilding synchronizes all existing table data into the search index.

Synchronous rebuild — the statement returns only after the rebuild completes.
```
ALTER INDEX IF EXISTS idx2 ON test REBUILD;
```
Asynchronous rebuild — the statement returns immediately. The rebuild continues in the background.
```
ALTER INDEX IF EXISTS idx2 ON test REBUILD ASYNC;
```