You are viewing the documentation for a prerelease version.

View Latest

DROP INDEX

    +

    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-name | index-name ON keyspace-ref ) [ index-using ]
    'DROP' 'INDEX' ( index-path '.' index-name | index-name 'ON' keyspace-ref ) index-using?
    index-name

    (Required) A unique name that identifies the index.

    Index Path

    keyspace-full | keyspace-prefix | keyspace-partial

    You can use a dotted notation to specify the index and the keyspace on which the index is built. This syntax provides compatibility with legacy versions of Couchbase Server.

    If there is a hyphen (-) inside the index name or any part of the index path, you must wrap the index name or that part of the index path in backticks (` `). Refer to the examples below.

    Index Path: Full Keyspace

    keyspace-full ::= namespace ':' bucket '.' scope '.' collection
    namespace ':' bucket '.' scope '.' collection

    If the index is built on a named collection, the index path may be a full keyspace path, including namespace, bucket, scope, and collection, followed by the index name. In this case, the query context is ignored.

    namespace

    (Required) An identifier that refers to the namespace of the keyspace. Currently, only the default namespace is available.

    bucket

    (Required) An identifier that refers to the bucket name of the keyspace.

    scope

    (Required) An identifier that refers to the scope name of the keyspace.

    collection

    (Required) An identifier that refers to the collection name of the keyspace.

    For example, default:`travel-sample`.places.cities.`idx-name` indicates the idx-name index on the cities collection in the places scope in the default:`travel-sample` bucket.

    Index Path: Keyspace Prefix

    keyspace-prefix ::= [ namespace ':' ] bucket
    ( namespace ':' )? bucket

    If the index is built on the default collection in the default scope within a bucket, the index path may be just an optional namespace and the bucket name, followed by the index name. In this case, the query context should not be set.

    namespace

    (Optional) An identifier that refers to the namespace of the keyspace. Currently, only the default namespace is available. If the namespace name is omitted, the default namespace in the current session is used.

    bucket

    (Required) An identifier that refers to the bucket name of the keyspace.

    For example, default:`travel-sample`.def_type indicates the def_type index on the default collection in the default scope in the default:`travel-sample` bucket.

    Index Path: Keyspace Partial

    keyspace-partial ::= collection
    collection

    Alternatively, if the keyspace is a named collection, the index path may be just the collection name, followed by the index name. In this case, you must set the query context to indicate the required namespace, bucket, and scope.

    collection

    (Required) An identifier that refers to the collection name of the keyspace.

    For example, cities.`idx-name` indicates the idx-name index on the cities collection, assuming that the query context is set.

    Index Name ON Keyspace Reference

    (Introduced in Couchbase Server 7.0)

    keyspace-ref ::= keyspace-path | keyspace-partial
    keyspace-path | keyspace-partial

    In Couchbase Server 7.0 and later, you can use the index name with the ON keyword and a keyspace reference to specify the keyspace on which the index is built.

    If there is a hyphen (-) inside the index name or any part of the keyspace reference, you must wrap the index name or that part of the keyspace reference in backticks (` `). Refer to the examples below.

    Keyspace Reference: Keyspace Path

    keyspace-path ::= [ namespace ':' ] bucket [ '.' scope '.' collection ]
    ( namespace ':' )? bucket ( '.' scope '.' collection )?

    If the keyspace is a named collection, or the default collection in the default scope within a bucket, the keyspace reference may be a keyspace path. In this case, the query context should not be set.

    namespace

    (Optional) An identifier that refers to the namespace of the keyspace. Currently, only the default namespace is available. If the namespace name is omitted, the default namespace in the current session is used.

    bucket

    (Required) An identifier that refers to the bucket name of the keyspace.

    scope

    (Optional) An identifier that refers to the scope name of the keyspace. If omitted, the bucket’s default scope is used.

    collection

    (Optional) An identifier that refers to the collection name of the keyspace. If omitted, the default collection in the bucket’s default scope is used.

    For example, def_type ON default:`travel-sample` indicates the def_type index on the default collection in the default scope in the default:`travel-sample` bucket.

    Similarly, `idx-name` ON default:`travel-sample`.places.cities indicates the idx-name index on the cities collection in the places scope in the default:`travel-sample` bucket.

    Keyspace Reference: Keyspace Partial

    keyspace-partial ::= collection
    collection

    Alternatively, if the keyspace is a named collection, the keyspace reference may be just the collection name. In this case, you must set the query context to indicate the required namespace, bucket, and scope.

    collection

    (Required) An identifier that refers to the collection name of the keyspace.

    For example, `idx-name` ON cities indicates the idx-name index on the cities collection, assuming the query context is set.

    USING Clause

    index-using ::= USING GSI
    '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.

    Examples

    Example 1. Drop index from the default collection in the default scope

    This example creates a secondary index on the default collection in the default scope in the travel-sample bucket. Once the index creation statement comes back, system:indexes is queried for the status of the index.

    Create 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 the index
    DROP INDEX `travel-sample`.`idx-callsign` USING GSI;
    SELECT * FROM system:indexes WHERE name="idx-callsign";

    The following command would drop the index in exactly the same way, but uses alternative syntax.

    Drop the index — alternative syntax
    DROP INDEX `idx-callsign` ON `travel-sample` USING GSI;
    Example 2. Drop index from a named collection with query context

    This statement drops an index called idx-name from the countries collection within the places scope in the travel-sample bucket. It is assumed that the scope, collection, and index have already been created.

    This example uses the cbq shell, but you can also set the query context and drop the index using the Query Workbench.

    Set the query context
    cbq> \SET -query_context "travel-sample.places";
    Drop the index
    cbq> DROP INDEX countries.`idx-name`;

    The following command would drop the index in exactly the same way, but uses alternative syntax.

    Drop the index — alternative syntax
    cbq> DROP INDEX `idx-name` ON countries;
    Example 3. Drop index from a named collection with path

    This statement drops an index called idx-name from the countries collection within the places scope in the travel-sample bucket. It is assumed that the scope, collection, and index have already been created.

    Drop the index
    DROP INDEX `idx-name` ON `travel-sample`.places.countries;

    You must use this syntax if the index was created on a named collection, and you need to specify the full or relative path to the collection.