This topic describes how to perform simple index operations in Oracle mode of OceanBase Database, including creating an index, querying indexes, dropping an index, and renaming an index.
Create an index
The syntax for creating an index is as follows:
CREATE [UNIQUE] INDEX index_name
[USING BTREE] ON table_name (sort_column_key [, sort_column_key...])
[index_option...] [partition_option];
where
index_namespecifies the name of the index to be created.table_namespecifies the name of the table on which the index is to be created.sort_column_keyspecifies the column as the sort key. You can specify multiple columns and separate them with commas (,).index_optionspecifies the index option. You can specify multiple index options and separate them with spaces.partition_optionspecifies the index partitioning option.
For more information about the syntax, see CREATE INDEX.
When an index is being created on a table, this table still supports read and write operations.
Assume that the table t1 exists in the database. The following sample statement creates an index named index1 on the table t1 with the index in ascending order on the c1 column, and sorts NULL values after non-NULL values:
obclient> CREATE INDEX index1 ON t1 (c1 ASC NULLS LAST);
Query indexes
You can query indexes in the following system views:
USER_INDEXES: displays indexes on all tables owned by the current user. For more information, see USER_INDEXES.
ALL_INDEXES: displays all indexes on a table. For more information, see ALL_INDEXES.
DBA_INDEXES: displays indexes on all tables in the database. For more information, see DBA_INDEXES.
ALL_IND_COLUMNS: displays the columns of indexes on all tables accessible to the current user. For more information, see ALL_IND_COLUMNS.
DBA_IND_COLUMNS: displays the columns of indexes on all tables in the database. For more information, see DBA_IND_COLUMNS.
USER_IND_COLUMNS: displays the details of indexes on a table. For more information, see USER_IND_COLUMNS.
You can query different views as needed. The following sample statement queries the details of indexes on the t1 table:
obclient> SELECT * FROM USER_IND_COLUMNS WHERE table_name='T1';
The output is as follows:
+--------------------------+------------+-------------+-----------------+---------------+-------------+---------+--------------------+
| INDEX_NAME | TABLE_NAME | COLUMN_NAME | COLUMN_POSITION | COLUMN_LENGTH | CHAR_LENGTH | DESCEND | COLLATED_COLUMN_ID |
+--------------------------+------------+-------------+-----------------+---------------+-------------+---------+--------------------+
| INDEX1 | T1 | C1 | 1 | 50 | 50 | ASC | NULL |
+--------------------------+------------+-------------+-----------------+---------------+-------------+---------+--------------------+
The fields in the output are described as follows:
INDEX_NAME: the name of the index.TABLE_NAME: the name of the table containing the index.COLUMN_NAME: the column that is indexed.COLUMN_POSITION: the position of the indexed column in the index.COLUMN_LENGTH: the length of the indexed column.CHAR_LENGTH: the character length of the indexed column.DESCEND: the sorting method of the indexed column.
Drop an index
The syntax for dropping an index is as follows:
DROP INDEX [schema.]index_name;
For more information about the syntax, see DROP INDEX.
When you drop an index from a table, this table still supports read and write operations.
Assume that the table t1 exists in the database and has an index named index1. The following sample statement drops the index:
obclient> DROP INDEX index1;
Rename an index
The syntax for renaming an index is as follows:
ALTER INDEX [ schema.]index_name RENAME TO new_name;
where:
index_namespecifies the original name of the index to be renamed.new_namespecifies the new name of the index.
For more information about the syntax, see ALTER INDEX.
The following sample statement renames index1 in the database as index2:
obclient> ALTER INDEX index1 RENAME TO index2;