CREATE INDEX Statement
The create index statement generates a new index on the specified columns in the given table.
Syntax
create_index_statement ::=
CREATE INDEX [IF NOT EXISTS] index_name
ON table_name "(" path_list ")" [WITH NO NULLS][WITH UNIQUE KEYS PER ROW][comment
]
index_name ::= id
path_list ::= index_path ("," index_path)*
index_path ::=
name_path [path_type] |
multikey_path_prefix [.name_path] [path_type]
name_path ::= field_name ("." field_name)*
field_name ::= id
| DSTRING
multikey_path_prefix ::=
field_name ( ("." field_name) | ("[" "]") | ("." VALUES"("")" ) )*
( ("[" "]") | ("." VALUES"(""")" ) | ("." KEYS"(""")") )
path_type ::= AS
(INTEGER | LONG | DOUBLE | STRING |
BOOLEAN | NUMBER | ANYATOMIC |POINT | GEOMETRY)
Semantics
The index name is unique to a table. If an index with the same name already exists in
a table, then the statement will fail and report an error. For example, you can have
only one index named idx_income1
in the UserInfo
table.
The index name is specific to a table. You can use the same index name in multiple
tables. For example, you can use the same index name idx_income1
in
the UserInfo
and Users3
tables.
The index specification is unique to a table. If an index with the same specification already exists in a table, then the statement will fail and report an error.
idx_income1
index on
UserInfo
table,
CREATE INDEX idx_income1 ON UserInfo (info.income AS ANYATOMIC)
idx_income2
index is a duplicate of an existing index with
another name. In this case, even though the index names are different, the index
specifications are the same.
CREATE INDEX idx_income2 ON UserInfo(info.income AS ANYATOMIC)
If the optional IF NOT EXISTS clause is specified in the CREATE INDEX statement, and if an index with the same name exists, then the statement will not execute and will not report an error.
If the optional WITH NO NULLS clause is specified in the CREATE INDEX statement, then the rows with NULL and/or EMPTY values on the indexed fields will not be indexed.
The indexes that are created with the WITH NO NULLS clause may be useful when the data contain a lot of NULL and/or EMPTY values on the indexed fields. It will reduce the time and space overhead during indexing. However, the use of such indexes by queries is restricted. For more information, see Using Indexes for Query Optimization section.
CREATE INDEX idx_phones ON UserInfo(info.phones[].number AS INTEGER)
WITH UNIQUE KEYS PER ROW
If the optional COMMENT is specified, then this becomes part of the index metadata and is not interpreted. The "comment" will be displayed in the output of the DESCRIBE statement.
If JSON data is indexed, you must specify a data type using the AS keyword next to every index path into the JSON data. For all other typed data, you should not specify the data type, as the data type will be inferred from the table schema.
The index entries are automatically updated when rows are inserted, deleted, or updated in the specified table.
Note: The maximum number of index keys generated per row is 10000. An IllegalArgumentException
will be raised during indexing if the number of index keys generated per is row exceeds 10000.