Index operations

This topic describes how to perform simple index operations in OceanBase Database in Oracle mode, including index creation, addition, viewing, deletion, and renaming.

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];

The following table describes the parameters.

• index_name: specifies the name of the index to be created.

• table_name: specifies the name of the table to which the index is to be added.

• sort_column_key: specifies the key of a sort column. You can specify multiple sort columns when you create an index. Separate multiple sort columns with commas (,).

• index_option: specifies the index options. You can specify multiple index options when you create an index. Separate multiple index options with spaces.

• partition_option: specifies the options for creating index partitions.

For more information about the syntax, see CREATE INDEX.

When you create an index on a table, the table can still be used for read and write operations.

Assume that a table named t1 exists in the database. The following example creates an index named index1 on the c1 column of the t1 table. The index is sorted in ascending order, and NULL values appear after non-NULL values in the sorted result.

obclient> CREATE INDEX index1 ON t1 (c1 ASC NULLS LAST);

View an index

You can query system views to view indexes. The following table lists the views that you can query to view indexes.

• USER_INDEXES: queries the indexes of all tables owned by the current user. For more information, see USER_INDEXES.

• ALL_INDEXES: queries the indexes of all tables accessible to the current user. For more information, see ALL_INDEXES.

• DBA_INDEXES: queries the indexes of all tables in the database. For more information, see DBA_INDEXES.

• ALL_IND_COLUMNS: queries the index column information of all tables accessible to the current user. For more information, see ALL_IND_COLUMNS.

• DBA_IND_COLUMNS: queries the index column information of all tables in the database. For more information, see DBA_IND_COLUMNS.

• USER_IND_COLUMNS: queries the index column information of all tables owned by the current user. For more information, see USER_IND_COLUMNS.

You can query different views based on your requirements. The following example queries the index information of 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 following table describes the output.

• INDEX_NAME: the name of the index.

• TABLE_NAME: the name of the table to which the index belongs.

• COLUMN_NAME: the name of the column to which the index belongs.

• COLUMN_POSITION: the position of the index column in the index.

• COLUMN_LENGTH: the length of the index column.

• CHAR_LENGTH: the character length of the index column.

• DESCEND: the sorting order of the index column.

Delete an index

The syntax for deleting an index is as follows:

DROP INDEX [schema.]index_name;

For more information about the syntax, see DROP INDEX.

When you delete an index on a table, the table can still be used for read and write operations.

Assume that a table named t1 exists in the database and an index named index1 exists on the t1 table. The following example deletes the index1 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 following table describes the parameters.

• index_name: specifies the name of the index to be renamed.

• new_name: specifies the new name of the index.

For more information about the syntax, see ALTER INDEX.

Assume that an index named index1 exists in the database. The following example renames the index1 index to index2.

obclient> ALTER INDEX index1 RENAME TO index2;

References

• Overview

• Create and manage indexes