Create indexes

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

OceanBase Database allows you to create an index on both partitioned and non-partitioned tables. An index can be either a local index or a global index. In addition, the index can either be a unique index or a normal index. A local unique index on a partitioned table must include a partitioning key of the table.

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.

SQL syntax for creating an index:

CREATE [UNIQUE] INDEX index_name ON table_name ( column_list ) [LOCAL | GLOBAL] [ PARTITION BY column_list PARTITIONS N ];

Note

Index names must be unique within a tenant.

Parameters:

• index_name: the index name.

• UNIQUE: specifies that the index to be created is a unique index.

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

• column_list: the one or more columns to be indexed. Separate multiple columns with commas (,).

• LOCAL: specifies that the index to be created is a local index.

• GLOBAL: specifies that the index to be created is a global index. If you do not specify GLOBAL or LOCAL when you create an index, the GLOBAL keyword takes effect by default.

• PARTITION BY column_list PARTITIONS N: the partitioning method.

Create a normal index on a non-partitioned table. Example:

obclient> CREATE INDEX t1_name_ind ON t1(name);
Query OK, 0 rows affected

obclient> SELECT index_name,index_type,table_owner,table_name,uniqueness FROM user_indexes WHERE table_name='T1';
+--------------------------+------------+-------------+------------+------------+
| INDEX_NAME               | INDEX_TYPE | TABLE_OWNER | TABLE_NAME | UNIQUENESS |
+--------------------------+------------+-------------+------------+------------+
| T1_OBPK_1585822641424088 | NORMAL     | TPCC        | T1         | UNIQUE     |
| T1_NAME_IND              | NORMAL     | TPCC        | T1         | NONUNIQUE  |
+--------------------------+------------+-------------+------------+------------+
2 rows in set