Purpose
This statement is used to create an index. An index is a structure created on a table to sort the values of one or more columns in a database table. Its main purpose is to improve query speed and reduce the performance overhead of the database system.
The current version of OceanBase AI Database supports unique indexes and non-unique indexes. When creating an index, you can use STORING(column_name,...) to specify redundant columns on the index table. In addition to the columns explicitly specified by users, the index table automatically includes the primary key columns of the primary key table (or the Hidden PK if no primary key table exists). Storing additional columns in the index provides the database optimizer with more options.
Limitations and considerations
- If all index columns in a table are dropped, the created index becomes invalid.
- Full-text indexes are only supported on columns of the
CHAR,VARCHAR, andTEXTtypes. - Search indexes are supported on JSON, single-level arrays, and various scalar type columns. For a complete list, see [Data types supported by search indexes](#Data types supported by search indexes).
- Vector indexes must be created on
VECTORcolumns (for dense vector indexes),SPARSEVECTORcolumns (for sparse vector indexes), orVARCHARcolumns (for semantic indexes). For more information, see [Data types supported by vector indexes](#Data types supported by vector indexes). - The current version only supports creating local (
LOCAL) full-text or search indexes. - You cannot specify the
UNIQUEkeyword when creating a full-text index. - To create a full-text index involving multiple columns, ensure these columns have the same character set.
- You can specify the tokenizer only when creating a full-text index using
WITH PARSER {SPACE | NGRAM | BENG | IK | NGRAM2 | analyzer}.
Privilege requirements
To execute the CREATE INDEX statement to create an index, the current user must have at least the INDEX privilege on the corresponding object. For more information about OceanBase AI Database privileges, see Permission classification.
Syntax
General index syntax
CREATE [hint_options] [ASYNC] [index_type] INDEX [IF NOT EXISTS] index_name
[USING BTREE] ON table_name (sort_column_key [, sort_column_key... ])
[WITH PARSER tokenizer_option]
[PARSER_PROPERTIES[=](parser_properties_list)]
[FTS_INDEX_TYPE [=] {MATCH | PHRASE_MATCH}]
[index_option...] [partition_option] [index_column_group_option]
index_type:
SPATIAL
| UNIQUE
| FULLTEXT
| SEARCH -- For specific syntax, see the Search Index syntax below.
| VECTOR -- For specific syntax, see the Vector Index syntax below.
sort_column_key:
column_name [(integer)] [ASC] [ID id]
| (index_expr) [ASC] [ID id]
tokenizer_option:
SPACE
| NGRAM
| BENG
| IK
| NGRAM2
| analyzer
parser_properties_list:
parser_properties, [parser_properties]
parser_properties:
min_token_size = int_value
| max_token_size = int_value
| ngram_token_size = int_value
| ik_mode = 'char_value'
| min_ngram_size = int_value
| max_ngram_size = int_value
| analysis = json_string
index_option:
GLOBAL
| LOCAL
| BLOCK_SIZE integer
| COMMENT STRING_VALUE
| STORING (column_name [, column_name...])
| WITH_ROWID
| WITH PARSER STRING_VALUE
| index_using_algorithm
| visibility_option
| DATA_TABLE_ID data_table_id
| INDEX_TABLE_ID index_table_id
| VIRTUAL_COLUMN_ID virtual_column_id
| MAX_USED_PART_ID used_part_id
| STORAGE_CACHE_POLICY(storage_cache_policy_option)
index_column_group_option:
WITH COLUMN GROUP([all columns, ]each column)
storage_cache_policy_option:
GLOBAL = {"hot" | "auto" | "cold" | "none"}
| timeline_strategy_list
timeline_strategy_list:
BOUNDARY_COLUMN = column_name
| BOUNDARY_COLUMN_UNIT = {"s" | "ms"}
| HOT_RETENTION = intnum retention_time_unit
| MIXED_RETENTION = intnum retention_time_unit
| GRANULARITY = "partition"
retention_time_unit:
YEAR
| MONTH
| WEEK
| DAY
| HOUR
| MINUTE
Syntax of Search Index
When index_type is specified as SEARCH, you can use the following syntax to create a search index:
CREATE [ASYNC] SEARCH INDEX index_name ON table_name (
search_index_column
[, search_index_column ...]
) [LOCAL];
search_index_column:
column_name [WITH (search_col_option_list)]
search_col_option_list:
[INCLUDE_PATHS = ('path1'[, 'path2']...)]
[| EXCLUDE_PATHS = ('path1'[, 'path2']...)]
[| INCLUDE_TYPES = (type1[, type2]...)]
[| NESTED_PATHS = ('path1'[, 'path2']...)]
In addition to CREATE INDEX, you can also create a search index using the CREATE TABLE or ALTER TABLE statement.
In both methods, the semantics of search_index_column and WITH are consistent with those of CREATE SEARCH INDEX. For detailed syntax, see CREATE TABLE and ALTER TABLE.
Syntax of Vector Index
When index_type is specified as VECTOR, you can use the following syntax to create a vector index:
CREATE [/*+ parallel($value) */] VECTOR INDEX index_name
ON table_name (column_name)
WITH (vector_index_option [, vector_index_option ...]);
For general parameters of vector_index_option (such as distance, type, lib, m, and ef_construction), see vector index documentation such as HNSW series indexes.
When creating a semantic index on a VARCHAR column, you can also specify semantic index parameters using the WITH clause. For complete descriptions, limitations, and examples, see Semantic search.
Parameters
General index parameters
Parameter |
Description |
|---|---|
| hint_options | Specify hint options. This is optional. Currently, only the parallel hint is supported. For example: /*+ parallel(10) */. |
| ASYNC | Optional. When it is used together with FULLTEXT, it indicates creating an asynchronous full-text index (CREATE ASYNC FULLTEXT INDEX ...), and when it is used together with SEARCH, it indicates creating an asynchronous search index (CREATE ASYNC SEARCH INDEX ...). ASYNC It cannot be used alone, nor for normal or unique indexes. It is only supported on heap tables; the same index type on the same table does not support coexistence of synchronous and asynchronous indexes. Both asynchronous full-text indexes and asynchronous search indexes are supported starting from V4.6.2.1. For more information, see the "Asynchronous Full-Text Index" section in Asynchronous Full-Text Index and Search index in the "Asynchronous search index" section. |
| index_type | Optional. Specifies the index type. For more information about the types, see index_type. |
| IF NOT EXISTS | It is used to indicate that if the index already exists, it will not be created. When creating an index, if the index exists and this option is not specified, IF NOT EXISTS, an error will be reported. |
| index_name | Specifies the name of the index to be created. |
| USING BTREE | Optional. Specifies to use a B-tree index structure when creating the index.
NoteCurrently, only |
| table_name | Specify the table on which to create the index. You can directly specify the table name or use the schema_name.table_name format to specify the table name and the name of the database to which the table belongs. |
| sort_column_key | Specify the key for a sorting column. When creating an index, you can specify multiple sorting columns, which should be separated by commas (,) separately. For more information, see sort_column_key. |
| WITH PARSER tokenizer_option | Optional. Specifies the tokenizer for the full-text index. For more information, see tokenizer_option. |
| PARSER_PROPERTIES[=](parser_properties_list) | Optional. Specifies the attributes of the parser. For more information, see parser_properties. |
| FTS_INDEX_TYPE [=] {MATCH \ | PHRASE_MATCH} | Optional. Specifies the type of the full-text index. MATCH Indicates a term match index, which supports matching and scoring based on the BM25 algorithm. The stored content includes tokens, document IDs, word frequencies, document lengths, and other information. PHRASE_MATCH indicates the phrase match index, which can support phrase queries more efficiently. It is a functional superset of MATCH and also stores the position list of words. It takes effect only when used together with a FULLTEXT index. |
| index_option | Specify index options. When creating an index, you can specify multiple index options, which should be separated by English spaces. For details, see index_option. |
| partition_option | Specifies the options for creating an index partition. Supports hash partitioning, key partitioning, range partitioning, list partitioning, and external table partitioning. |
| index_column_group_option | Specify the index options. For more information, see index_column_group_option. |
| STORAGE_CACHE_POLICY | Optional. Specifies the index hotspot caching strategy during index creation in shared storage mode. For more information, see storage_cache_policy. |
| LOCAL | Optional. Specifies to create a local index. |
Search index parameters
Parameter |
Description |
Value range |
|---|---|---|
| search_index_column | You can search by specifying one or more index columns. Each column can be specified using the WITH (...) Specify the column-level path and type options. |
|
| INCLUDE_PATHS / EXCLUDE_PATHS | Column-level path allowlist or blocklist. Choose one of the two to control the JSON paths of the column involved in index construction. By default, the full path is used for index construction. Two path configurations are supported:
|
|
| INCLUDE_TYPES | Limit the set of data types that can participate in index construction (the type values are subject to the actual version support). For example INCLUDE_TYPES = (JSON_STRING) indicates that only a path index of type JSON_STRING is built. By default, all types are involved in index construction. |
JSON_STRING, JSON_NUMBER |
| NESTED_PATHS | Declares which paths in the JSON document are nested paths. Each path corresponds to an object array field, and all fields under that path will be forced to have indexes. Paths must start with $., use dot-separated member paths, and cannot contain column names; multiple paths can be declared. If an outer field is itself an array and there are arrays within it (for example, a comments array contains a replies array), they must be declared together, for example, ('$.comments', '$.comments.replies'). It can be used in combination with INCLUDE_PATHS or EXCLUDE_PATHS, as well as INCLUDE_TYPES; the priority of path coverage by NESTED_PATHS overrides that of EXCLUDE_PATHS. Only JSON columns are supported. For detailed path matching rules, see the "NESTED_PATHS Path Matching Description" section below. For more information, see Search index. |
For example, even if EXCLUDE_PATHS excludes $.comments.text, after declaring NESTED_PATHS = ('$.comments'), all fields under the comments path (including text) will still be forced to have an index:
CREATE SEARCH INDEX idx_nested ON movie_rating (
j1 WITH (
EXCLUDE_PATHS = ('$.comments.text'),
NESTED_PATHS = ('$.comments')
)
);
NESTED_PATHS path matching rules
When declaring NESTED_PATHS, the system compares the actual structure of each layer in the JSON with the specified paths.
The path notation is consistent with common JSON paths: $ denotes the starting point of the entire JSON, and member paths are written layer by layer using dots, for example, $.comments.replies. The last segment of the path is the name of the last member (in the example above, replies).
Basic rules:
- The value at the end of the path must be a JSON array (for example,
[{...}, {...}]) and cannot be a single object (for example,{...}). - OceanBase AI Database distinguishes between arrays and objects. A level that is not specified as an array path in the declaration will not be matched as an array. Taking
$.comments.repliesas an example:- When only this one is specified, the system splits the path into:
comments(the middle field) →replies(the last segment of the path). Only the last segmentrepliesmeets the array requirement (indicating that you need must be written as an array); the precedingcommentsfield is required for a single object. - Therefore,
{"comments":{"replies":[...]}}matches, but{"comments":[{"replies":[...]}]}does not — herecommentsis already an array, which conflicts with interpreting it as an object. - If the
commentsfield in the data is already a list of comments (an array), you must declare both$.commentsand$.comments.replies.
- When only this one is specified, the system splits the path into:
Quick reference for the three declarations:
Declaration |
Summary |
|---|---|
$.comments |
comments must be an array |
Only $.comments.replies |
comments must be an object, replies must be an array |
('$.comments', '$.comments.replies') |
Overwriteable comments for arrays, and the elements also contain replies Common formats such as arrays |
Data types supported by search indexes
Search indexes can be created on columns of the following data types:
Category |
Supported Types |
|---|---|
| Integer type | TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT, and their UNSIGNED variants |
| Floating-point and fixed-point types | FLOAT, DOUBLE, DECIMAL, NUMBER |
| String type | VARCHAR, CHAR |
| Text type | TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT |
| Binary type | BINARY, VARBINARY |
| Binary Large Object | TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB |
| Date and time types | DATE, TIME, DATETIME, TIMESTAMP, YEAR |
| JSON type | JSON |
| Array type | ARRAY(element_type) — Supports only single-layer arrays (for example, ARRAY(INT), ARRAY(VARCHAR(256))). |
Unsupported types: BIT, ENUM, and SET types do not support creating search indexes.
Data types supported by vector indexes
Vector indexes can be created on columns of the following data types:
Index type |
Supported column types |
Note |
|---|---|---|
| Dense Vector Index | VECTOR |
Create dense vector indexes such as HNSW and IVF on dense vector columns. |
| Sparse Vector Index | SPARSEVECTOR |
Creates a sparse vector index on a sparse vector column. |
| Semantic index | VARCHAR |
Create semantic indexes on text or image URL columns, and the system automatically performs embedding. |
index_type
SPATIAL: This keyword is used to specify that a spatial index is to be created.- The
UNIQUEkeyword is used to specify that a unique index is created. FULLTEXT: specifies to create a full-text index.SEARCH: This keyword is used to specify the creation of a search index.VECTOR: This keyword is used to specify the creation of a vector index, which includes dense vector indexes, sparse vector indexes, and semantic indexes.
For information about creating different types of indexes, see Create an index.
sort_column_key
column_name [(integer)] [ASC] [ID id]: specifies a column name as the sort key.column_name: the name of the column to be sorted.integer: Optional. Specifies the maximum length of the sort key.ASC: Optional. Specifies ascending order. DESC order is not supported.ID id: Optional. The ID of the key used for sorting.
For example, to create an index named
index3on the table namedt3and specify that the index sorts in ascending order on columnc1.CREATE INDEX index3 ON t3 (c1 ASC);(index_expr) [ASC] [ID id]: Specifies an index expression as the sort key. The index expression can be defined using expressions or functions. It can include the following options:(index_expr): Indicates an index expression, which can be a boolean expression, such asc1=c1. Creating a function-based index on a generated column is prohibited in the current version of OceanBase AI Database. For expressions supported by function-based indexes, see List of system functions supported for function-based indexes.ASC: Optional. Specifies ascending order. Descending order (DESC) is not supported.ID id: Optional. The ID of the key used for sorting.
For example, to create an index named
index4on the table namedt4, specifyc1+c2as the index expression, and sort in ascending order.CREATE INDEX index4 ON t4 ((c1 + c2) ASC);
You can specify multiple columns for sorting when creating an index. Separate multiple sort columns with commas (,). For example, to create an index named index5 on the table named t5 and specify using column c1 and the expression c2+c3 as the sort keys for the index.
CREATE INDEX index5 ON t5 (c1, (c2+c3));
tokenizer_option
SPACE: The default value, which indicates that the text is segmented by spaces. The following attributes can be specified:AttributeValue rangemin_token_size [1, 16] max_token_size [10, 84] NGRAM: Indicates the word segmentation method based on N-gram (Chinese). The following attributes can be specified:AttributeValue rangengram_token_size [1, 10] NGRAM2: Indicates that the text is split into consecutive characters within the range ofmin_ngram_sizetomax_ngram_size. The following attributes can be specified:AttributeValue rangemin_ngram_size [1, 16] max_ngram_size [1, 16] BENG: The word segmentation method based on Beng. You can specify the following attributes:AttributeValue rangemin_token_size [1, 16] max_token_size [10, 84] IK: Indicates the word segmentation method based on IK (Chinese). Currently, only theutf-8character set is supported. The following attributes can be specified:AttributeValue rangeik_mode smartmax_word
analyzer: specifies the text analyzer (Analyzer). This parameter is used through theanalysisparameter inPARSER_PROPERTIES.
You can use the TOKENIZE function to view the tokenization results of a text using the specified tokenizer and JSON-formatted parameters.
parser_properties
min_token_size: The minimum token length. Default value: 3. Value range: 1 to 16.max_token_size: the maximum token length. The default value is 84, and the valid range is 10 to 84.ngram_token_size: The token length forNGRAM. This parameter is valid only when the tokenizer isNGRAM. The default value is 2, and the valid range is 1 to 10.ik_mode: The tokenization mode of theIKtokenizer. Valid values:smart: The default value. It prioritizes identifying longer words, reducing the number of splits, and ensures each character belongs to only one word. It also attempts to merge numerals with measure words into a single word in the output.max_word: Outputs recognizable words as much as possible. Longer words and the shorter words within them are retained. The same character can appear in multiple words.
min_ngram_size: Valid only for theNGRAM2tokenizer. Specifies the minimum number of characters for a generated n-gram. Value range:[1, 16].max_ngram_size: Valid only for theNGRAM2tokenizer. Specifies the maximum number of characters for a generated n-gram. The value range is[1, 16]. The tokenizer generates consecutive character segments with lengths between the minimum and maximum values.analysis: Valid only whenWITH PARSER analyzeris specified. Specifies the JSON definition string for the analyzer. Built-in analyzers can be abbreviated as{"analyzer": "standard"}and so on. For a complete list of built-in types and their corresponding languages, see the description of theanalyzer_typeparameter in TOKENIZE.
Example:
Create table
tbl1.CREATE TABLE tbl1 (col1 VARCHAR(200), col2 TEXT);Create a full-text index on column
col2of tabletbl1, specify to use theIKtokenizer, and set tokenizer properties usingPARSER_PROPERTIES.CREATE FULLTEXT INDEX fidx_tbl1 ON tbl1(col2) WITH PARSER IK PARSER_PROPERTIES=(ik_mode='max_word');Create a full-text index on the
bodycolumn of thearticlestable using the built-instandardanalyzer.CREATE FULLTEXT INDEX ft_idx_body ON articles(body) WITH PARSER analyzer PARSER_PROPERTIES=(analysis='{"analyzer": "standard"}');
index_option
GLOBAL: Indicates that a global index is to be created.LOCAL: Creates a local index.BLOCK_SIZE integer: Specifies the size of an index block, that is, the number of bytes in each index block.COMMENT STRING_VALUE: Adds a comment for the index.STORING (column_name [, column_name...]): Specifies the columns to store in the index. Separate multiple columns with commas (,).WITH_ROWID: Creates an index that includes a row identifier.WITH PARSER STRING_VALUE: Specifies the parser required for parsing the index.index_using_algorithm: Specifies the algorithm used by the index. You can specify USING BTREE or USING HASH. However, you cannot use USING HASH to create a multi-valued index.visibility_option: Specifies the visibility of the index.DATA_TABLE_ID data_table_id: the ID of the data table to which the index belongs.INDEX_TABLE_ID index_table_id: the ID of the index table.VIRTUAL_COLUMN_ID virtual_column_id: Specifies the ID of the virtual column.MAX_USED_PART_ID used_part_id: The maximum partition ID of the specified index.
index_column_group_option
WITH COLUMN GROUP(all columns, each column): Specifies to create a redundant index for row-based and columnar storage.WITH COLUMN GROUP(each column): Specifies to create a columnstore index.
storage_cache_policy
Notice
The storage cache policy (STORAGE_CACHE_POLICY) can be specified only when creating an index in shared storage mode.
You can configure hot and cold storage policies for index tables:
For local indexes, you can specify the table-level
STORAGE_CACHE_POLICY. If the table-levelSTORAGE_CACHE_POLICYis not specified, the default isnone, which means the configuration of the data table'sSTORAGE_CACHE_POLICYis used. The hot and cold storage policies for local index partitions then follow the partitioning rules of the data table.For global indexes, in addition to specifying the table-level
STORAGE_CACHE_POLICY, if the index table has partitions, you can also specify the partition-levelSTORAGE_CACHE_POLICYfor the index table. If the table-levelSTORAGE_CACHE_POLICYis not specified for a global index, the default isnone.
Different attributes in storage_cache_policy_option are defined in Key-Value format. The semantics of each attribute are as follows:
GLOBAL = {"hot" | "auto" | "cold" | "none"}: Specifies the hot, auto, cold, or none storage strategy for index data. Valid values:hot: specifies the index as a hot storage. If sufficient cache disk space is available, all data under the index table will be cached to the local cloud disk.auto: Indicates that the system automatically identifies hot data in the specified index table.cold: Indicates that the data of the specified index table is cold data and will not be cached to the local cache disk. For data already persisted to the cache, cache eviction will be actively triggered. Subsequent accesses to the table data can be stored in the macroblock memory cache and will not be persisted to the macroblock cache.none: The default value. This means the index follows theSTORAGE_CACHE_POLICYvalue of the primary table.
timeline_strategy_list: The list of timeline strategy parameters, separated by commas. Timeline strategies use time to determine whether partition data is stored in hot or cold storage. The system automatically adjusts the data on local cache disks based on the configured strategy.BOUNDARY_COLUMN = column_name: specifies the column that identifies hot data. The column can be of an integer type (BIGINTorINT, in Unix timestamp format) or a time type (TIMESTAMP,DATE,DATETIME, orYEAR). IfBOUNDARY_COLUMNis of an integer type, the table must be partitioned by RANGE or RANGE COLUMNS. IfBOUNDARY_COLUMNis of a time type, the table must be partitioned by RANGE COLUMNS.BOUNDARY_COLUMN_UNIT = {"s" | "us" | "ms"}: specifies the time unit for theBOUNDARY_COLUMNparameter. This parameter is supported only whenBOUNDARY_COLUMNis an integer. Valid values:- When the partitioning column is of type
INT, the value ofBOUNDARY_COLUMN_UNITcan only bes. - When the partitioning column is of the
BIGINTtype, the values forBOUNDARY_COLUMN_UNITcan besorms.
- When the partitioning column is of type
HOT_RETENTION = intnum retention_time_unit: Specifies the time range for hot data. Valid values:MINUTE,HOUR,DAY,WEEK,MONTH, andYEAR.MIXED_RETENTION = intnum retention_time_unit:- If
MIXED_RETENTIONis set, data within the time range ofHOT_RETENTION + MIXED_RETENTIONis consideredcolddata. - If
MIXED_RETENTIONis not set, the retention type will remainautoafter reachingHOT_RETENTION. - If
HOT_RETENTIONis not set, the time range of [0, MIXED_RETENTION] representsautodata, and the data afterMIXED_RETENTIONrepresentscolddata.
- If
GRANULARITY = "partition": Optional. Specifies the granularity for hot and cold data storage, indicating that caching data is handled at the partition level.
Examples
Example 1
Use the following SQL statement to create a columnstore index for a table.
Use the following SQL statement to create the
test_tbl1table.CREATE TABLE test_tbl1 (col1 INT, col2 VARCHAR(50));Create a columnstore index named
idx1_test_tbl1on thetest_tbl1table, referencing thecol1column.CREATE INDEX idx1_test_tbl1 ON test_tbl1 (col1) WITH COLUMN GROUP(each column);
Example 2
In shared storage mode, use the following SQL statements to create a hot/cold storage strategy for an index table.
When creating the index, specify the data of the index table
idx1_tbl1as hot data.CREATE INDEX idx1_tbl1 ON tbl1 (c1) LOCAL STORAGE_CACHE_POLICY (GLOBAL = "hot");Create the
tbl2table and configure the data of the index tableidx2on it as hot data.CREATE TABLE tbl2 (col1 INT, col2 INT, INDEX idx2 ((col1 + 1)) STORAGE_CACHE_POLICY (GLOBAL = "hot"), UNIQUE KEY ((col1 + col2)));When creating a global index, configure its partition caching strategy and configure a timeline strategy.
CREATE INDEX idx_global ON tbl1 (c1) GLOBAL PARTITION BY RANGE (c1) (PARTITION p1 VALUES LESS THAN (100), PARTITION p2 VALUES LESS THAN (200), PARTITION p3 VALUES LESS THAN MAXVALUE);
Example 3
The following example shows how to create a search index.
Create the
tbltable.CREATE TABLE tbl (col1 JSON, col2 DECIMAL(10,2), col3 JSON, col4 JSON, col5 JSON, col6 varchar(512));Create a search index named
search_idx.CREATE SEARCH INDEX search_idx ON tbl( -- Replace path with the actual path. col1 WITH (INCLUDE_PATHS = ('$.path3', '$.path4')), col2 WITH (EXCLUDE_PATHS = ('$.path1', '$.path3')), col3 WITH (INCLUDE_TYPES = (JSON_STRING)), col4 );
Example 4
Create an asynchronous full-text index (the target table must be a heap table):
CREATE ASYNC FULLTEXT INDEX idx_content ON doc_async(content);
Create an asynchronous search index (the target table must be a heap table):
CREATE ASYNC SEARCH INDEX idx_search ON t(doc);