CREATE INDEX|V4.2.0| docs|Distributed Database

CREATE INDEX

Last Updated：2023-10-31 11:17:11 Updated

Purpose

You can use this statement to create an index. An index is a database structure created for a table to sort data in one or more columns of the table in a specific order. It improves the query speed and reduces the performance overhead of database systems.

The current version of OceanBase Database supports unique indexes and non-unique indexes. When you create an index, you can use the STORING(column_name,...) clause to specify redundant columns in the index table. The redundant columns of an index table include the columns that you specified and the primary key columns if the table has a primary key or hidden primary key columns if the table does not have a primary key.

Note

In MySQL mode of OceanBase Database, if you drop all index columns in a table, the created index becomes invalid.

Syntax

CREATE [UNIQUE] INDEX index_name
      ON table_name
      (key_part,...) [STORING (column_name,...)] [index_type] [index_options]

index_type:
      USING BTREE

index_options:
      index_option [index_option...]

index_option:
    GLOBAL | LOCAL
    | COMMENT 'string'
    | BLOCK_SIZE [=] size
    | STORING(column_name_list)
    | VISIBLE | INVISIBLE

key_part:
    {index_col_name [(length)] | (expr)} [ASC | DESC]

column_name_list:
    column_name [, column_name...]

Parameters

Parameter	Description
index_name	The name of the index to be created.
table_name	The name of the table to which the index belongs.
key_part	Creates a normal or function-based index.
index_col_name	The column name of the index. You can add `ASC` (ascending order) to the end of each column name. `DESC` (descending order) is not supported. By default, the columns are sorted in ascending order. Index-based sorting method: Data is first sorted by the values in the first column of `index_col_name` and by the values in the next column for the records with the same values in the first column.
expr	A valid function-based index expression. A Boolean expression, such as `c1=c1`, is allowed. Notice Currently, you cannot create function-based indexes on generated columns in OceanBase Database. For more information about the expressions supported by function-based indexes, see System functions supported by function-based indexes.
column_name	The name of the column used to create the index.
length	For a string column, you can use the `col_name(length)` syntax to extract part of the string for creating an index. Supported data types: `CHAR`, `VARCHAR`, `BINARY`, and `VARBINARY`.
index_type	The index type. Only `USING BTREE` is supported for you to create B-tree indexes.
UNIQUE	Specifies to create a unique index.
index_option	The index options. Multiple index options are separated with spaces.
GLOBAL \| LOCAL	Specifies whether the index is a global index or a local index. Default value: `LOCAL`.
COMMENT	The comment.
BLOCK_SIZE	The microblock size.
STORING	Specifies to store copies of some columns in the index table to improve the query performance of the system.

Examples

Create a table named test, create an index named test_index on the c1 and c2 columns of the table, and query the indexes on the test table. Key_name in two records in the results is test_index. Columns c1 and c2 are index columns, and column c2 is a sub_part of test_index.

obclient>  CREATE TABLE test (c1 int primary key, c2 VARCHAR(10));
Query OK, 0 rows affected

obclient> CREATE INDEX test_index ON test (c1, c2 ASC);
Query OK, 0 rows affected

obclient> SHOW INDEX FROM test;
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
| Table | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment   | Index_comment | Visible |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
| test  |          0 | PRIMARY    |            1 | c1          | A         |        NULL | NULL     | NULL   |      | BTREE      | available |               | YES     |
| test  |          1 | test_index |            1 | c1          | A         |        NULL | NULL     | NULL   |      | BTREE      | available |               | YES     |
| test  |          1 | test_index |            2 | c2          | A         |        NULL | NULL     | NULL   | YES  | BTREE      | available |               | YES     |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
3 rows in set

Create a global index.

obclient> CREATE TABLE tbl1(col1 INT PRIMARY KEY, col2 INT, col3 INT, INDEX IDX(col2) GLOBAL);
Query OK, 0 row affected

Create a unique index on the tbl2 table.

/* Create a table named tbl2. */
obclient> CREATE TABLE tbl2 (col1 INT, col2 INT, col3 INT, PRIMARY KEY(col1));
Query OK, 0 rows affected

/* Create a unique index on the tbl2 table. */
obclient> CREATE UNIQUE INDEX tbl2_idx2 ON tbl2(col2) STORING(col3);
Query OK, 0 rows affected

/* View the unique index on the tbl2 table. */
obclient> SHOW INDEX FROM tbl2 \G
*************************** 1. row ***************************
        Table: tbl2
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: col1
    Collation: A
  Cardinality: NULL
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment: available
Index_comment:
      Visible: YES
*************************** 2. row ***************************
        Table: tbl2
   Non_unique: 0
     Key_name: tbl2_idx2
 Seq_in_index: 1
  Column_name: col2
    Collation: A
  Cardinality: NULL
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: available
Index_comment:
      Visible: YES
2 rows in set

Create an index named i1 based on the c1+c2 < 1 function on the t1_func table.

obclient> CREATE TABLE t1_func(c1 INT, c2 INT);
Query OK, 0 rows affected

obclient> CREATE INDEX i1 ON t1_func((c1+c2 < 1));
Query OK, 0 rows affected