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.

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 table.

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 table_name(column_name column_definition,[column_name column_definition,...] [UNIQUE] INDEX|KEY [index_name](column_name));

where:

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

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

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

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

• INDEX|KEY indicates that you can use either the INDEX or the 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 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);

where:

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

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

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

• column_name specifies 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);

where:

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

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

• INDEX|KEY indicates that you can use either the INDEX or the 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 a value for this parameter, the index name is the same as the column name by default.

• column_name specifies 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 table tbl3.

   ALTER TABLE tbl3 ADD INDEX tbl3_idx1(id);
   

Create a spatial index

The MySQL mode of OceanBase Database 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 the 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

When you create a spatial index, note that:

• 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.

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

where:

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

• column_g_name or column_name specifies the name of the column in the table.

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

• column_definition specifies 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.

• INDEX|KEY indicates that you can use either the INDEX or the KEY keyword in the statement.

• index_name specifies 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);

where:

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

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

• column_g_name specifies 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.

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

where:

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

• INDEX|KEY indicates that you can use either the INDEX or the 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 a value for this parameter, the index name is the same as the column name by default.

• column_g_name specifies 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.

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 this 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 created based on the result of any function applied to a column or multiple columns. Function-based indexing is an optimization technique. You can use function-based indexes to quickly locate function values that match query conditions. This avoids repeated calculation and improves query efficiency.

Assume that you have a table named sale and that the table has a column named date_col of the date type.

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

If you want to obtain the data of March, you can use the Month() function.

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

In this scenario, the database must calculate the month(date_col) value for each row in the table and filter out rows that do not meet the condition month(date_col) = 3. If you need to frequently filter data by month and the month(date_col) values need to be calculated each time, the overhead is high. In this case, you can create a function-based index on the date_col column to store the month(date_col) values in an index table to accelerate 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 be a unique or spatial index.

Limitations

The MySQL mode of OceanBase Database imposes limitations on the expressions of function-based indexes. Specifically, the expressions of some system functions cannot be used as function-based indexes. For more information, see System functions supported for function-based indexes and System functions not supported for function-based indexes.

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

You can create a function-based index on a table when you create the table. The syntax is as follows:

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

where:

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

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

• [UNIQUE | SPATIAL] specifies an optional keyword. Here, UNIQUE indicates a unique index, and SPATIAL indicates a spatial index. When you create a unique index or a spatial index, you must add the corresponding keyword.

• INDEX|KEY indicates that you can use either the INDEX or the KEY keyword in the statement.

• index_name specifies the name of the function-based index to be created. This parameter is optional. If this parameter is not specified, the system automatically generates a name in the functional_index_xx format, in which xx is the index ID.

• expr specifies the expression of the function-based index. It can 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)));

Execute the following SHOW CREATE TABLE statement to view the table creation 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,
  `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

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

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

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

where:

• [UNIQUE | SPATIAL] specifies an optional keyword. Here, UNIQUE indicates a unique index, and SPATIAL indicates a spatial index. When you create a unique index or a spatial index, you must add the corresponding keyword.

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

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

• expr specifies the expression of the function-based index. It can be a Boolean expression, such as c1=c1.

Example: Create a function-based index named tbl2_func_idx1 with an expression 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 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);

where:

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

• [UNIQUE | SPATIAL] specifies an optional keyword. Here, UNIQUE indicates a unique index, and SPATIAL indicates a spatial index. When you create a unique index or a spatial index, you must add the corresponding keyword.

• INDEX|KEY indicates that you can use either the INDEX or the KEY keyword in the statement.

• index_name specifies the name of the function-based index to be created. This parameter is optional. If you do not specify a value for this parameter, the system automatically generates an index name in the functional_index_xx format, in which xx is the index ID.

• expr specifies the expression of the function-based index. It can 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-based 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 CREATE TABLE to create a columnstore index

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

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

where:

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

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

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

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

• INDEX|KEY indicates that you can use either the INDEX or the 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 a value for this parameter, the index name is the same as the column name by default.

• STORING(column_name, ...) specifies 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 format.

• WITH COLUMN GROUP([all columns, ]each column) specifies the columnstore attribute of 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 two 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 CREATE INDEX 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);

where:

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

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

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

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

• STORING(column_name, ...) specifies 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 format.

• WITH COLUMN GROUP([all columns, ]each column) specifies the columnstore attribute of 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 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);

where:

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

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

• INDEX|KEY indicates that you can use either the INDEX or the 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 a value for this parameter, the index name is the same as the column name by default.

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

• STORING(column_name, ...) specifies 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 format.

• WITH COLUMN GROUP([all columns, ]each column) specifies the columnstore attribute of 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_tbl7_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

In OceanBase Database V4.3.1, you can create a multi-valued index only when you execute the CREATE TABLE statement to create a table. After a table is created, you cannot create a multi-valued index on the table but can execute the ALTER TABLE statement to drop an existing 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.

• Indexes with multi-valued key parts do not support sorting and therefore, cannot be used as primary keys. For the same reason, multi-valued indexes cannot be defined with the ASC or DESC keyword.

• 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.

• You cannot create multi-valued indexes online. You must specify ALGORITHM = COPY when you create a multi-valued index.

• Multi-valued indexes support only the following two combinations of character sets and collations:

  • Binary character set and its default binary collation
  • utf8mb4 character set and its default utf8mb4_0900_as_cs collation

• You cannot use the USING HASH clause when you create multi-valued indexes.

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

where:

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

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

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

• json_column_name specifies the name of a column of the JSON data type.

• 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 in 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 specifies the name of the multi-valued index to be created.

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

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

Example 1:

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

Example 2:

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

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 several 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 the MySQL mode of OceanBase Database V4.3.1, you can use the CREATE 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 full-text indexes on non-partitioned tables with or without primary keys. For partitioned tables:

  • You can create full-text indexes on partitioned tables with primary keys.
  • You cannot create full-text indexes on partitioned tables without primary keys.

• Full-text indexes are applicable only to columns of the CHAR, VARCHAR, or TEXT data type.

• OceanBase Database 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.

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

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 {SPACE | NGRAM | BENG}] [LOCAL];

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

where:

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

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

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

• FULLTEXT specifies to create a full-text index.

  Notice

  • OceanBase Database V4.3.1 supports only local full-text indexes.
  • The full-text indexing feature is still under experiment. We recommend that you do not use it in a production environment, to avoid impact on system stability.

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

• index_name specifies 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 {SPACE | NGRAM | BENG} specifies the parser for the full-text index. This keyword is optional.

  OceanBase Database V4.3.1 supports three built-in parsers: SPACE, NGRAM, and BENG. If you do not specify this keyword, the SPACE parser is used by default.

• LOCAL specifies to create a local index. This keyword is optional.

  Note

  If you do not specify the LOCAL or GLOBAL keyword when you create an index, a local index is created on partitioned tables by default.

Here are two examples:

• Create a table named tbl1 with a full-text index named full_idx1_tbl1.

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

• Create a table named tbl2 with a full-text index named full_idx1_tbl2, and specify NGRAM as the parser for the full-text index.

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

References

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

• Overview

• Create an index on a partitioned table

• Query indexes

• Drop an index