CREATE INDEX

6.0-SNAPSHOT 5.5 5.4 5.3 5.2 5.1 5.0

The CREATE INDEX statement creates a sorted, hash, or bitmap index for a given map.

Note

  • You cannot yet delete an index.

  • Although you can create bitmap indexes with SQL, SQL queries do not currently support bitmap index scans. As a result, bitmap indexes do not improve SQL query performance.

  • SQL queries cannot use more than one SORTED or HASH index for given scan, index combining is not supported.

  • You cannot yet create indexes on maps that have Tiered Storage enabled.

Syntax Summary

This code block is a quick reference with all the parameters that you can use with the CREATE MAPPING statement.

See some practical examples.

CREATE INDEX [ IF NOT EXISTS ] index_name
ON map_name ( attribute_name [, ...] )
[ TYPE index_type ]
[ OPTIONS ( 'option_name' = 'option_value' [, ...] ) ]
sql

Parameters

The CREATE INDEX statement accepts the following parameters.

You must provide the following:

  • index_name

  • map_name

  • attribute_name

Parameter Description

IF NOT EXISTS

Create a new index only if an index of the same name does not already exist (recommended).

index_name

Name of the index. This name must be unique. The index_name is scoped to the map.

map_name

Name of the map to index

attribute_name

Names of the map’s fields to index. The attribute_name may not be exactly the same as SQL maps it, because there’s no link to an SQL mapping. For example, if an attribute refers to an array, the index contains the array elements as individual index entries, all referring to the containing entry.

index_type

One of the following index types:

  • SORTED (default)

  • HASH

  • BITMAP

For details about these index types, see Indexing Maps.

OPTIONS

Configuration options for BITMAP indexes. Valid values for the option_name parameter include the following:

  • unique-key: The attribute to use as a unique key source for internal unique long ID assignment. Default: __key.

  • unique-key-transformation: The transformation to be applied to unique keys to generate unique long IDs from them. Default: OBJECT.

For details, see Bitmap Indexes.

Permissions

Enterprise Edition

If security is enabled, you can grant or deny permission for certain clients to use this statement. See SQL Permissions.

Examples

This section lists some example SQL queries that show you how to use the CREATE INDEX statement.

Sorted index
CREATE INDEX IF NOT EXISTS age
ON employees (age)
TYPE SORTED
sql
Sorted composite index
CREATE INDEX IF NOT EXISTS name_age
ON employees (name, age)
TYPE SORTED
sql
Hash index
CREATE INDEX IF NOT EXISTS name
ON employees (name)
TYPE HASH
sql
Bitmap index
CREATE INDEX IF NOT EXISTS firstName
ON employees ("name.first")
TYPE BITMAP
OPTIONS (
  'unique-key' = '__key'
)
sql
lifebuoy

Help and support