DROP INDEX

reference

The DROP INDEX statement allows you to drop a named primary index or a 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.

RBAC Privileges

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.

Syntax

drop-index ::= DROP INDEX index-path [ index-using ]

Index Path

index-path ::= [ namespace ':' ] keyspace '.' index-name

(namespace ':')? keyspace '.' index-name

namespace: (Optional) An identifier that refers to the namespace of the indexed keyspace. Currently, only the default namespace is available. If the namespace name is omitted, the default namespace in the current session is used.
keyspace: (Required) An identifier that refers to the bucket name or keyspace. It specifies the bucket as the source for the index which needs to be deleted.
index-name: (Required) A unique name that identifies the index.

For example, default:`travel-sample`.`def-type` indicates the def-type index in the travel-sample keyspace in the default namespace.

If there is a hyphen (-) inside the keyspace or index name, you must wrap it in backticks (` `).

USING Clause

index-using ::= USING GSI

In Couchbase Server 6.5 and later, the index type for a secondary index must be Global Secondary Index (GSI). The USING GSI keywords are optional and may be omitted.

Usage

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.

Example

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