The DROP INDEX statement allows you to drop a named primary or secondary index, dropping an index that has replicas will also drop all of the replica indexes too. You can drop unnamed primary indexes using the DROP PRIMARY INDEX statement.
DROP INDEX named_keyspace_ref.index_name [USING GSI | VIEW];
index name is a unique name that is provided to identify the index.
[ namespace-name :] keyspace-name
identifier that refers to the bucket name. Specifies the bucket as source for which the index needs to be created. You can add an optional namespace name to the keyspace name in this way:
namespace-name : keyspace-name
For example, main:customer indicates the customer keyspace in the main namespace. If the namespace name is omitted, the default namespace in the current session is used.
USING GSI | VIEW
USING clause specify the index type to use. Indexes can be created using GSI or views. If USING clause is not specified, by default GSI is used as the indexer.
When using memory-optimized indexes, DROP INDEX is an expensive operation and may take a few minutes to complete.
If you drop an index with replicas while one of the index nodes is failed over, then only the replicas in the active index nodes are dropped. If the failed-over index node is recovered, then the orphan replica will be dropped when this failed-over indexer is added back to cluster.
If you drop an index with replicas when one of the index nodes is unavailable but not failed over, the drop index operation may fail.
|We recommend that you do not drop (or create) secondary indexes when any node with a secondary index role is down as this may result in duplicate index names.|
User executing the DROP INDEX statement must have the Query Manage Index privilege granted on the keyspace/bucket. For more details about user roles, see Authorization.
This example creates a secondary index on the travel-sample bucket. Once the index creation statement comes back, system:indexes is queried for status of the index.
CREATE INDEX `idx-callsign` ON `travel-sample`(callsign) USING GSI; SELECT * FROM system:indexes WHERE name="idx-callsign";
Subsequently, the index is dropped with the following statement and it no longer is reported in the system:indexes output.
DROP INDEX `travel-sample`.`idx-callsign` USING GSI; SELECT * FROM system:indexes WHERE name="idx-callsign";