Create an index

This topic describes how to create an index.

Background information

OceanBase Database allows you to create local indexes, global indexes, unique indexes, non-unique indexes, spatial indexes, or function-based indexes on non-partitioned and partitioned tables. A local unique index on a partitioned table must include a partitioning key of the table. For more information about indexes, see Overview of indexes.

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 on a table in OceanBase Database, make sure that the index name is unique within the database.

Create a normal index

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

Use the CREATE TABLE statement to create a normal index

You can create an index when you create a table by using the CREATE TABLE statement.

The syntax is as follows:

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

The parameters are described as follows:

• TEMPORARY: Optional. Specifies that the table to be created is a temporary table.

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

• column_name: the name of a column in the table.

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

• UNIQUE: specifies to create a unique index. This parameter is optional. You must add this keyword when you create a unique index.

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

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

Example: Create 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 normal index

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

The syntax is as follows:

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

The parameters are described as follows:

• UNIQUE: specifies to create a unique index. This parameter is optional. You must add this keyword when you create a unique index.

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

• table_name: the name of the table on which the index is to be created.

• column_name: the name of the column to be indexed. If you specify multiple columns, separate the columns with commas (,).

Example: Create a table named tbl2 table and then create an index named tbl2_idx1.

1. Create a table named tbl2.

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

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

   CREATE INDEX tbl2_idx1 ON tbl2(id);
   

Use the ALTER TABLE statement to create a normal index

You can use the ALTER TABLE statement to create one or more indexes on an existing table.

The syntax is as follows:

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

The parameters are described as follows:

• table_name: the name of the table on which the index is to be created.

• UNIQUE: specifies to create a unique index. This parameter is optional. You must add this keyword when you create a unique index.

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

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

• column_name: the name of the column to be indexed. If you specify multiple columns, separate the columns with commas (,).

Example: Create a table named tbl3 and then create an index named tbl3_idx1.

1. Create a table named tbl3.

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

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

   ALTER TABLE tbl3 ADD INDEX tbl3_idx1(id);
   

Create a spatial index

OceanBase Database in MySQL mode supports spatial indexes. A spatial index describes the location of data stored on a medium and is used to improve the efficiency of data acquisition by the system.

Similar to creating a normal index, you can create a spatial index by using the CREATE TABLE, CREATE INDEX, or ALTER TABLE statement. The differences are that you must specify the SPATIAL keyword in the statement and the columns in a spatial index must have a NOT NULL constraint.

Limitations

Take note of the following limitations when you create a spatial index:

• You can create only a local spatial index. Global spatial indexes are not supported.

• You must define the SRID attribute for the columns on which a spatial index is to be created. Otherwise, the spatial index cannot take effect in subsequent queries. This is because, the coordinates corresponding to the SRID attribute are required for comparison based on the spatial index. In addition, the spatial index stores the minimum bounding rectangle (MBR) of a geometry, and the MBR comparison also depends on the SRID.

  For more information about SRID, see SRS.

• You can create a spatial index only on columns of spatial data types. For more information about the supported spatial data types, see Overview of spatial data types.

• You can create a spatial index only on columns that have the NOT NULL constraint.

  For a column without the NOT NULL constraint, you can use the ALTER TABLE statement to add the NOT NULL constraint to the column and then create a spatial index on the column. For more information about how to modify a column attribute, see Define column constraints.

Use the CREATE TABLE statement to create a spatial index

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 parameters are described as follows:

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

• column_g_name or column_name: the name of a column in the table.

• SRID 4326: the SRID of the column on which a spatial index is to be created.

• column_definition: the data type of the column in the table. The column on which a spatial index is to be created must be of a spatial data type. For more information about the supported spatial data types, see Overview of spatial data types.

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

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

Example: Create a table named tbl1_g and a spatial index named tbl1_g_idx1.

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

After you create an index, you can execute the SHOW INDEX statement to view its 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

Use the CREATE INDEX statement to create a spatial index

The syntax is as follows:

CREATE SPATIAL INDEX index_name ON table_name(column_g_name);

The parameters are described as follows:

• table_name: the name of the table on which the index is to be created.

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

• column_g_name: the name of the column to be indexed. If you specify multiple columns, separate the columns with commas (,). The column on which a spatial index is to be created must be of a spatial data type. For more information about the supported spatial data types, see Overview of spatial data types.

Example: Create a table named tbl2_g and then create a spatial index named tbl2_g_idx1.

1. Create a table named tbl2_g.

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

2. Create a spatial index named tbl2_g_idx1 on the table.

   CREATE SPATIAL INDEX tbl2_g_idx1 ON tbl2_g(g);
   

3. View 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
   

Use the ALTER TABLE statement to create a spatial index

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

The parameters are described as follows:

• table_name: the name of the table on which the index is to be created.

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

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

• column_g_name: the name of the column to be indexed. If you specify multiple columns, separate the columns with commas (,). The column on which a spatial index is to be created must be of a spatial data type. For more information about the supported spatial data types, see Overview of spatial data types.

Example: Create a table named tbl3_g and then create a spatial index named tbl3_g_idx1.

1. Create a table named tbl3_g.

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

2. Create a spatial index on the table.

   ALTER TABLE tbl3_g ADD SPATIAL INDEX tbl3_g_idx1(g);
   

3. View the index information.

   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-based index

A function-based index is an index built on the calculated results of one or more columns in a table. Function indexes are an optimization technique that allows you to quickly locate matching function values during queries, thus avoiding repeated calculations and improving query efficiency.

Suppose you have a table named sale with a column date_col of data type date:

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

If you need to retrieve data for March, you can use the MONTH() function to query the data:

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

In this scenario, the database needs to calculate month(date_col) for each row and filter out rows that do not meet the condition month(date_col) = 3. If you frequently use month information to filter data, the database needs to recalculate month(date_col) each time, which causes significant overhead. To resolve this issue, you can create a function-based index on date_col to store month(date_col) in the 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-based index. A function-based index can also be a unique index or a spatial index.

Limitations

In OceanBase Database MySQL mode, expressions for function indexes are restricted, and certain system functions are prohibited from being used in function-based index expressions. For a list of supported system functions, see List of supported system functions for function indexes. For a list of unsupported system functions, see List of unsupported system functions for function indexes.

Use the CREATE TABLE statement to create a function-based index

You can create a function-based index when you create a table by using the CREATE TABLE statement. The syntax is as follows:

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

The parameters are described as follows:

• table_name: the name of the table on which the index is to be created.

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

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

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

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

• expr: a valid function-based index expression, which can also be a boolean expression, such as c1=c1.

Example: Create a function-based index named tbl1_func_idx1.

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

After you successfully create the index, you can view the CREATE TABLE statement by using the SHOW CREATE TABLE statement:

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,
  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

Use the CREATE INDEX statement to create a function-based index

After you create a table, you can create a function-based index on an existing table. The syntax is as follows:

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

The parameters are described as follows:

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

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

• table_name: the name of the table on which the index is to be created.

• expr: a valid function-based index expression, which can also be a boolean expression, such as c1=c1.

Example: Create a function-based index named tbl2_func_idx1 with an index definition of c1+c2 < 1.

1. Create a table named tbl2_func.

   CREATE TABLE tbl2_func(c1 int, c2 int);
   

2. Create a function-based index named tbl2_func_idx1.

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

3. View the created function-based index.

   SHOW INDEX FROM tbl2_func;
   

   The return 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
   

Use the ALTER TABLE statement to create a function-based index

The syntax is as follows:

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

The parameters are described as follows:

• table_name: the name of the table on which the index is to be created.

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

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

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

• expr: a valid function-based index expression, which can also be a boolean expression, such as c1=c1.

1. Create a table named tbl3_func.

   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.

Use the CREATE TABLE statement to create a columnstore index

You can create a columnstore index when you create a table by using the CREATE TABLE statement.

The syntax is as follows:

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 parameters are described as follows:

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

• column_name: the name of a column in the table.

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

• UNIQUE: specifies to create a unique index. This parameter is optional. You must add this keyword when you create a unique index.

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

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

• STORING(column_name, ...): the redundant columns in the index table. This parameter is optional. Even though the column_name column is not an index column, it is redundantly stored in the columnar storage format.

• WITH COLUMN GROUP([all columns, ]each column): the columnar storage options for the index.

  • WITH COLUMN GROUP(all columns, each column): specifies to create a hybrid rowstore-columnstore 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 hybrid rowstore-columnstore index named idx1_tbl4_cg at the same time.

  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 at the same time, and redundantly store the 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));
  

Use the CREATE INDEX statement to create a columnstore index

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

The syntax is as follows:

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

The parameters are described as follows:

• UNIQUE: specifies to create a unique index. This parameter is optional. You must add this keyword when you create a unique index.

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

• table_name: the name of the table on which the index is to be created.

• column_name: the name of the column to be indexed. If you specify multiple columns, separate the columns with commas (,).

• STORING(column_name, ...): the redundant columns in the index table. This parameter is optional. Even though the column_name column is not an index column, it is redundantly stored in the columnar storage format.

• WITH COLUMN GROUP([all columns, ]each column): the columnar storage options for the index.

  • WITH COLUMN GROUP(all columns, each column): specifies to create a hybrid rowstore-columnstore 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 tbl6, and then 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. On the tbl6 table, create a columnstore index named idx1_tbl6_cg and redundantly store the data of the age column in the index table.

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

Use ALTER TABLE to create a columnstore index

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

The syntax is as follows:

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

The parameters are described as follows:

• table_name: the name of the table on which the index is to be created.

• UNIQUE: specifies to create a unique index. This parameter is optional. You must add this keyword when you create a unique index.

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

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

• column_name: the name of the column to be indexed. If you specify multiple columns, separate the columns with commas (,).

• STORING(column_name, ...): the redundant columns in the index table. This parameter is optional. Even though the column_name column is not an index column, it is redundantly stored in the columnar storage format.

• WITH COLUMN GROUP([all columns, ]each column): the columnar storage options for the index.

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

Here is an example:

Create a table named tbl7, and then 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. On the tbl7 table, create a columnstore index named idx1_tbl7_cg and redundantly store the 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-valued index

You can create a multi-valued index when you create a table (CREATE TABLE) in OceanBase Database. Currently, you cannot add a multi-valued index to a table after it is created. However, you can drop an existing index by using the ALTER TABLE statement. You can perform complex DML operations on a primary table that contains a multi-valued index.

Limitations

• A multi-valued index can have only one multi-valued key part. However, the CAST(... AS ... ARRAY) expression can reference multiple arrays in a JSON document. A sample expression is as follows:

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

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

• Multi-valued indexes cannot be covering indexes.

• Multi-valued key parts support only JSON expressions. The expressions do not necessarily reference existing elements in a JSON document inserted into an indexed column, but must be valid in syntax.

• Indexes recorded in a clustered index are scattered in a multi-valued index. Therefore, multi-valued indexes do not support range scanning or index-only scanning.

• You cannot use multi-valued indexes on foreign keys.

• You cannot define prefixes for multi-valued indexes.

• You cannot define multi-valued indexes on binary data.

• For the support of multi-valued indexes in offline DDL, see Offline DDL.

• For OceanBase Database V4.3.5 BP3, creating columnstore multi-valued indexes is currently not supported.

Use the CREATE TABLE statement to create a multi-valued index

The simplified syntax is as follows:

Create a multi-valued 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 multi-valued 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 parameters are described as follows:

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

• column_name: the name of a column that is of a data type other than JSON in the table.

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

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

• UNIQUE: specifies to create a unique multi-valued index. This keyword is optional. The keys of a unique multi-valued index must be unique.

  Notice

  You can define a multi-valued index as a unique index. If you attempt to insert a value that already exists into a unique multi-valued index, the system returns an error indicating that the value already exists. If a multi-valued index contains duplicate values, the operation of defining the index as a unique index will fail.

• idx_json_name: the name of the multi-valued index to be created.

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

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

Here is an example:

1. Create a table named json_tbl1 with a multi-valued index named idx1_json_tbl1.

   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.

   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:

Create a table named json_tbl2 with a composite multi-valued index named idx1_json_tbl2.

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

You can perform the following complex DML operations on a primary table that contains a multi-valued index:

• 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 on multiple tables

  Perform the following steps to prepare the 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]}');
  

  The following example shows how to perform UPDATE operations:

  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;
  

  The following example shows how to perform DELETE operations:

  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;
  

• Updatable 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]}';
  

Create a multi-valued index after a table is created

To create a multi-valued index after a table is created, you must 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 multi-valued indexes that can be created after a table is created.

Here are examples of how to create multi-valued indexes after a table is created:

1. Create a table named json_tbl1 and create a (non-unique) multi-valued index named idx1_json_tbl1.

   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":"Jk002","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 multi-valued 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":"Jk002","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 multi-valued 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":"Jk002","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 multi-valued 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":"Jk002","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-valued indexes in queries

If the optimizer specifies any one of the following functions in the WHERE clause, a multi-valued index is used in the query:

• MEMBER OF()
• JSON_CONTAINS()
• JSON_OVERLAPS

Here are some examples:

• Use the MEMBER OF() function in a query.

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

  The return 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 for rows whose ipcode field value in the col3 column is 94507 and execute the EXPLAIN statement to query the execution plan.

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

  The return 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
  

• Use the JSON_CONTAINS() function in a query.

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

  The return 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
  

• Use the JSON_OVERLAPS() function in a query.

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

  The return 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 MySQL mode of OceanBase Database V4.3.3, 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 information about how to query data by using full-text indexes, see Full-text queries.

Limitations and considerations

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

• Full-text indexes are applicable only to columns of the CHAR, VARCHAR, or TEXT data type.
• OceanBase Database V4.3.3 supports only local full-text indexes.
• You cannot add the UNIQUE keyword when you create a full-text index.
• If you want to create a full-text index on multiple columns, make sure that the columns use the same character set.
• For the support of multi-valued indexes in offline DDL, see Offline DDL.
• For OceanBase Database V4.3.5 BP3, creating columnstore full-text indexes is not supported.

Use the CREATE TABLE statement to create a full-text index

By using the CREATE TABLE statement, a full-text index can be created for the table during its creation. 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 parameters are described as follows:

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

• column_name: the name of a column in the table.

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

• FULLTEXT: specifies to create a full-text index.

  Notice

  The current version only supports creating local full-text indexes.

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

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

• WITH PARSER tokenizer_option: Optional. Specifies the tokenizer for the full-text index. The values are as follows:

  • SPACE: Default value, indicating tokenization by spaces. The following attributes can be specified:

    Attribute	Value range
    min_token_size	[1, 16]
    max_token_size	[10, 84]

  • NGRAM: Indicates N-Gram (Chinese) tokenization. The following attributes can be specified:

    Attribute	Value range
    ngram_token_size	[1, 10]

  • NGRAM2: Splits text into continuous characters ranging from min_ngram_size to max_ngram_size.

    Attribute	Value range
    min_ngram_size	[1, 16]
    max_ngram_size	[1, 16]

    Note

    For OceanBase Database V4.3.5, support for the NGRAM2 tokenizer was introduced starting from V4.3.5 BP2.

  • BENG: Tokenization based on Beng (basic English). The following attributes can be specified:

    Attribute	Value range
    min_token_size	[1, 16]
    max_token_size	[10, 84]

  • IK: Indicates IK (Chinese) tokenization. Currently, only the utf-8 character set is supported. The following attributes can be specified:

    Attribute	Values
    ik_mode	smart max_word

    Note

    For OceanBase Database V4.3.5, support for the IK tokenizer was introduced starting from V4.3.5 BP1.

  You can use the TOKENIZE function to view the tokenization results of text using the specified tokenizer and JSON format parameters.

• PARSER_PROPERTIES[=](parser_properties_list): Optional. Specifies the properties of the tokenizer. The values are as follows:

  • min_token_size: Specifies the minimum token length. The default value is 3, and the range is 1 to 16.

  • max_token_size: Specifies the maximum token length. The default value is 84, and the range is 10 to 84.

  • ngram_token_size: Specifies the token length for the NGRAM tokenizer. This is only effective for the NGRAM tokenizer. The default value is 2, and the range is 1 to 10.

  • ik_mode: Specifies the tokenization mode for the IK tokenizer. The values are as follows:

    • smart: The default value. Indicates that the words in the dictionary will be used to improve tokenization accuracy. The boundaries of words in the dictionary will be prioritized, potentially reducing unnecessary extensions.
    • max_word: Indicates that words defined in the dictionary will be recognized but will not affect the maximal tokenization expansion. Even if a word is defined in the dictionary, the max_word mode will still attempt to split the text into more tokens.

  Note

  For OceanBase Database V4.3.5, support for specifying tokenizer properties (PARSER_PROPERTIES) was introduced starting from V4.3.5 BP1.

• LOCAL: Optional. Specifies the creation of a local index.

Here are some examples:

• Create the table tbl1 and simultaneously create the full-text index full_idx1_tbl1.

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

• Create the table tbl2 and simultaneously create the full-text index full_idx1_tbl2, specifying the tokenizer as NGRAM and setting tokenizer properties using PARSER_PROPERTIES.

  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));
  

Create a full-text index by using the CREATE FULLTEXT INDEX statement

You can execute the CREATE FULLTEXT INDEX statement to create a full-text index on 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 syntax of the CREATE INDEX statement, see CREATE INDEX.

The parameters are described as follows:

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

• table_name: the name of the table on which the index is to be created.

• column_name: the name of the column to be indexed. Separate multiple columns with commas.

• WITH PARSER tokenizer_option: Optional. Specifies the tokenizer for the full-text index. The values are as follows:

  • SPACE: The default value, indicating tokenization by spaces. The following attributes can be specified:

    Attribute	Value range
    min_token_size	[1, 16]
    max_token_size	[10, 84]

  • NGRAM: Indicates tokenization based on N-Gram (Chinese). The following attributes can be specified:

    Attribute	Value range
    ngram_token_size	[1, 10]

  • NGRAM2: Splits text into continuous characters ranging from min_ngram_size to max_ngram_size. The following attributes can be specified:

    Attribute	Value range
    min_ngram_size	[1, 16]
    max_ngram_size	[1, 16]

    Note

    For OceanBase Database V4.3.5, support for the NGRAM2 tokenizer was introduced starting from V4.3.5 BP2.

  • BENG: Tokenization based on Beng (basic English). The following attributes can be specified:

    Attribute	Value range
    min_token_size	[1, 16]
    max_token_size	[10, 84]

  • IK: Indicates tokenization based on IK (Chinese). Currently, only the utf-8 character set is supported. The following attributes can be specified:

    Attribute	Values
    ik_mode	smart max_word

    Note

    For OceanBase Database V4.3.5, support for the IK tokenizer was introduced starting from V4.3.5 BP1.

  You can use the TOKENIZE function to view the tokenization results of text using the specified tokenizer and JSON format parameters.

• PARSER_PROPERTIES[=](parser_properties_list): Optional. Specifies the properties of the tokenizer. The values are as follows:

  • min_token_size: Specifies the minimum token length. The default value is 3, and the range is 1 to 16.

  • max_token_size: Specifies the maximum token length. The default value is 84, and the range is 10 to 84.

  • ngram_token_size: Specifies the token length for the NGRAM tokenizer. This is only effective for the NGRAM tokenizer. The default value is 2, and the range is 1 to 10.

  • ik_mode: Specifies the tokenization mode for the IK tokenizer. The values are as follows:

    • smart: The default value. Indicates that the words in the dictionary are used to improve the accuracy of tokenization. The boundaries of words in the dictionary are prioritized, potentially reducing unnecessary extensions.
    • max_word: Indicates that words defined in the dictionary will be recognized but will not affect the maximal tokenization expansion. Even if a word is defined in the dictionary, the max_word mode will still attempt to split the text into more tokens.

  Note

  For OceanBase Database V4.3.5, support for specifying tokenizer properties (PARSER_PROPERTIES) was introduced starting from V4.3.5 BP1.

• LOCAL: Optional. Specifies the creation of a local index.

Here are some examples:

Create a table named tbl3 and then add a full-text index named ft_idx1_tbl3 to the table.

1. Create a table named tbl3.

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

2. Add a full-text index named ft_idx1_tbl3 to the tbl3 table and specify the NGRAM parser for the full-text index.

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

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

You can execute the 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 syntax of the ALTER TABLE statement, see ALTER TABLE.

The parameters are described as follows:

• table_name: the name of the table on which the index is to be created.

• INDEX | KEY: indicates that the INDEX or KEY keyword can be used in this statement. The default value is KEY.

• index_name: the name of the index to be created. If this parameter is not specified, the name of the index is the same as that of the specified column.

• column_name: the name of the column to be indexed. Separate multiple columns with commas.

• WITH PARSER tokenizer_option: Optional. Specifies the tokenizer for the full-text index. The values are as follows:

  • SPACE: The default value, indicating tokenization by spaces. The following attributes can be specified:

    Attribute	Value range
    min_token_size	[1, 16]
    max_token_size	[10, 84]

  • NGRAM: Indicates tokenization based on N-Gram (Chinese). The following attributes can be specified:

    Attribute	Value range
    ngram_token_size	[1, 10]

  • NGRAM2: Splits text into continuous characters ranging from min_ngram_size to max_ngram_size. The following attributes can be specified:

    Attribute	Value range
    min_ngram_size	[1, 16]
    max_ngram_size	[1, 16]

    Note

    For OceanBase Database V4.3.5, support for the NGRAM2 tokenizer was introduced starting from V4.3.5 BP2.

  • BENG: Tokenization based on Beng (basic English). The following attributes can be specified:

    Attribute	Value range
    min_token_size	[1, 16]
    max_token_size	[10, 84]

  • IK: Indicates tokenization based on IK (Chinese). Currently, only the utf-8 character set is supported. The following attributes can be specified:

    Attribute	Values
    ik_mode	smart max_word

    Note

    For OceanBase Database V4.3.5, support for the IK tokenizer was introduced starting from V4.3.5 BP1.

  You can use the TOKENIZE function to view the tokenization results of text using the specified tokenizer and JSON format parameters.

• PARSER_PROPERTIES[=](parser_properties_list): Optional. Specifies the properties of the tokenizer. The values are as follows:

  • min_token_size: Specifies the minimum token length. The default value is 3, and the range is 1 to 16.

  • max_token_size: Specifies the maximum token length. The default value is 84, and the range is 10 to 84.

  • ngram_token_size: Specifies the token length for the NGRAM tokenizer. This is only effective for the NGRAM tokenizer. The default value is 2, and the range is 1 to 10.

  • ik_mode: Specifies the tokenization mode for the IK tokenizer. The values are as follows:

    • smart: The default value. Indicates that the words in the dictionary are used to improve the accuracy of tokenization. The boundaries of words in the dictionary are prioritized, potentially reducing unnecessary extensions.
    • max_word: Indicates that words defined in the dictionary will be recognized but will not affect the maximal tokenization expansion. Even if a word is defined in the dictionary, the max_word mode will still attempt to split the text into more tokens.

  Note

  For OceanBase Database V4.3.5, support for specifying tokenizer properties (PARSER_PROPERTIES) was introduced starting from V4.3.5 BP1.

• LOCAL: Optional. Specifies the creation of a local index.

Here are some examples:

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

1. Create a table named tbl4.

   CREATE TABLE tbl4(col1 INT, col2 TEXT);
   

2. Add a full-text index named ft_idx1_tbl4 to the tbl4 table and specify the BENG parser for the full-text index.

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

References

For more information about indexes of OceanBase Database's MySQL-compatible mode, see the following topics:

• Overview of indexes

• Create an index on a partitioned table

• Query indexes

• Drop an index