In the Oracle mode of OceanBase Database, you can create (or add), drop, and rename an index.
Create (or add) an index
The syntax for creating (or adding) an index is as follows:
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.
Here is an 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 the PARALLEL hint. The syntax for specifying a degree of parallelism (DOP) in PARALLEL is as follows:
CREATE /*+ PARALLEL(integer) */ INDEX index_name
ON table_name (index_col_name,...)
Here is an example:
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
Drop an index
The two syntaxes for dropping an index are as follows:
DROP INDEX [schema.]index_name;
When you drop an index from a table, this table still supports read and write operations.
Here is an example:
obclient> DROP INDEX tbl1_idx;
Query OK, 0 rows affected
Rename an index
The syntax for renaming an index is as follows:
ALTER INDEX [ schema. ]index_name RENAME TO new_name;
Here is an example:
obclient> ALTER INDEX tbl1_idx RENAME TO ind1;
Query OK, 0 rows affected