可以在表的一个或多个列上创建索引以加速表上的 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)
  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. +--------------------------+------------+-------------+------------+------------+
  9. 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)
  11. 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)
  3. obclient> create unique index t3_uk on t3(name) local;
  4. ERROR-00600: internal error code, arguments: -5261, A UNIQUE INDEX must include all columns in the table's partitioning function
  5. obclient> create unique index t3_uk on t3(name, id) local;
  6. Query OK, 0 rows affected (2.32 sec)
  7. obclient> create index t3_ind2 on t3(gmt_create) global ;
  8. Query OK, 0 rows affected (12.48 sec)
  9. obclient> SELECT index_name,index_type,table_owner,table_name,uniqueness,partitioned FROM user_indexes where table_name='T3';
  10. +--------------------------+------------+-------------+------------+------------+-------------+
  11. | INDEX_NAME | INDEX_TYPE | TABLE_OWNER | TABLE_NAME | UNIQUENESS | PARTITIONED |
  12. +--------------------------+------------+-------------+------------+------------+-------------+
  13. | T3_OBPK_1585822748793678 | NORMAL | TPCC | T3 | UNIQUE | NO |
  14. | T3_UK | NORMAL | TPCC | T3 | UNIQUE | YES |
  15. | T3_IND2 | NORMAL | TPCC | T3 | NONUNIQUE | NO |
  16. +--------------------------+------------+-------------+------------+------------+-------------+
  17. 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)