Please use the form below to provide your feedback. Because your feedback is valuable to us, the information you submit in this form is recorded in our issue tracking system (JIRA), which is publicly available. You can track the status of your feedback using the ticket number displayed in the dialog once you submit the form.

Defer Indexes

  • Capella Operational
  • how-to
    +
    How to create deferred indexes and build them later.

    Introduction

    When you create a primary or secondary index, you can mark it as deferred. This means the index is not built at once; you can build the deferred index later. This enables you to build multiple indexes more efficiently.

    If you want to try out the examples in this section, follow the instructions given in Create an Account and Deploy Your Free Tier Operational Cluster to create a free account, deploy a cluster, and load a sample dataset. Read the following for further information about the tools available for editing and executing queries:

    Deferring an Index

    You can defer an index to be built later using a SQL++ statement or an SDK call.

    The SDK calls only enable you to create indexes in the default collection and default scope within a bucket. A SQL++ statement enables you to create indexes in any collection and scope within a bucket.

    • SQL++

    • .NET

    • Java

    • Node.js

    • Python

    To defer a primary or secondary index to be built later:

    1. Use the WITH clause to specify the index options.

    2. In the index options, set the defer_build attribute to true.

    Context

    For this example, set the query context to the inventory scope in the travel sample dataset. For more information, see Setting the Query Context.

    Queries

    The following queries create a set of primary and secondary indexes in the landmark keyspace, with build deferred until later.

    CREATE INDEX idx_landmark_country
  ON landmark(country)
  USING GSI
  WITH {"defer_build":true};
    CREATE INDEX idx_landmark_name 
  ON landmark(name)
  USING GSI
  WITH {"defer_build":true};
    CREATE PRIMARY INDEX idx_landmark_primary
  ON landmark
  USING GSI
  WITH {"defer_build":true};

    For further details and examples, refer to CREATE PRIMARY INDEX and CREATE INDEX.

    To defer a primary or secondary index to be built later:

    1. Use CreatePrimaryQueryIndexOptions or CreateQueryIndexOptions to specify the index options.

    2. In the index options, invoke the Deferred method with the argument true.

    The following examples create a set of primary and secondary indexes in the specified keyspace, with build deferred until later.

    await cluster.QueryIndexes.CreatePrimaryIndexAsync(
	"`travel-sample`",
	 options => options.Deferred(true)
);
    await cluster.QueryIndexes.CreateIndexAsync(
	"`travel-sample`",
	"idx_name_email",
	new[] { "name", "email" },
	options => options.Deferred(true)
);

    Click the View button to see this code in context.

    For further details, refer to CreatePrimaryQueryIndexOptions and CreateQueryIndexOptions.

    To defer a primary or secondary index to be built later:

    1. Use CreatePrimaryQueryIndexOptions or CreateQueryIndexOptions to specify the index options.

    2. In the index options, invoke the deferred method with the argument true.

    The following examples create a set of primary and secondary indexes in the specified keyspace, with build deferred until later.

    CreatePrimaryQueryIndexOptions primaryOpts = CreatePrimaryQueryIndexOptions
    .createPrimaryQueryIndexOptions()
    .deferred(true);

cluster.queryIndexes().createPrimaryIndex("travel-sample", primaryOpts);
    CreateQueryIndexOptions secondaryOpts = CreateQueryIndexOptions
    .createQueryIndexOptions()
    .deferred(true);

cluster.queryIndexes().createIndex(
  "travel-sample", 
  "idx_name_email",	
  Arrays.asList("name", "email"), 
  secondaryOpts
);

    Click the View button to see this code in context.

    For further details, refer to CreatePrimaryQueryIndexOptions and CreateQueryIndexOptions.

    To defer a primary or secondary index to be built later:

    1. Use CreatePrimaryQueryIndexOptions or CreateQueryIndexOptions to specify the index options.

    2. In the index options, set the deferred property to true.

    The following examples create a set of primary and secondary indexes in the specified keyspace, with build deferred until later.

    await cluster
  .queryIndexes()
  .createPrimaryIndex('travel-sample', { name: '#primary', deferred: true })
    await cluster
  .queryIndexes()
  .createIndex('travel-sample', 'idx_name_email', ['name', 'email'], {
    deferred: true,
  })

    Click the View button to see this code in context.

    For further details, refer to CreatePrimaryQueryIndexOptions and CreateQueryIndexOptions.

    To defer a primary or secondary index to be built later:

    1. Use CreatePrimaryQueryIndexOptions or CreateQueryIndexOptions to specify the index options.

    2. In the index options, set the deferred property to True.

    The following examples create a set of primary and secondary indexes in the specified keyspace, with build deferred until later.

    cluster.query_indexes().create_primary_index(
    "travel-sample",
    CreatePrimaryQueryIndexOptions(deferred=True)
)
    cluster.query_indexes().create_index(
    "travel-sample",
    "idx_name_email",
    ["name", "email"],
    CreateQueryIndexOptions(deferred=True)
)

    Click the View button to see this code in context.

    For further details, refer to SQL++ Index Management.

    Building a Deferred Index

    You can build one or more deferred primary or secondary indexes using a SQL++ statement. You can also build all deferred indexes in a keyspace using an SDK call.

    The SDK calls only enable you to build indexes in the default collection and default scope within a bucket. A SQL++ statement enables you to build indexes in any collection and scope within a bucket.

    • SQL++

    • .NET

    • Java

    • Node.js

    • Python

    To build one or more deferred indexes, use the BUILD INDEX statement:

    1. Use the ON keyword to specify the keyspace which contains the index or indexes.

    2. Specify the index or indexes that you want to build in parentheses ().

    Context

    For this example, set the query context to the inventory scope in the travel sample dataset. For more information, see Setting the Query Context.

    Queries

    The following query builds a single deferred index.

    BUILD INDEX ON landmark(idx_landmark_country) USING GSI;

    The following query builds multiple deferred indexes.

    BUILD INDEX ON hotel(idx_landmark_name, idx_landmark_primary);

    For further details and examples, refer to BUILD INDEX.

    To build all deferred indexes in a keyspace, use the task BuildDeferredIndexesAsync on the interface IQueryIndexManager.

    The following example builds all deferred indexes in the specified keyspace.

    // Start building any deferred indexes which were previously created.
await cluster.QueryIndexes.BuildDeferredIndexesAsync("`travel-sample`");

// Wait for the deferred indexes to be ready for use.
await cluster.QueryIndexes.WatchIndexesAsync(
	"`travel-sample`",
	new[] { "idx_name_email" },
	options => options.WatchPrimary(true)
);

    Click the View button to see this code in context.

    For further details, refer to IQueryIndexManager().

    To build all deferred indexes in a keyspace, use the buildDeferredIndexes method.

    The following example builds all deferred indexes in the specified keyspace.

    // Start building any deferred indexes which were previously created.
cluster.queryIndexes().buildDeferredIndexes("travel-sample");

WatchQueryIndexesOptions opts = WatchQueryIndexesOptions
    .watchQueryIndexesOptions()
    .watchPrimary(true);

// Wait for the deferred indexes to be ready for use.
// Set the maximum time to wait to 3 minutes.
cluster.queryIndexes().watchIndexes(
  "travel-sample", 
  Arrays.asList("idx_name_email"), 
  Duration.ofMinutes(3), 
  opts
);

    Click the View button to see this code in context.

    For further details, refer to QueryIndexManager.

    To build all deferred indexes in a keyspace, use the buildDeferredIndexes function on a QueryIndexManager object.

    The following example builds all deferred indexes in the specified keyspace.

    // Start building any deferred indexes which were previously created.
await cluster.queryIndexes().buildDeferredIndexes('travel-sample')

// Wait for the deferred indexes to be ready for use.
// Set the maximum time to wait to 3 minutes.
await cluster.queryIndexes().watchIndexes(
  'travel-sample',
  ['idx_name_email'],
  180000, // milliseconds
  { watchPrimary: true }
)

    Click the View button to see this code in context.

    For further details, refer to QueryIndexManager.

    To build all deferred indexes in a keyspace, use the build_deferred_indexes function on a QueryIndexManager object.

    The following example builds all deferred indexes in the specified keyspace.

    # Start building any deferred indexes which were previously created.
cluster.query_indexes().build_deferred_indexes("travel-sample")

# Wait for the deferred indexes to be ready for use.
# Set the maximum time to wait to 3 minutes.
cluster.query_indexes().watch_indexes(
    "travel-sample",
    ["idx_name_email"],
    WatchQueryIndexOptions(timeout=timedelta(minutes=3), watch_primary=True)
)

    Click the View button to see this code in context.

    For further details, refer to SQL++ Index Management.

    Reference and explanation:

    Administrator guides:

    Indexes with SDKs: