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. At the same time, it can either be or a unique index or a normal index. A unique index on a partitioned table must include a partitioning key of the table.
For more information about creating an index on a partitioned table, see create an index on a partitioned table.
MySQL mode
In MySQL mode, you can use the following syntax to create an index:
CREATE [UNIQUE] INDEX index_name ON table_name ( column_list ) [LOCAL | GLOBAL] [ PARTITION BY column_list PARTITIONS N ] ;
In a MySQL tenant, two indexes on the same table cannot have the same name. You can use the SHOW INDEX statement to view the indexes on a table.
In MySQL mode, you can also use the ALTER TABLE statement to create indexes:
ALTER TABLE table_name
ADD INDEX|KEY index_name ( column_list ) ;
This statement allows you to create multiple indexes at a time. You can use either the INDEX or the KEY keyword.
Example: Creating a normal index on a non-partitioned table
obclient> CREATE TABLE test (c1 int primary key, c2 VARCHAR(10));
Query OK, 0 rows affected (0.20 sec)
obclient> CREATE INDEX idx ON test (c1, c2);
Query OK, 0 rows affected (0.59 sec)
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 | idx | 1 | c1 | A | NULL | NULL | NULL | | BTREE | available | | YES |
| test | 1 | idx | 2 | c2 | A | NULL | NULL | NULL | YES | BTREE | available | | YES |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
3 rows in set (0.00 sec)
Oracle mode
In Oracle mode, use the following syntax to create an index:
CREATE [UNIQUE] INDEX index_name ON table_name ( column_list ) [LOCAL | GLOBAL] [ PARTITION BY column_list PARTITIONS N ] ;
Two indexes in the same Oracle tenant cannot have the same name.
Example: Creating a normal index on a non-partitioned table
obclient> CREATE INDEX t1_name_ind ON t1(name);
Query OK, 0 rows affected (0.43 sec)
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 (0.02 sec)