Index operations

An index is a database structure created for a table to sort data in one or more columns of the table in a specific order. It mainly improves the query speed and reduces the performance overhead of database systems.

Create an index

Use the CREATE INDEX statement to create a table index.

Example

1. Run the following command to create Table test:

   obclient> CREATE TABLE test (c1 int primary key, c2 VARCHAR(10));
   

2. Run the following command to create an index for Table test:

   obclient> CREATE INDEX test_index ON test (c1, c2);
   

For more information on the syntax of the CREATE INDEX statement, see the "CREATE INDEX" topic in SQL Reference (Oracle Mode).

View an index

• View all indexes of a table.

  obclient> SELECT * FROM all_indexes WHERE table_name='TEST';
  

• View detailed information about table indexes.

  obclient> SELECT * FROM user_ind_columns WHERE table_name='TEST';
  

Drop an index

Use the DROP INDEX statement to drop a table index.

Example

Drop an index named test_index.

obclient> DROP INDEX test_index;

For more information on the syntax of the DROP INDEX statement, see the "DROP INDEX" topic in SQL Reference (Oracle Mode).