You are viewing the documentation for a prerelease version.

View Latest

BUILD INDEX

The BUILD INDEX statement enables you to build one or more GSI indexes that are marked for deferred building all at once.

By default, CREATE INDEX statement starts building the created index. However for more efficient building of multiple indexes, CREATE INDEX command can mark indexes for deferred building using the defer_build:true option. BUILD INDEX is capable of building multiple indexes all at once and can utilize a single scan to feed many index build operations.

BUILD INDEX is an asynchronous operation. BUILD INDEX creates a task to build the primary or secondary GSI indexes and returns as soon as the task is queued for execution. The full index creation operation happen in the background. Indexes of type GSI provide a status field and mark index status pending while the index build operation is in progress. This status field and other index metadata can be queried using system:indexes.

When building an index which has automatic index replicas, all of the replicas are also built as part of the BUILD INDEX statement, without having to manually specify them.

RBAC Privileges

User executing the BUILD INDEX statement must have the Query Manage Index privilege granted on the keyspace/bucket. For more details about user roles, see Authorization.

Known issue: If multiple index building operations are kicked off without waiting for all indexes to get to the online state, index building for some indexes might fail. In this case, system:indexes output might report the following error:

"errors": [{"code": 12014,
   "msg": "error: Build Already In Progress. Bucket BUCKET_NAME. Index INDEX_NAME. Index state: pending"}]

Index building might never complete if you get error 12014. To work around the issue, you can drop the indexes returning error 12014 and then recreate all of them either with a deferred build in one BUILD INDEX command (instead of multiple BUILD INDEX commands) or recreate them one at a time with a delay between the subsequent index creations to allow for index building to complete (that is index to get to the online state).

Syntax

build-index ::= BUILD INDEX ON keyspace-ref '(' index-term [ ',' index-term ]* ')' [ index-using ]
'BUILD' 'INDEX' 'ON' keyspace-ref '(' index-term (',' index-term)* ')' index-using?

Keyspace Reference

keyspace-ref ::= [ namespace ':' ] keyspace
(namespace ':')? keyspace
namespace

(Optional) An identifier that refers to the namespace of the bucket to be indexed. 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 which the index needs to be created.

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

Index Term

index-term ::= index-name | index-expr | subquery-expr
index-name | index-expr | subquery-expr

You can specify one index term, or multiple index terms separated by commas. An index term must be specified for each index to be built.

In Couchbase Server 6.5 and later, each index term may be an index name, an expression, or a subquery. The BUILD INDEX clause may contain a mixture of the different types of index term.

Index Name

An identifier that refers to the name of an index.

BUILD index on default(ix1, ix2, ix3);

Index Expression

An expression that may be a string, or an array of strings, each referring to the name of an index.

BUILD index on default('ix1', 'ix2', 'ix3');
BUILD index on default(['ix1', 'ix2', 'ix3']);
BUILD index on default('ix1', ['ix2', 'ix3'], ['ix4']);

Arrays of identifiers are not permitted.

BUILD index on default([ix1, ix2, ix3]);
BUILD index on default([ix1], [ix2, ix3]);

Subquery Expression

subquery-expr ::= '(' select ')'
'(' select ')'

Use parentheses to specify a subquery.

The subquery must return an array of strings, each string representing the name of an index. See Example 4 for details.

For more details and examples, see SELECT Clause and Subqueries.

USING Clause

index-using ::= USING ( VIEW | GSI )
'USING' ('VIEW' | 'GSI')

Specifies which index form to use.

USING VIEW

Cannot be used. View indexes do not support deferred building.

USING GSI

Builds the index using the Global Secondary Index form.

This clause is optional; if omitted, the default is USING GSI.

Examples

Example 1. Create a deferred index

Create a set of primary and secondary indexes on the beer-sample bucket with the defer_build option.

CREATE INDEX `beer-sample-type-index` ON `beer-sample`(type) USING GSI
   WITH {"defer_build":true};
Example 2. Check deferred index status

Query system:indexes for the status of the index.

SELECT * FROM system:indexes WHERE name="beer-sample-type-index";
Results
[
  {
    "indexes": {
      "datastore_id": "http://127.0.0.1:8091",
      "id": "91e28fbb76aa93f6",
      "index_key": [
        "`type`"
      ],
      "keyspace_id": "beer-sample",
      "name": "beer-sample-type-index",
      "namespace_id": "default",
      "state": "deferred", (1)
      "using": "gsi"
    }
  }
]
1 The beer-sample-type-index is in the pending state (deferred).
Example 3. Build a named index

Kick off the deferred build using the index name.

BUILD INDEX ON `beer-sample`(`beer-sample-type-index`) USING GSI;
Example 4. Build all indexes

Alternatively, kick off all deferred builds in the keyspace, using a subquery to find the deferred builds.

BUILD INDEX ON `beer-sample` (( (1)
  SELECT RAW name (2)
  FROM system:indexes
  WHERE keyspace_id = 'beer-sample'
    AND state = 'deferred' ));
1 One set of parentheses delimits the whole group of index terms, and another set of parentheses delimits the subquery. In this case there is a double set of parentheses, as the subquery is the only index term.
2 The RAW keyword forces the subquery to return a flattened array of strings, each of which refers to an index name.
Example 5. Check online index status

Query system:indexes for the status of the index.

SELECT * FROM system:indexes WHERE name="beer-sample-type-index";
Results
[
  {
    "indexes": {
      "datastore_id": "http://127.0.0.1:8091",
      "id": "91e28fbb76aa93f6",
      "index_key": [
        "`type`"
      ],
      "keyspace_id": "beer-sample",
      "name": "beer-sample-type-index",
      "namespace_id": "default",
      "state": "online", (1)
      "using": "gsi"
    }
  }
]
1 The index has now been created.