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

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

  1. obclient> create table t1(id number not null primary key, name varchar2(50));
  2. Query OK, 0 rows affected (0.05 sec)
  4. obclient> SELECT index_name,index_type,table_owner,table_name,uniqueness FROM user_indexes WHERE table_name='T1';;
  5. +--------------------------+------------+-------------+------------+------------+
  6. | INDEX_NAME               | INDEX_TYPE | TABLE_OWNER | TABLE_NAME | UNIQUENESS |
  7. +--------------------------+------------+-------------+------------+------------+
  8. | T1_OBPK_1585822641424088 | NORMAL     | TPCC        | T1         | UNIQUE     |
  9. +--------------------------+------------+-------------+------------+------------+
  10. 1 row in set (0.02 sec)

新增索引

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

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

  1. 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。

  • 示例：为普通表创建普通索引
  1. obclient> create index t1_name_ind on t1(name);
  2. Query OK, 0 rows affected (0.43 sec)
  3. obclient> SELECT index_name,index_type,table_owner,table_name,uniqueness FROM user_indexes where table_name='T1';
  4. +--------------------------+------------+-------------+------------+------------+
  5. | INDEX_NAME               | INDEX_TYPE | TABLE_OWNER | TABLE_NAME | UNIQUENESS |
  6. +--------------------------+------------+-------------+------------+------------+
  7. | T1_OBPK_1585822641424088 | NORMAL     | TPCC        | T1         | UNIQUE     |
  8. | T1_NAME_IND              | NORMAL     | TPCC        | T1         | NONUNIQUE  |
  9. +--------------------------+------------+-------------+------------+------------+
  10. 2 rows in set (0.02 sec)
  12. obclient>

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

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

  1. 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;
  2. Query OK, 0 rows affected (0.09 sec)
  4. obclient> create unique index t3_uk on t3(name) local;
  5. ERROR-00600: internal error code, arguments: -5261, A UNIQUE INDEX must include all columns in the table's partitioning function
  7. obclient> create unique index t3_uk on t3(name, id) local;
  8. Query OK, 0 rows affected (2.32 sec)
  10. obclient> create index t3_ind2 on t3(gmt_create) global ;
  11. Query OK, 0 rows affected (12.48 sec)
  13. obclient> SELECT index_name,index_type,table_owner,table_name,uniqueness,partitioned FROM user_indexes where table_name='T3';
  14. +--------------------------+------------+-------------+------------+------------+-------------+
  15. | INDEX_NAME               | INDEX_TYPE | TABLE_OWNER | TABLE_NAME | UNIQUENESS | PARTITIONED |
  16. +--------------------------+------------+-------------+------------+------------+-------------+
  17. | T3_OBPK_1585822748793678 | NORMAL     | TPCC        | T3         | UNIQUE     | NO          |
  18. | T3_UK                    | NORMAL     | TPCC        | T3         | UNIQUE     | YES         |
  19. | T3_IND2                  | NORMAL     | TPCC        | T3         | NONUNIQUE  | NO          |
  20. +--------------------------+------------+-------------+------------+------------+-------------+
  21. 3 rows in set (0.03 sec)

删除索引

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

  1. DROP INDEX index_name ;
  • 示例：删除表的索引
  1. obclient> drop index t3_ind2;
  2. Query OK, 0 rows affected (0.02 sec)