可以在表的一个或多个列上创建索引以加速表上的 SQL 语句执行速度。索引使用正确的话，可以减少物理 IO 或者逻辑 IO。

如果创建表时同时设置了主键，OceanBase 数据库会默认创建一个唯一索引。以下面的 SQL 为例：

obclient> create table t1(id number not null primary key, name varchar2(50));
Query OK, 0 rows affected (0.05 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     |
+--------------------------+------------+-------------+------------+------------+
1 row in set (0.02 sec)

新增索引

为表增加索引可以通过 CREATE INDEX 语句。OceanBase 数据库能在普通表和分区表上创建索引，索引可以是本地索引或者全局索引。同时索引可以是唯一索引或者普通索引，如果是分区表的唯一索引，唯一索引必须包含表分区的拆分键。

创建索引的 SQL 语法格式如下：

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

Oracle 租户里，索引名称在租户范围内不能重复。索引可以从系统视图里查看，Oracle 租户查看索引的视图有：USER_INDEXES、ALL_INDEXES、DBA_INDEXES。

• 示例：为普通表创建普通索引

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)

• 示例：为分区表创建唯一的本地索引、普通的全局索引

  分区表的唯一索引必须包含分区表的分区键，分区表的全局索引要求分区表必须有主键。

obclient> create table t3(id number not null primary key, name varchar2(50), gmt_create date not null default sysdate) partition by hash(id) partitions 8;
Query OK, 0 rows affected (0.09 sec)
obclient> create unique index t3_uk on t3(name) local;
ERROR-00600: internal error code, arguments: -5261, A UNIQUE INDEX must include all columns in the table's partitioning function
obclient> create unique index t3_uk on t3(name, id) local;
Query OK, 0 rows affected (2.32 sec)
obclient> create index t3_ind2 on t3(gmt_create) global ;
Query OK, 0 rows affected (12.48 sec)
obclient> SELECT index_name,index_type,table_owner,table_name,uniqueness,partitioned FROM user_indexes where table_name='T3';
+--------------------------+------------+-------------+------------+------------+-------------+
| INDEX_NAME               | INDEX_TYPE | TABLE_OWNER | TABLE_NAME | UNIQUENESS | PARTITIONED |
+--------------------------+------------+-------------+------------+------------+-------------+
| T3_OBPK_1585822748793678 | NORMAL     | TPCC        | T3         | UNIQUE     | NO          |
| T3_UK                    | NORMAL     | TPCC        | T3         | UNIQUE     | YES         |
| T3_IND2                  | NORMAL     | TPCC        | T3         | NONUNIQUE  | NO          |
+--------------------------+------------+-------------+------------+------------+-------------+
3 rows in set (0.03 sec)

删除索引

删除索引的语法格式如下：

DROP INDEX index_name ;

• 示例：删除表的索引

obclient> drop index t3_ind2;
Query OK, 0 rows affected (0.02 sec)