In Oracle mode of OceanBase Database, you can create (or add), drop, and rename an index.
Create (or add) an index
Syntax for creating (or adding) an index:
CREATE INDEX index_name ON table_name (index_col_name,...);
When you create an index on a table, this table still supports read and write operations.
Example:
obclient> CREATE TABLE tbl1 (c1 INT PRIMARY KEY, c2 VARCHAR(10));
Query OK, 0 rows affected
obclient> CREATE INDEX tbl1_idx ON tbl1 (c1, c2 ASC);
Query OK, 0 rows affected
When you create an index in OceanBase Database, you can use parallel hints by specifying a degree of parallelism (DOP) in PARALLEL. Syntax:
CREATE /*+ PARALLEL(integer) */ INDEX index_name
ON table_name (index_col_name,...)
Sample code:
obclient> CREATE TABLE tbl1 (col1 INT PRIMARY KEY, col2 VARCHAR(10));
Query OK, 0 rows affected
obclient> CREATE /*+ PARALLEL(10) */ INDEX IDX ON tbl1(col1, col2);
Query OK, 0 rows affected
Syntaxes:
DROP INDEX [schema.]index_name;
When you drop an index from a table, this table still supports read and write operations.
Sample code:
obclient> DROP INDEX tbl1_idx;
Query OK, 0 rows affected
RENAME INDEX
Syntax:
ALTER INDEX [ schema. ]index_name RENAME TO new_name;
Sample code:
obclient> ALTER INDEX tbl1_idx RENAME TO ind1;
Query OK, 0 rows affected