Lindorm:ALTER INDEX

Applicable engines and versions

The ALTER INDEX is applicable to all versions of LindormTable.

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 parameters

LindormTable supports the following two types of indexes: secondary indexes and search indexes.

The following table describes the parameters of the ALTER INDEX syntax supported by secondary indexes and search indexes.

Parameters

ADD COLUMNS

You can use this syntax to add index columns only to search 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 more information about the attributes that you can configure for an index column, see Index key attributes for search indexes (option_definition).

alter_state_expression

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

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

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 secondary 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.

Enable a disabled search index

• If a search index is in the DISABLED state, you can execute the following statement to rebuild and enable the search index:

  ALTER INDEX IF EXISTS idx2 ON test REBUILD;

• If a search index is in the INACTIVE state, you can execute the following statement to enable the search index:

  ALTER INDEX IF EXISTS idx2 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.

ALTER INDEX IF EXISTS idx2 ON test DROP COLUMNS(c3);