You can create an index on one or more columns of a table to speed up SQL statements on the table. Well-designed indexes can reduce physical or logical I/O operations.
If you specify a primary key when you create a table, OceanBase Database automatically creates a UNIQUE index. Example:
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 an index
You can use the CREATE INDEX statement to create an index on a table. OceanBase enables you to create indexes on partitioned and non-partitioned tables. Indexes are classified into local and global indexes. An index can either be a UNIQUE or a normal index. A UNIQUE index on a partitioned table must contain the partition key of the partition.
SQL syntax for creating an index:
CREATE [UNIQUE] INDEX index_name ON table_name ( column_list ) [LOCAL | GLOBAL] [ PARTITION BY column_list PARTITIONS N ];
In an Oracle tenant, index names are unique across a table. In an Oracle tenant, you view indexes in system views, such as USER_INDEXES, ALL_INDEXES, and DBA_INDEXES.
- Example: Creating a normal index on a 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)
Example: Creating a local UNIQUE index and a global normal index for a partitioned table
The UNIQUE index on a partitioned table must contain the partition key of the table. A partitioned table with a global index must contain a primary key.
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)
Delete an index
Syntax for deleting an index:
DROP INDEX index_name;
- Example: Deleting an index on a table
obclient> drop index t3_ind2;
Query OK, 0 rows affected (0.02 sec)
