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 or add 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];
The parameters are described as follows:
index_name: the name of the index to be created.table_name: the name of the table on which the index is to be created.sort_column_key: the column used as the sort key. You can specify multiple columns and separate them with commas (,).index_option: the index option. You can specify multiple index options and separate them with spaces.partition_option: 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 t1 table exists in the database. The following sample statement creates an index named index1 on the t1 table, with the index sorted in ascending order on the c1 column and NULL values sorted after non-NULL values: Here is an example:
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.
DBA_IND_COLUMNS: displays the columns of indexes on all tables in the database.
USER_IND_COLUMNS: displays the details of indexes on a table.
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 columns in the result are described as follows:
INDEX_NAME: the name of the index.TABLE_NAME: the name of the table where the index resides.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 t1 table 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;
The parameters are described as follows:
index_name: the original name of the index to be renamed.new_name: 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;