CREATE INDEX

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.

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

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