BUILD INDEX

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 kicks off a building of the 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 kicking off building of 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.

Important:

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).

build-index:

BUILD INDEX ON named_keyspace_ref(index_name[,index_name]*) USING GSI;

index_name:

A unique name that identifies an index. A name must be specified for each index to be built.

named-keyspace-ref

[ namespace-name :] keyspace-name 

keyspace-name:

keyspace-name is an identifier that refers to the bucket name. It specifies the bucket as the 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

The USING clause specifies the index type to use. The BUILD INDEX command can be used only with GSI indexes. Views do not support deferred building.

Example

The following example creates a set of primary and secondary indexes on the beer-sample bucket with the defer_build option. After the create index statement comes back, system:indexes is queried to for the status of the index.

CREATE INDEX `beer-sample-type-index` ON `beer-sample`(type) USING GSI 
   WITH {"defer_build":true};

SELECT * FROM system:indexes WHERE name="beer-sample-type-index";

With the defer_build option enabled, the output from system:indexes shows the beer-sample-type-index in the pending state ("state": "deferred"). BUILD INDEX can be used to kick off the deferred build on the index on type attribute.

BUILD INDEX ON `beer-sample`(`beer-sample-type-index`) USING GSI;

SELECT * FROM system:indexes WHERE name="beer-sample-type-index";