如果创建表时同时设置了主键,OceanBase 数据库会默认创建一个唯一索引。以下面的 SQL 为例:
创建索引的SQL语法格式如下:
- 示例:为普通表创建普通索引
- 示例:为分区表创建唯一的本地索引、普通的全局索引
分区表的唯一索引必须包含分区表的分区键,分区表的全局索引要求分区表必须有主键。
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;
obclient> create unique index t3_uk on t3(name, id) local;
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)
删除索引
- 示例:删除表的索引
Query OK, 0 rows affected (0.02 sec)