Create an index

This topic describes how to create an index.

Background information

OceanBase Database allows you to create indexes on non-partitioned and partitioned tables. An index can be a local or global index, a unique or non-unique index, a spatial index, or a function-based index. If a unique index is created on a partitioned table, the local unique index must contain the partitioning key of the table partition. For more information about indexes, see Index overview.

This topic describes how to create an index on a non-partitioned table. For information about how to create an index on a partitioned table, see Create an index on a partitioned table.

Considerations

When you create an index in OceanBase Database, the index name must be unique in the database. When you create an index in OceanBase Database, the index name must be unique in the database.

Create a regular index

You can use the CREATE TABLE, CREATE INDEX, or ALTER TABLE statement to create a regular index.

Use the CREATE TABLE statement to create a regular index

You can use the CREATE TABLE statement to create an index when you create a table.

Syntax: Syntax:

CREATE TABLE table_name(column_name column_definition,[column_name column_definition,...] [UNIQUE] INDEX|KEY [index_name](column_name));

The following table describes the parameters in the syntax.

• table_name: specifies the name of the table to be created.

• table_name: specifies the name of the table to be created.

• column_name: specifies the column of the table.

• column_definition: specifies the data type of each column in the table.

• UNIQUE: optional. This parameter specifies a unique index. You must specify this parameter when you create a unique index.

• INDEX|KEY: specifies the index keyword. You can use INDEX or KEY.

• INDEX|KEY: specifies the index keyword. You can use INDEX or KEY.

• index_name: optional. Specifies the name of the index to be created. If you do not specify this parameter, the index name is the same as the column name.

• index_name: optional. Specifies the name of the index to be created. If you do not specify this parameter, the index name is the same as the column name.

The following example creates a table named tbl1 and an index named tbl1_idx1: The following example creates a table named tbl1 and an index named tbl1_idx1:

CREATE TABLE tbl1(id INT,name VARCHAR(18),date DATE,PRIMARY KEY (id),INDEX tbl1_idx1 (date));

Use the CREATE INDEX statement to create a regular index

You can use the CREATE INDEX statement to create an index for an existing table.

Syntax: Syntax:

CREATE [UNIQUE] INDEX index_name ON table_name (column_name);

The following table describes the parameters in the syntax.

• UNIQUE: optional. This parameter specifies a unique index. You must specify this parameter when you create a unique index.

• index_name: specifies the name of the index to be created.

• index_name: specifies the name of the index to be created.

• table_name: specifies the name of the table to which the index is to be added.

• column_name: specifies the column to be indexed. If multiple columns are specified, separate them with commas.

The following example creates an index named tbl2_idx1 for a table named tbl2:

1. Create a table named tbl2.

   CREATE TABLE tbl2(id INT,name VARCHAR(20));
   

2. Create an index named tbl2_idx1 for the tbl2 table.

3. Create an index named tbl2_idx1 for the tbl2 table.

   CREATE INDEX tbl2_idx1 ON tbl2(id);
   

Use the ALTER TABLE statement to create a regular index

You can use the ALTER TABLE statement to create an index for an existing table. This statement allows you to create multiple indexes at a time.

Syntax: Syntax:

ALTER TABLE table_name ADD [UNIQUE] INDEX|KEY [index_name](column_name);

The following table describes the parameters in the syntax.

• table_name: specifies the name of the table to which the index is to be added.

• UNIQUE: optional. This parameter specifies a unique index. You must specify this parameter when you create a unique index.

• INDEX|KEY: specifies the index keyword. You can use INDEX or KEY.

• INDEX|KEY: specifies the index keyword. You can use INDEX or KEY.

• index_name: optional. Specifies the name of the index to be created. If you do not specify this parameter, the index name is the same as the column name.

• index_name: optional. Specifies the name of the index to be created. If you do not specify this parameter, the index name is the same as the column name.

• column_name: specifies the column to be indexed. If multiple columns are specified, separate them with commas.

The following example creates an index named tbl3_idx1 for a table named tbl3:

1. Create a table named tbl3.

   CREATE TABLE tbl3(id INT,name VARCHAR(20));
   

2. Create an index named tbl3_idx1 for the tbl3 table.

3. Create an index named tbl3_idx1 for the tbl3 table.

   ALTER TABLE tbl3 ADD INDEX tbl3_idx1(id);
   

Create a spatial index

OceanBase Database in MySQL-compatible mode allows you to create spatial indexes. A spatial index describes the location information of data stored on a medium and can improve the system's efficiency in retrieving data.

The syntax for creating a spatial index is similar to that for creating a regular index. You can use the CREATE TABLE, CREATE INDEX, or ALTER TABLE statement to create an index. The difference is that you must use the SPATIAL keyword when creating a spatial index, and the column in the spatial index must be NOT NULL.

Limitations on spatial indexes

Limitations on spatial indexes

Before creating spatial indexes, you need to be aware of the following limitations:

• Spatial indexes support only local indexes, not global indexes.

• The column to which you want to add a spatial index must be defined with the SRID attribute. If a column does not have the SRID attribute, a spatial index created on the column cannot be used in subsequent queries. This is because, after a spatial index is created, when spatial comparisons are performed using the index, the spatial coordinate system defined by the SRID attribute in the column definition must be used. Spatial indexes are built based on the MBRs (minimum bounding rectangles) of the spatial objects, and comparisons between MBRs also rely on the SRID attribute.

  For more information about the SRID, see Spatial reference systems (SRS).

• You can create spatial indexes on columns that store spatial data only. For information about spatial data types supported by OceanBase Database, see Overview of spatial data types.

• The column to which you want to add a spatial index must have the NOT NULL constraint.

  If the column does not have the NOT NULL constraint, you can use the ALTER TABLE statement to change the constraint to NOT NULL, and then create a spatial index. For information about how to define the constraint type for a column, see Define the constraint type for a column.

Create a spatial index by using the CREATE TABLE statement

Create a spatial index by using the CREATE TABLE statement

The syntax is as follows:

CREATE TABLE table_name(column_g_name GEOMETRY NOT NULL SRID 4326,[column_name column_definition,...] SPATIAL INDEX|KEY [index_name](column_g_name));

The parameter descriptions are as follows:

• table_name: specifies the name of the table.

• column_g_name, column_name: specifies the column names of the table.

• SRID 4326: specifies the SRID value of the spatial column.

• column_definition: specifies the data types of the columns in the table. The data type of the column on which you want to add a spatial index must be a spatial data type. For information about spatial data types supported by OceanBase Database, see Overview of spatial data types.

• INDEX|KEY: specifies that either INDEX or KEY can be used as the index keyword.

• index_name: specifies the index name of the index to be added. If you do not specify a name, the index name is the same as the column name.

The following example shows how to create a table named tbl1_g and create a spatial index named tbl1_g_idx1 on this table:

CREATE TABLE tbl1_g (id INT,name VARCHAR(18),g GEOMETRY NOT NULL SRID 4326,SPATIAL INDEX tbl1_g_idx1(g));

After the index is created, you can execute the SHOW INDEX statement to query the index information.

SHOW INDEX FROM tbl1_g;

The query result is as follows:

+--------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
| Table  | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment   | Index_comment | Visible |
+--------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
| tbl1_g |          1 | tbl1_g_idx1 |            1 | g           | A         |        NULL | NULL     | NULL   |      | SPATIAL    | available |               | YES     |
+--------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
1 row in set

Create a spatial index by using the CREATE INDEX statement

Create a spatial index by using the CREATE INDEX statement

The syntax is as follows:

CREATE SPATIAL INDEX index_name ON table_name(column_g_name);

The parameter descriptions are as follows:

• table_name: specifies the name of the table.

• index_name: specifies the index name of the index to be added.

• column_g_name: specifies the columns on which indexes are to be added. Separate multiple columns with commas. The data type of the column on which you want to add a spatial index must be a spatial data type. For information about spatial data types supported by OceanBase Database, see Overview of spatial data types.

The following example shows how to create a spatial index named tbl2_g_idx1 after the table tbl2_g is created:

1. Create the table tbl2_g.

   CREATE TABLE tbl2_g(id INT,name VARCHAR(20),g GEOMETRY NOT NULL SRID 4326);
   

2. Add the tbl2_g_idx1 spatial index to the tbl2_g table.

   CREATE SPATIAL INDEX tbl2_g_idx1 ON tbl2_g(g);
   

3. Query the index information.

   SHOW INDEX FROM tbl2_g;
   

   The query result is as follows:

   +--------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
   | Table  | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment   | Index_comment | Visible |
   +--------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
   | tbl2_g |          1 | tbl2_g_idx1 |            1 | g           | A         |        NULL | NULL     | NULL   |      | SPATIAL    | available |               | YES     |
   +--------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
   1 row in set
   

Create a spatial index by using the ALTER TABLE statement

ALTER TABLE table_name ADD SPATIAL INDEX|KEY [index_name](column_g_name);

Here are the explanations of the parameters:

• table_name: specifies the name of the table on which to create the index.

• INDEX|KEY: specifies the INDEX or KEY keyword to be used in the statement.

• index_name: an optional parameter that specifies the name of the index to be created. If you do not specify a value for this parameter, the name of the index is the same as the name of the column.

• column_g_name: specifies the columns to be indexed. Separate column names with commas if multiple columns are specified. The data type of the column on which to create a spatial index must be a spatial data type. For more information about spatial data types supported by OceanBase Database, see Overview of spatial data types.

Here is an example of how to create a spatial index tbl3_g_idx1 on the tbl3_g table:

1. Create the tbl3_g table.

   CREATE TABLE tbl3_g(id INT,name VARCHAR(20),g GEOMETRY NOT NULL SRID 4326);
   

2. Create the spatial index on the table.

   ALTER TABLE tbl3_g ADD SPATIAL INDEX tbl3_g_idx1(g);
   

3. View index details.

   SHOW INDEX FROM tbl3_g;
   

   The query result is as follows:

   +--------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
   | Table  | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment   | Index_comment | Visible |
   +--------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
   | tbl3_g |          1 | tbl3_g_idx1 |            1 | g           | A         |        NULL | NULL     | NULL   |      | SPATIAL    | available |               | YES     |
   +--------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
   1 row in set
   

Create a function index

Create a function index

A function index is an index created based on the values of one or more columns in a table. A function index is an optimization technique. Using a function index allows you to quickly locate matching function values during queries, thus avoiding repeated calculations and improving query efficiency.

Assume that you have a sale table with a date_col column of the date data type: Assume that you have a sale table with a date_col column of the date data type:

CREATE TABLE sale(date_col date, sales_col int, name_col varchar(10));

If you want to query data for March, you can use the Month() function:

SELECT * FROM sale WHERE month(date_col) = 3;

In this case, the database needs to calculate month(date_col) for each row in the table and filter out rows where month(date_col) != 3. If you frequently use the month information to filter data, you need to recalculate month(date_col) each time, which will incur a lot of overhead. Therefore, you can create a function index on the date_col column to store the month(date_col) values in an index table, thereby accelerating queries.

CREATE INDEX idx1 ON sale((month(date_col)));

You can use the CREATE TABLE, CREATE INDEX, or ALTER TABLE statement to create a function index. A function index can be a unique index or a spatial index.

Limitations on function indexes

Limitations on function indexes

In the MySQL-compatible mode of OceanBase Database, some system functions are not supported as expressions for function indexes. For more information, see System functions supported as expressions for function indexes and System functions not supported as expressions for function indexes.

Create a function index by using the CREATE TABLE statement

You can create a function index when you create a table. Syntax:

CREATE TABLE table_name({column_name column_definition,[column_name column_definition,...]} [UNIQUE| SPATIAL] {INDEX|KEY} [index_name](expr));

The following table describes the parameters in the syntax.

• table_name: the name of the table on which you want to create an index.

• column_definition: the data types of the columns in the table.

• [UNIQUE | SPATIAL]: optional. UNIQUE specifies a unique index. SPATIAL specifies a spatial index. You must specify the corresponding keyword when you create a unique index or a spatial index.

• INDEX|KEY: specifies the index keyword as INDEX or KEY.

• index_name: the name of the function index to be created. If you do not specify a name, the system automatically generates one. The format is functional_index_xx, where xx is the index number.

• expr: a valid function index expression, which can be a Boolean expression, for example, c1=c1.

Here is an example of creating a function index named tbl1_func_idx1: Here is an example of creating a function index named tbl1_func_idx1:

CREATE TABLE tbl1_func (c1 int, c2 int, index tbl1_func_idx1 ((c1+1)), UNIQUE KEY ((c1+c2)));

After the function index is created, you can run the SHOW CREATE TABLE statement to view the statement for creating the table. The statement is as follows:

SHOW CREATE TABLE tbl1_func;

The query result is as follows:

+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table     | Create Table                                                                                                                                                                                                                                                                                                                                                                                        |
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tbl1_func | CREATE TABLE `tbl1_func` (
  `c1` int(11) DEFAULT NULL,
  `c2` int(11) DEFAULT NULL,
  UNIQUE KEY `functional_index` ((`c1` + `c2`)) BLOCK_SIZE 16384 LOCAL,
  KEY `tbl1_func_idx1` ((`c1` + 1)) BLOCK_SIZE 16384 LOCAL
) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 |
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set

Create a function index by using the CREATE INDEX statement

After a table is created, you can create a function index on the table. Syntax:

CREATE [UNIQUE | SPATIAL] INDEX index_name ON table_name (expr);

The following table describes the parameters in the syntax.

• [UNIQUE | SPATIAL]: optional. UNIQUE specifies a unique index. SPATIAL specifies a spatial index. You must specify the corresponding keyword when you create a unique index or a spatial index.

• index_name: the name of the function index to be created.

• index_name: the name of the function index to be created.

• table_name: the name of the table on which you want to create an index.

• expr: a valid function index expression, which can be a Boolean expression, for example, c1=c1.

Here is an example of creating a function index named tbl2_func_idx1 with the expression c1 + c2 < 1 on the tbl2_func table:

1. Create the tbl2_func table.

   CREATE TABLE tbl2_func(c1 int, c2 int);
   

2. Create the tbl2_func_idx1 function index.

   CREATE INDEX tbl2_func_idx1 on tbl2_func ((c1+c2 < 1));
   

3. View the created function index.

   SHOW INDEX FROM tbl2_func;
   

   The result is as follows: The result is as follows:

   +-----------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+---------------------+
   | Table     | Non_unique | Key_name       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment   | Index_comment | Visible | Expression          |
   +-----------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+---------------------+
   | tbl2_func |          1 | tbl2_func_idx1 |            1 | SYS_NC18$   | A         |        NULL | NULL     | NULL   | YES  | BTREE      | available |               | YES     | ((`c1` + `c2`) < 1) |
   +-----------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+---------------------+
   1 row in set
   

Create a function index by using the ALTER TABLE statement

Syntax:

ALTER TABLE table_name ADD [UNIQUE | SPATIAL] {INDEX | KEY} [index_name](expr);

The following table describes the parameters in the syntax.

• table_name: the name of the table on which you want to create an index.

• [UNIQUE | SPATIAL]: optional. UNIQUE specifies a unique index. SPATIAL specifies a spatial index. You must specify the corresponding keyword when you create a unique index or a spatial index.

• INDEX|KEY: specifies the index keyword as INDEX or KEY.

• index_name: the name of the function index to be created. If you do not specify a name, the system automatically generates one. The format is functional_index followed by a number.

• expr: a valid function index expression, which can be a Boolean expression, for example, c1=c1.

1. Create the tbl3_func table.

   CREATE TABLE tbl3_func(c1 int, c2 int);
   

2. Create two function indexes on the table.

   ALTER TABLE tbl3_func ADD INDEX ((c1+1));
   

   ALTER TABLE tbl3_func ADD INDEX ((concat(c1,'a')));
   

3. View the index information.

   SHOW INDEX FROM tbl3_func;
   

   The query result is as follows:

   +-----------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------------+
   | Table     | Non_unique | Key_name           | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment   | Index_comment | Visible | Expression       |
   +-----------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------------+
   | tbl3_func |          1 | functional_index   |            1 | SYS_NC18$   | A         |        NULL | NULL     | NULL   | YES  | BTREE      | available |               | YES     | (`c1` + 1)       |
   | tbl3_func |          1 | functional_index_2 |            1 | SYS_NC19$   | A         |        NULL | NULL     | NULL   | YES  | BTREE      | available |               | YES     | concat(`c1`,'a') |
   +-----------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------------+
   2 rows in set
   

Create a columnstore index

You can use the CREATE TABLE, CREATE INDEX, or ALTER TABLE statement to create a columnstore index.

Create a columnstore index by using the CREATE TABLE statement

Create a columnstore index by using the CREATE TABLE statement

You can use the CREATE TABLE statement to create a columnstore index when you create a table.

Syntax: Syntax:

CREATE TABLE table_name(column_name column_definition,[column_name column_definition,...] [UNIQUE] INDEX|KEY [index_name](column_name) [STORING(column_name, ...)] WITH COLUMN GROUP([all columns, ]each column));

The following table describes the parameters in the syntax. The following table describes the parameters in the syntax.

• table_name: specifies the name of the table to be created.

• table_name: specifies the name of the table to be created.

• column_name: specifies the columns of the table.

• column_definition: specifies the data types of the columns in the table.

• UNIQUE: optional. Specifies a unique index. You must specify this keyword when you create a unique index.

• UNIQUE: optional. Specifies a unique index. You must specify this keyword when you create a unique index.

• INDEX|KEY: specifies the index keyword. You can use INDEX or KEY in this statement.

• INDEX|KEY: specifies the index keyword. You can use INDEX or KEY in this statement.

• index_name: optional. Specifies the name of the index to be created. If you do not specify this parameter, the index name is the same as the column name.

• STORING(column_name, ...) : optional. Specifies the redundant columns in the index table. If the column_name column is not an indexed column, it is redundantly stored in the columnstore format.

• WITH COLUMN GROUP([all columns, ]each column): specifies the columnstore attributes of the index.

  • WITH COLUMN GROUP(all columns, each column): specifies to create a rowstore and columnstore redundant index.
  • WITH COLUMN GROUP(all columns): specifies to create a rowstore index.
  • WITH COLUMN GROUP(each column): specifies to create a columnstore index.

Here are some examples:

• Create a table named tbl4 and a rowstore and columnstore redundant index named idx1_tbl4_cg.

  CREATE TABLE tbl4(id INT, name VARCHAR(18), date DATE, PRIMARY KEY (id), INDEX idx1_tbl4_cg (date) WITH COLUMN GROUP(all columns, each column));
  

• Create a table named tbl5 and a columnstore index named idx1_tbl5_cg. Store redundant data of the id column in the index table.

  CREATE TABLE tbl5(id INT, name VARCHAR(18), date DATE, INDEX idx1_tbl5_cg (date) STORING(id) WITH COLUMN GROUP(each column));
  

Create a columnstore index by using the CREATE INDEX statement

Create a columnstore index by using the CREATE INDEX statement

You can use the CREATE INDEX statement to create a columnstore index for an existing table. You can use the CREATE INDEX statement to create a columnstore index for an existing table.

Syntax: Syntax:

CREATE [UNIQUE] INDEX index_name ON table_name (column_name) [STORING(column_name, ...)] WITH COLUMN GROUP([all columns, ]each column);

The following table describes the parameters in the syntax. The following table describes the parameters in the syntax.

• UNIQUE: optional. Specifies a unique index. You must specify this keyword when you create a unique index.

• UNIQUE: optional. Specifies a unique index. You must specify this keyword when you create a unique index.

• index_name: specifies the name of the index to be created.

• index_name: specifies the name of the index to be created.

• table_name: specifies the name of the table to be indexed.

• table_name: specifies the name of the table to be indexed.

• column_name: specifies the columns to be indexed. Separate multiple columns with commas (,).

• STORING(column_name, ...) : optional. Specifies the redundant columns in the index table. If the column_name column is not an indexed column, it is redundantly stored in the columnstore format.

• WITH COLUMN GROUP([all columns, ]each column): specifies the columnstore attributes of the index.

  • WITH COLUMN GROUP(all columns, each column): specifies to create a rowstore and columnstore redundant index.
  • WITH COLUMN GROUP(all columns): specifies to create a rowstore index.
  • WITH COLUMN GROUP(each column): specifies to create a columnstore index.

Here are some examples:

After you create a table named tbl6, create a columnstore index named idx1_tbl6_cg. After you create a table named tbl6, create a columnstore index named idx1_tbl6_cg.

1. Create a table named tbl6.

   CREATE TABLE tbl6(id INT, name VARCHAR(20), age INT);
   

2. Create a columnstore index named idx1_tbl6_cg for the tbl6 table and store redundant data of the age column in the index table.

   CREATE INDEX idx1_tbl6_cg ON tbl6(id) STORING(age) WITH COLUMN GROUP(each column);
   

Create a columnstore index by using the ALTER TABLE statement

Create a columnstore index by using the ALTER TABLE statement

You can use the ALTER TABLE statement to create a columnstore index for an existing table. You can use the ALTER TABLE statement to create a columnstore index for an existing table.

Syntax: Syntax:

ALTER TABLE table_name ADD [UNIQUE] INDEX|KEY [index_name](column_name) [STORING(column_name, ...)] WITH COLUMN GROUP([all columns, ]each column);

The following table describes the parameters in the syntax. The following table describes the parameters in the syntax.

• table_name: specifies the name of the table to be indexed.

• table_name: specifies the name of the table to be indexed.

• UNIQUE: optional. Specifies a unique index. You must specify this keyword when you create a unique index.

• UNIQUE: optional. Specifies a unique index. You must specify this keyword when you create a unique index.

• INDEX|KEY: specifies the index keyword. You can use INDEX or KEY in this statement.

• INDEX|KEY: specifies the index keyword. You can use INDEX or KEY in this statement.

• index_name: optional. Specifies the name of the index to be created. If you do not specify this parameter, the index name is the same as the column name.

• column_name: specifies the columns to be indexed. Separate multiple columns with commas (,).

• STORING(column_name, ...) : optional. Specifies the redundant columns in the index table. If the column_name column is not an indexed column, it is redundantly stored in the columnstore format.

• WITH COLUMN GROUP([all columns, ]each column): specifies the columnstore attributes of the index.

  • WITH COLUMN GROUP(all columns, each column): specifies to create a rowstore and columnstore redundant index.
  • WITH COLUMN GROUP(all columns): specifies to create a rowstore index.
  • WITH COLUMN GROUP(each column): specifies to create a columnstore index.

Here are some examples: Here are some examples:

After you create a table named tbl7, create a columnstore index named idx1_tbl6_cg.

1. Create a table named tbl7.

   CREATE TABLE tbl7(id INT, name VARCHAR(20), age INT);
   

2. Create a columnstore index named idx1_tbl7_cg for the tbl7 table and store redundant data of the age column in the index table.

   ALTER TABLE tbl7 ADD INDEX idx1_tbl7_cg(id) STORING(age) WITH COLUMN GROUP(each column);
   

Create a multi-value index

Create a multi-value index

OceanBase Database allows you to create a multi-value index when you create a table by using the CREATE TABLE statement. However, you cannot add a multi-value index to an existing table. You can use the ALTER TABLE statement to drop an existing index. For a primary table that contains a multi-value index, you can perform complex DML operations.

Limitations on multivalued indexes

• Each multivalued index can contain only one multivalued key part. However, the CAST(... AS ... ARRAY) expression can reference multiple arrays in a JSON document, as shown in the following example:

  CAST(data->'$.arr[*][*]' AS UNSIGNED ARRAY)
  

  In this case, all matching JSON expressions are stored as a single flat array in the index. In this case, all matching JSON expressions are stored as a single flat array in the index.

• Multivalued indexes cannot be covering indexes.

• The only type of expression allowed in a multivalued key part is a JSON expression. The expression does not have to reference existing elements in the JSON document inserted into the index column, but it must be syntactically valid.

• Since the index records of the same clustered index record are scattered in the multivalued index, multivalued indexes do not support range scans or index-only scans.

• Multivalued indexes cannot be used in foreign key specifications.

• Index prefixes cannot be defined for multivalued indexes.

• Multivalued indexes cannot be defined on Binary data.

• For information about the support of multivalued indexes for offline DDL, see Offline DDL.

Create a multivalued index by using the CREATE TABLE statement

The simplified syntax for creating a multivalued index by using the CREATE TABLE statement is as follows:

Create a multivalued index:

CREATE TABLE table_name(column_name column_definition, [column_name column_definition, ...],
    json_column_name JSON, 
    [UNIQUE] INDEX idx_json_name((CAST(json_column_name->'$.json_field_name' AS UNSIGNED ARRAY)))
);

Create a composite multivalued index:

CREATE TABLE table_name(column_name column_definition, [column_name column_definition, ...],
    json_column_name JSON,
    INDEX idx_json_name(column_name, [column_name, ...] (CAST(json_column_name->'$.json_field_name' AS CHAR(n) ARRAY)))
);                   

The following table describes the related parameters:

• table_name: specifies the name of the table to be created.

• table_name: specifies the name of the table to be created.

• column_name: specifies the name of a column of a data type other than JSON.

• column_definition: specifies the data type of each column in the table.

• column_definition: specifies the data type of each column in the table.

• json_column_name: specifies the name of a JSON column in the table.

• UNIQUE: optional. Specifies to create a unique multivalued index. In a unique multivalued index, the index key cannot be repeated.

  Notice

  A multivalued index can be defined as a unique index. If you define a multivalued index as a unique index, an error will be returned when you attempt to insert a value that already exists in the multivalued index. If duplicate values already exist, attempting to add a unique multivalued index will fail.

• idx_json_name: specifies the name of the multivalued index to be created.

• json_field_name: specifies the name of the field in the JSON column to be indexed.

• json_field_name: specifies the name of the field in the JSON column to be indexed.

For more information about the syntax of the CREATE TABLE statement, see CREATE TABLE. For more information about the syntax of the CREATE TABLE statement, see CREATE TABLE.

Here is an example of creating a multivalued index:

1. Create a table named json_tbl1 and create a multivalued index named idx1_json_tbl1 in the table.

   CREATE TABLE json_tbl1(col1 INT, col2 VARCHAR(18), col3 JSON,
      INDEX idx1_json_tbl1((CAST(col3->'$.ipcode' AS UNSIGNED ARRAY)))
   );
   

2. Insert test data into the json_tbl1 table by using the following statement:

3. Insert test data into the json_tbl1 table by using the following statement:

   INSERT INTO json_tbl1 VALUES(1, 'a1', '{"user":"Jk001","user_id":37,"ipcode":[94582,94536]}'),
      (2, 'a2', '{"user":"Jk002","user_id":22,"ipcode":[94568,94507,94582]}'),
      (3, 'a3', '{"user":"Jk003","user_id":31,"ipcode":[94477,94507]}'),
      (4, 'a4', '{"user":"Jk004","user_id":72,"ipcode":[94536]}'),
      (5, 'a5', '{"user":"Jk005","user_id":56,"ipcode":[94507,94582]}');
   

Here is an example of creating a multivalued index:

Create a table named json_tbl2 and create a composite multivalued index named idx1_json_tbl2 in the table.

CREATE TABLE json_tbl2(col1 INT, col2 VARCHAR(18), col3 JSON,
      INDEX idx1_json_tbl2(col1, (CAST(col3->'$.ipcode' AS UNSIGNED ARRAY)))
);

Support for complex DML operations

Support for complex DML operations

For a primary table that contains a multivalued index, you can perform complex DML operations. Here are some examples:

• INSERT INTO ON DUPLICATE KEY

  CREATE TABLE customers(id INT PRIMARY KEY, uid INT, j JSON);
  
  INSERT INTO customers VALUES (10, 21, '{"user":"Jack","user_id":37,"zipcode":[94582,94536]}');
  
  CREATE INDEX zips ON customers((CAST(j->'$.zipcode' AS UNSIGNED ARRAY)));
  
  INSERT INTO customers 
  VALUES (10, 22, '{"user":"Jack","user_id":37,"zipcode":[24582,34536]}') 
  ON DUPLICATE KEY 
  UPDATE j = '{"user":"Jack","user_id":37,"zipcode":[34582,44536]}';
  

• REPLACE INTO

  CREATE TABLE test_array(id int,a varchar(50),j json,INDEX index_1( (CAST(j->'$.key' AS UNSIGNED ARRAY))) );
  
  INSERT INTO test_array VALUES(1,'test','{"key":[1,2,3,4]}');
  
  REPLACE INTO test_array VALUES(1,'test','{"key":[8,9,13,43]}');
  

• UPDATE and DELETE operations across multiple tables

  Prepare the following sample tables:

  CREATE TABLE table_1(a int PRIMARY KEY, b json, INDEX index_1( (CAST(b->'$.key' AS UNSIGNED ARRAY))) );
  CREATE TABLE table_2(a int PRIMARY KEY, b json);
  
  INSERT INTO table_1 values(1, '{"key":[1,2,3,4]}');
  INSERT INTO table_2 values(1, '{"key":[1,2,3,4]}');
  

  Here is an example of the UPDATE operation:

  UPDATE table_1 JOIN table_2 ON table_1.a = table_2.a 
  SET table_1.b = '{"key":[11,12,13,14]}', table_2.b = '{"key":[21,22,23,24]}';
  
  UPDATE  table_1 JOIN table_2 ON table_1.a = table_2.a SET table_1.b = '{"key":[31,32,33,34]}';
  UPDATE table_1 JOIN table_2 ON table_1.a = table_2.a SET table_2.b = table_1.b;
  

  Here is an example of the DELETE operation:

  DELETE table_1, table_2 FROM table_1 JOIN table_2 ON table_1.a = table_2.a;
  DELETE table_1 FROM table_1 JOIN table_2 ON table_1.a = table_2.a;
  DELETE table_1 FROM table_1 JOIN table_2 ON table_1.a = table_2.a;
  

• Updates to views

  CREATE TABLE test(id int,a varchar(50),j json,INDEX index_1( (CAST(j->'$.key' AS UNSIGNED ARRAY))) );
  
  INSERT INTO test values(1,'test','{"key":[1,2,3,4]}');
  
  CREATE VIEW vtest AS SELECT * FROM test;
  
  UPDATE vtest SET j='{"key":[1,2]}';
  

Add a multivalued index to an existing table

To add a multivalued index to an existing table, you must first enable the _enable_add_fulltext_index_to_existing_table parameter.

ALTER SYSTEM SET _enable_add_fulltext_index_to_existing_table = true;

The following table describes the types of multivalued indexes that can be added to an existing table.

Here are some examples: Here are some examples:

1. Create a table named json_tbl1 and create a non-unique multivalued index named idx1_json_tbl1 in the table.

   CREATE TABLE json_tbl1(id INT PRIMARY KEY, uid INT, j JSON);
   
   INSERT INTO json_tbl1 VALUES 
   (10, 21, '{"user":"Jack","user_id":37,"zipcode":[94582,94536]}'),
   (11, 22, '{"user":"Jill","user_id":22,"zipcode":[94568,94507,94582]}'),
   (12, 23, '{"user":"Bob","user_id":31,"zipcode":[94477,94507]}'),
   (13, 24, '{"user":"Mary","user_id":72,"zipcode":[94536]}'),
   (14, 25, '{"user":"Ted","user_id":56,"zipcode":[94507,94582]}');
   
   CREATE INDEX idx1_json_tbl1 ON json_tbl1((CAST(j->'$.zipcode' AS UNSIGNED ARRAY)));
   

2. Create a unique multivalued index named idx1_json_tbl2.

   CREATE TABLE json_tbl2(id INT PRIMARY KEY, uid INT, j JSON);
   
   INSERT INTO json_tbl2 VALUES 
   (10, 21, '{"user":"Jack","user_id":37,"zipcode":[94507,94536]}'),
   (11, 22, '{"user":"Jill","user_id":22,"zipcode":[94568,94582]}'),
   (12, 23, '{"user":"Bob","user_id":31,"zipcode":[94477]}');
   
   CREATE UNIQUE INDEX idx1_json_tbl2 ON json_tbl2((CAST(j->'$.zipcode' AS UNSIGNED ARRAY)));
   

3. Create a composite multivalued index named idx1_json_tbl3.

   CREATE TABLE json_tbl3 (
    id BIGINT NOT NULL PRIMARY KEY,
    c1 BIGINT NOT NULL,
    modified  BIGINT NOT NULL,
    custinfo JSON
   );
   
   INSERT INTO json_tbl3 VALUES 
   (10, 21, 101, '{"user":"Jack","user_id":37,"zipcode":[94582,94536]}'),
   (11, 22, 102, '{"user":"Jill","user_id":22,"zipcode":[94568,94507,94582]}'),
   (12, 23, 103, '{"user":"Bob","user_id":31,"zipcode":[94477,94507]}'),
   (13, 24, 104, '{"user":"Mary","user_id":72,"zipcode":[94536]}'),
   (14, 25, 105, '{"user":"Ted","user_id":56,"zipcode":[94507,94582]}');
   
   CREATE INDEX idx1_json_tbl3 ON json_tbl3 (c1, modified,
        (CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY)) );
   

4. Create a unique composite multivalued index named idx1_json_tbl4.

   CREATE TABLE json_tbl4 (
    id BIGINT NOT NULL PRIMARY KEY,
    c1 BIGINT NOT NULL,
    modified  BIGINT NOT NULL,
    custinfo JSON
   );
   
   INSERT INTO json_tbl4 VALUES 
   (10, 21, 101, '{"user":"Jack","user_id":37,"zipcode":[94582,94536]}'),
   (11, 22, 102, '{"user":"Jill","user_id":22,"zipcode":[94568,94507,94582]}'),
   (12, 23, 103, '{"user":"Bob","user_id":31,"zipcode":[94477,94507]}'),
   (13, 24, 104, '{"user":"Mary","user_id":72,"zipcode":[94536]}'),
   (14, 25, 105, '{"user":"Ted","user_id":56,"zipcode":[94507,94582]}');
   
   CREATE UNIQUE INDEX idx1_json_tbl4 ON json_tbl4 (c1, modified,
        (CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY)) );
   

Use multi-value indexes in queries

Use multi-value indexes in queries

The optimizer uses multi-value indexes when the WHERE clause contains the following functions: The optimizer uses multi-value indexes when the WHERE clause contains the following functions:

• MEMBER OF()
• JSON_CONTAINS()
• JSON_OVERLAPS

Here are some examples:

• Example of using MEMBER OF().

• Example of using MEMBER OF().

  SELECT *
     FROM json_tbl1
     WHERE 94507 MEMBER OF(col3->'$.ipcode');
  

  The result is as follows: The result is as follows:

  +------+------+-------------------------------------------------------------------+
  | col1 | col2 | col3                                                              |
  +------+------+-------------------------------------------------------------------+
  |    2 | a2   | {"user": "Jk002", "ipcode": [94568, 94507, 94582], "user_id": 22} |
  |    3 | a3   | {"user": "Jk003", "ipcode": [94477, 94507], "user_id": 31}        |
  |    5 | a5   | {"user": "Jk005", "ipcode": [94507, 94582], "user_id": 56}        |
  +------+------+-------------------------------------------------------------------+
  3 rows in set
  

• Query the json_tbl1 table to find rows where the ipcode field in the JSON data of the col3 column contains the value 94507. Use the EXPLAIN command to view the query execution plan.

  EXPLAIN SELECT *
     FROM json_tbl1
     WHERE 94507 MEMBER OF(col3->'$.ipcode');
  

  The result is as follows: The result is as follows:

  +-------------------------------------------------------------------------------------------------------------------------------------------------------+
  | Query Plan                                                                                                                                            |
  +-------------------------------------------------------------------------------------------------------------------------------------------------------+
  | =====================================================================                                                                                 |
  | |ID|OPERATOR        |NAME                     |EST.ROWS|EST.TIME(us)|                                                                                 |
  | ---------------------------------------------------------------------                                                                                 |
  | |0 |TABLE RANGE SCAN|json_tbl1(idx1_json_tbl1)|3       |13          |                                                                                 |
  | =====================================================================                                                                                 |
  | Outputs & filters:                                                                                                                                    |
  | -------------------------------------                                                                                                                 |
  |   0 - output([json_tbl1.col1], [json_tbl1.col2], [json_tbl1.col3]), filter(nil)                                                                       |
  |       access([json_tbl1.__pk_increment], [json_tbl1.col3], [json_tbl1.col1], [json_tbl1.col2]), partitions(p0)                                        |
  |       is_index_back=true, is_global_index=false,                                                                                                      |
  |       range_key([json_tbl1.SYS_NC_mvi_19], [json_tbl1.__pk_increment], [json_tbl1.__doc_id_1715756608129539]), range(94507,MIN,MIN ; 94507,MAX,MAX),  |
  |       range_cond([JSON_MEMBER_OF(94507, json_tbl1.SYS_NC_mvi_19)])                                                                                    |
  +-------------------------------------------------------------------------------------------------------------------------------------------------------+
  12 rows in set
  

• Example of using JSON_CONTAINS().

• Example of using JSON_CONTAINS().

  SELECT *
     FROM json_tbl1
     WHERE JSON_CONTAINS(col3->'$.ipcode', CAST('[94507,94582]' AS JSON));
  

  The result is as follows: The result is as follows:

  +------+------+-------------------------------------------------------------------+
  | col1 | col2 | col3                                                              |
  +------+------+-------------------------------------------------------------------+
  |    2 | a2   | {"user": "Jk002", "ipcode": [94568, 94507, 94582], "user_id": 22} |
  |    5 | a5   | {"user": "Jk005", "ipcode": [94507, 94582], "user_id": 56}        |
  +------+------+-------------------------------------------------------------------+
  2 rows in set
  

• Example of using JSON_OVERLAPS().

• Example of using JSON_OVERLAPS().

  SELECT *
     FROM json_tbl1
     WHERE JSON_OVERLAPS(col3->'$.ipcode', CAST('[94507,94582]' AS JSON));
  

  The result is as follows: The result is as follows:

  +------+------+-------------------------------------------------------------------+
  | col1 | col2 | col3                                                              |
  +------+------+-------------------------------------------------------------------+
  |    1 | a1   | {"user": "Jk001", "ipcode": [94582, 94536], "user_id": 37}        |
  |    2 | a2   | {"user": "Jk002", "ipcode": [94568, 94507, 94582], "user_id": 22} |
  |    3 | a3   | {"user": "Jk003", "ipcode": [94477, 94507], "user_id": 31}        |
  |    5 | a5   | {"user": "Jk005", "ipcode": [94507, 94582], "user_id": 56}        |
  +------+------+-------------------------------------------------------------------+
  4 rows in set
  

Create a full-text index

In the current version of OceanBase Database in MySQL-compatible mode, you can use the CREATE TABLE, CREATE FULLTEXT INDEX, or ALTER TABLE statement to create a full-text index. In the current version of OceanBase Database in MySQL-compatible mode, you can use the CREATE TABLE, CREATE FULLTEXT INDEX, or ALTER TABLE statement to create a full-text index.

For more information about full-text indexes, see Full-text indexes.

For more information about full-text queries, see Full-text queries. For more information about full-text queries, see Full-text queries.

Limitations and considerations for full-text indexes

You can create a full-text index on a non-partitioned table or a partitioned table with or without a primary key. The following limitations apply:

• A full-text index can be created only on columns of the CHAR, VARCHAR, or TEXT type.
• Only local (LOCAL) full-text indexes are supported in the current version.
• The UNIQUE keyword cannot be specified when creating a full-text index.
• If you want to create a full-text index that involves multiple columns, these columns must have the same character set.
• For information about whether full-text indexes support offline DDL operations, see Offline DDL.

Create a full-text index by using the CREATE TABLE statement

You can create a full-text index for a table when you create the table by using the CREATE TABLE statement. The simplified syntax is as follows:

CREATE TABLE table_name(column_name column_definition,[column_name column_definition,...]
    FULLTEXT [INDEX | KEY] [index_name](column_name)
    [WITH PARSER tokenizer_option]
    [PARSER_PROPERTIES[=](parser_properties_list)]
    [LOCAL]);

tokenizer_option:
    SPACE
    | NGRAM
    | BENG
    | IK
    | NGRAM2

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

For more information about the CREATE TABLE statement, see CREATE TABLE.

The following table describes the related parameters: The following table describes the related parameters:

• table_name: specifies the name of the table to be created.

• table_name: specifies the name of the table to be created.

• column_name: specifies the column of the table.

• column_name: specifies the column of the table.

• column_definition: specifies the data type of each column in the table.

• column_definition: specifies the data type of each column in the table.

• FULLTEXT: specifies that a full-text index is to be created.

  Notice

  In the current version, only local full-text indexes are supported.

• INDEX | KEY: specifies the INDEX or KEY keyword in the statement.

• index_name: specifies the name of the index to be created. This parameter is optional. If you do not specify this parameter, the index name is the same as the column name.

• index_name: specifies the name of the index to be created. This parameter is optional. If you do not specify this parameter, the index name is the same as the column name.

• WITH PARSER tokenizer_option: specifies the tokenizer for the full-text index. Valid values: SPACE, NGRAM, NGRAM2, BENG, and IK. The default value is SPACE.

  • SPACE: specifies that the tokenizer splits the text by space. You can specify the following parameters:

    Parameter	Value Range
    min_token_size	[1, 16]
    max_token_size	[10, 84]

  • NGRAM: specifies that the tokenizer splits the text by N-Gram (Chinese). You can specify the following parameters:

    Parameter	Value Range
    ngram_token_size	[1, 10]

  • NGRAM2: specifies that the tokenizer splits the text into consecutive characters in the range of min_ngram_size to max_ngram_size.

    Parameter	Value Range
    min_ngram_size	[1, 16]
    max_ngram_size	[1, 16]

  • BENG: specifies that the tokenizer splits the text by Beng (basic English). You can specify the following parameters:

    Parameter	Value Range
    min_token_size	[1, 16]
    max_token_size	[10, 84]

  • IK: specifies that the tokenizer splits the text by IK (Chinese). The utf-8 character set is supported. You can specify the following parameter:

    Parameter	Value Range
    ik_mode	smart max_word

  You can use the TOKENIZE function to view the tokenization result of the text in JSON format based on the specified tokenizer and parameters.

• PARSER_PROPERTIES[=](parser_properties_list): specifies the properties of the tokenizer. Valid values:

  • min_token_size: specifies the minimum token length. The default value is 3, and the value ranges from 1 to 16.

  • max_token_size: specifies the maximum token length. The default value is 84, and the value ranges from 10 to 84.

  • ngram_token_size: specifies the token length for the NGRAM tokenizer. This parameter is valid only for the NGRAM tokenizer. The default value is 2, and the value ranges from 1 to 10.

  • ik_mode: specifies the tokenization mode for the IK tokenizer. Valid values:

    • smart: the default value. The tokenizer uses the dictionary to improve the accuracy of tokenization and prioritizes the boundaries of the dictionary terms, which may reduce unnecessary expansion.
    • max_word: the tokenizer identifies the terms defined in the dictionary but does not affect the maximum expansion of tokenization. Even if terms are defined in the dictionary, the max_word mode still tries to split the text into more terms.

• LOCAL: specifies that a local index is to be created. This parameter is optional.

Here are some examples:

• Create a table named tbl1 and create a full-text index named full_idx1_tbl1 for the table.

  CREATE TABLE tbl1(id INT, name VARCHAR(18), date DATE, PRIMARY KEY (id), FULLTEXT INDEX full_idx1_tbl1(name));
  

• Create a table named tbl2 and create a full-text index named full_idx1_tbl2 for the table. Specify the NGRAM tokenizer for the full-text index and set the tokenizer properties by using the PARSER_PROPERTIES parameter.

  CREATE TABLE tbl2(id INT, name VARCHAR(18), doc TEXT, 
      FULLTEXT INDEX full_idx1_tbl2(name, doc)
        WITH PARSER NGRAM
        PARSER_PROPERTIES=(ngram_token_size=3));
  

Use the CREATE FULLTEXT INDEX statement to create a full-text index

Use the CREATE FULLTEXT INDEX statement to create a full-text index

You can use the CREATE FULLTEXT INDEX statement to create a full-text index for an existing table. The syntax is as follows: You can use the CREATE FULLTEXT INDEX statement to create a full-text index for an existing table. The syntax is as follows:

CREATE FULLTEXT INDEX index_name ON table_name (column_name, [column_name ...])
    [WITH PARSER tokenizer_option]
    [PARSER_PROPERTIES[=](parser_properties_list)]
    [LOCAL];

tokenizer_option:
    SPACE
    | NGRAM
    | BENG
    | IK
    | NGRAM2

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

For more information about the CREATE INDEX statement, see CREATE INDEX. For more information about the CREATE INDEX statement, see CREATE INDEX.

The following table describes the related parameters: The following table describes the related parameters:

• index_name: specifies the name of the index to be added.

• table_name: specifies the name of the table to create the index on.

• column_name: specifies the columns to be indexed. If multiple columns are specified, separate them with commas.

• WITH PARSER tokenizer_option: specifies the tokenizer for the full-text index. Valid values are as follows:

  • SPACE: the default value, which indicates that the tokenizer splits the text by spaces. You can specify the following properties:

    Property	Value Range
    min_token_size	[1, 16]
    max_token_size	[10, 84]

  • NGRAM: indicates the N-Gram tokenizer for Chinese text. You can specify the following properties:

    Property	Value Range
    ngram_token_size	[1, 10]

  • NGRAM2: indicates the tokenizer that splits the text into consecutive characters of lengths ranging from min_ngram_size to max_ngram_size. You can specify the following properties:

    Property	Value Range
    min_ngram_size	[1, 16]
    max_ngram_size	[1, 16]

  • BENG: indicates the tokenizer for basic English text. You can specify the following properties:

    Property	Value Range
    min_token_size	[1, 16]
    max_token_size	[10, 84]

  • IK: indicates the tokenizer for Chinese text. Currently, only the utf-8 character set is supported. You can specify the following properties:

    Property	Value Range
    ik_mode	smart max_word

  You can use the TOKENIZE function to view the tokenization result of the text based on the specified tokenizer and JSON parameters.

• PARSER_PROPERTIES[=](parser_properties_list): specifies the properties of the tokenizer. Valid values are as follows:

  • min_token_size: specifies the minimum token length. The default value is 3, and the value ranges from 1 to 16.

  • max_token_size: specifies the maximum token length. The default value is 84, and the value ranges from 10 to 84.

  • ngram_token_size: specifies the token length for the NGRAM tokenizer. This parameter is valid only for the NGRAM tokenizer. The default value is 2, and the value ranges from 1 to 10.

  • ik_mode: specifies the tokenization mode for the IK tokenizer. Valid values are as follows:

    • smart: the default value. This mode uses the dictionary to improve the accuracy of tokenization. The boundaries of the dictionary words are prioritized, which may reduce unnecessary expansions.
    • max_word: this mode identifies the words defined in the dictionary but does not affect the maximum expansion of tokenization. Even if the dictionary defines words, the max_word mode still tries to split the text into more words.

• LOCAL: specifies whether to create a local index.

Here is an example: Here is an example:

After you create the tbl3 table, create the full-text index ft_idx1_tbl3.

1. Create the tbl3 table.

2. Create the tbl3 table.

   CREATE TABLE tbl3(col1 INT, col2 VARCHAR(4096));
   

3. Create the full-text index ft_idx1_tbl3 on the tbl3 table. Specify the IK tokenizer for the full-text index and set the tokenizer properties by using the PARSER_PROPERTIES option.

   CREATE FULLTEXT INDEX ft_idx1_tbl3 ON tbl3(col2)
       WITH PARSER IK
       PARSER_PROPERTIES=(ik_mode='max_word');
   

Create a full-text index with the ALTER TABLE statement

You can execute an ALTER TABLE statement to add a full-text index to an existing table. The syntax is as follows:

ALTER TABLE table_name ADD FULLTEXT [INDEX | KEY] [index_name](column_name, [column_name ...])
    [WITH PARSER tokenizer_option]
    [PARSER_PROPERTIES[=](parser_properties_list)]
    [LOCAL];

tokenizer_option:
    SPACE
    | NGRAM
    | BENG
    | IK
    | NGRAM2

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

For more information about the ALTER TABLE statement, see ALTER TABLE.

The following table lists parameters of the statement:

• table_name : specifies the name of the table on which you want to create an index.

• INDEX | KEY: optional. This parameter indicates that you can use either INDEX or KEY in the statement. The default value is KEY.

• index_name: optional. This parameter specifies the name of the index to be created. If you do not specify a name, the default name is the same as the name of the column specified.

• column_name : specifies the columns on which the index is to be created. If multiple columns are to be indexed, separate them with commas (,).

• WITH PARSER tokenizer_option : optional. This parameter specifies the tokenizer for the full-text index. Valid values:

  • SPACE: default. This option indicates that the space tokenizer is used. You can specify the following properties:

    Property	Value Range
    min_token_size	[1, 16]
    max_token_size	[10, 84]

  • NGRAM: specifies the NGRAM (Chinese) tokenizer. You can specify the following property:

    Property	Value Range
    ngram_token_size	[1, 10]

  • NGRAM2: specifies the NGRAM2 tokenizer. You can specify the following properties:

    Property	Value Range
    min_ngram_size	[1, 16]
    max_ngram_size	[1, 16]

  • BENG: specifies the Beng (English) tokenizer. You can specify the following properties:

    Property	Value Range
    min_token_size	[1, 16]
    max_token_size	[10, 84]

  • IK: specifies the IK (Chinese) tokenizer. Currently, only the utf-8 character set is supported. You can specify the following property:

    Property	Value Range
    ik_mode	smart max_word

  For information about how to verify the tokenizer, see TOKENIZE.

• PARSER_PROPERTIES[=](parser_properties_list): optional. This parameter specifies the properties of the tokenizer. Valid values:

  • min_token_size : specifies the minimum token length. The default value is 3, and the value ranges from 1 to 16.

  • max_token_size : specifies the maximum token length. The default value is 84, and the value ranges from 10 to 84.

  • ngram_token_size : specifies the token length for the NGRAM tokenizer. The value is valid only for the NGRAM tokenizer. The default value is 2, and the value ranges from 1 to 10.

  • ik_mode: specifies the tokenizer mode for the IK tokenizer. Valid values:

    • smart: default. This mode specifies that the dictionary is used to improve the accuracy of tokenization. The boundaries in the dictionary are prioritized to minimize unnecessary token expansions.
    • max_word: specifies that the tokens defined in the dictionary are to be identified but not affect the maximum token expansion. In this mode, even if the dictionary contains the token definitions, the text is still split into as many tokens as possible.

• LOCAL: optional. This parameter specifies whether to create a local index.

Example: Example:

Create the tbl4 table and add a full-text index ft_idx1_tbl4 to it.

1. Create the tbl4 table.

2. Create the tbl4 table.

   CREATE TABLE tbl4(col1 INT, col2 TEXT);
   

3. Add the ft_idx1_tbl4 full-text index to the tbl4 table. Set the tokenizer of the full-text index to BENG and specify the tokenizer properties by using the PARSER_PROPERTIES clause.

   ALTER TABLE tbl4 ADD FULLTEXT INDEX ft_idx1_tbl4(col2)
       WITH PARSER BENG
       PARSER_PROPERTIES=(min_token_size=2, max_token_size=64);
   

Create a search index

Create a search index by using the CREATE SEARCH INDEX statement

You can execute the CREATE SEARCH INDEX statement to create a search index on an existing table. Syntax:

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

For more information on the syntax and supported data types, see CREATE INDEX.

The following parameters are used in the preceding syntax:

• index_name: specifies the name of the search index.
• table_name: specifies the name of the table on which the search index is to be created.
• search_index_column: specifies the columns for the search index. You can specify one or more columns. For each column, you can specify its path or type by using the WITH (...) clause.
• INCLUDE_PATHS / EXCLUDE_PATHS: specifies the JSON paths for the column. You can specify either INCLUDE_PATHS or EXCLUDE_PATHS.
• INCLUDE_TYPES: specifies the JSON scalar types (JSON_STRING and JSON_NUMBER) to be indexed.
• LOCAL: specifies to create a local search index for the partitioned table. A partitioned table only supports local search indexes.

Here is an example:

After you create the tbl_search_1 table, create the search_idx_1 search index on the table.

1. Create the tbl_search_1 table.

   CREATE TABLE tbl_search_1 (
     id INT,
     doc JSON,
     c2 VARCHAR(64)
   ) ORGANIZATION = HEAP;
   

2. Create the search_idx_1 search index on the tbl_search_1 table. Specify the JSON paths to be indexed for the JSON column and the scalar types to be indexed.

   CREATE SEARCH INDEX search_idx_1 ON tbl_search_1(
     doc WITH (
       INCLUDE_PATHS = ('$.name', '$.score'),
       INCLUDE_TYPES = (JSON_STRING, JSON_NUMBER)
     ),
     c2
   );
   

Create a search index by using the CREATE TABLE statement

You can execute the CREATE TABLE statement to create a search index when you create a table. Syntax:

CREATE TABLE table_name (
    ...,
    SEARCH INDEX index_name (
        search_index_column
        [, search_index_column ...]
    )
) ORGANIZATION = HEAP;

For more information on the syntax and supported data types, see CREATE TABLE.

The following parameters are used in the preceding syntax:

• table_name: specifies the name of the table.
• index_name: specifies the name of the search index.
• search_index_column: specifies the columns for the search index. You can specify one or more columns. For each column, you can specify its path or type by using the WITH (...) clause.

Here is an example:

Create the tbl_search_3 table and create the search_idx_3 search index on the table at the same time.

CREATE TABLE tbl_search_3 (
  id INT,
  profile JSON,
  tag VARCHAR(32),
  SEARCH INDEX search_idx_3 (
    profile WITH (EXCLUDE_PATHS = ('$.debug')),
    tag
  )
) ORGANIZATION = HEAP;

Add a search index by using the ALTER TABLE statement

You can execute the ALTER TABLE statement to add a search index to an existing table. Syntax:

ALTER TABLE table_name
ADD SEARCH INDEX [index_name] (
    search_index_column
    [, search_index_column ...]
);

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

For more information on the syntax, see ALTER TABLE.

The following parameters are used in the preceding syntax:

• table_name: specifies the name of the table to which the search index is to be added.
• index_name: specifies the name of the search index. If you do not specify this parameter, the system generates a default index name based on some rules.
• search_index_column: specifies the columns for the search index. You can specify one or more columns. For each column, you can specify its path or type by using the WITH (...) clause.
• INCLUDE_PATHS / EXCLUDE_PATHS: specifies the JSON paths for the column. You can specify either INCLUDE_PATHS or EXCLUDE_PATHS.
• INCLUDE_TYPES: specifies the JSON scalar types (JSON_STRING and JSON_NUMBER) to be indexed.

Here is an example:

After you create the tbl_search_2 table, add the search_idx_2 search index to the table by executing the ALTER TABLE statement.

1. Create the tbl_search_2 table.

   CREATE TABLE tbl_search_2 (
     id INT,
     profile JSON,
     tag VARCHAR(32)
   ) ORGANIZATION = HEAP;
   

2. Add the search_idx_2 search index to the tbl_search_2 table.

   ALTER TABLE tbl_search_2
   ADD SEARCH INDEX search_idx_2 (
     profile WITH (EXCLUDE_PATHS = ('$.debug')),
     tag
   );
   

References

For more information about indexes in MySQL-compatible mode, see the following topics: For more information about indexes in MySQL-compatible mode, see the following topics:

• Overview of indexes

• Overview of indexes

• Create an index on a partitioned table

• View indexes

• View indexes

• Drop an index