How do I use DROP INDEX to delete an index - Lindorm - Alibaba Cloud Documentation Center

You can use the DROP INDEX syntax to delete an existing index.

Applicable engines and versions

The DROP INDEX syntax is applicable to all versions of LindormTable.

Important

To use the DROP INDEX syntax to delete a search index or columnar index, the Lindorm SQL version must be 2.6.1 or later. For more information about how to view the version of Lindorm SQL, see SQL versions.

Syntax

drop_index_statement     ::= DROP INDEX [IF EXISTS] index_identifier 
                             ON table_identifier;

Important

Before you execute a DROP INDEX statement to delete a secondary index or search index, you must execute the ALTER INDEX statement to disable the index and then execute the SHOW INDEX statement to check whether the index is in the INACTIVE state. For more information, see ALTER INDEX and SHOW INDEX.
If a table is deleted, the indexes of the table are also deleted.

Examples

In this example, the following statements are executed in advance to create a table and the indexes of the table:

-- 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, c1, c2, c3);

Delete a secondary index

Execute the following statement to disable the secondary index idx1:
```
ALTER INDEX IF EXISTS idx1 ON test DISABLED;
```
Execute the following statement to delete the secondary index idx1:
```
DROP INDEX IF EXISTS idx1 ON test;
```

Verify the result

Execute the following statement to check whether the index is deleted:

SHOW INDEX FROM test;

Delete a search index

Execute the following statement to disable the search index idx2:
```
ALTER INDEX IF EXISTS idx2 ON test DISABLED;
```
Execute the following statement to delete the search index idx2:
```
DROP INDEX IF EXISTS idx2 ON test;
```

Verify the result

Execute the following statement to check whether the index is deleted:

SHOW INDEX FROM test;