Managing Indexes

Overview

Creating Indexes

Rebuilding Indexes

Dropping Indexes

Obtaining Index Information


Overview

An index is a tree structure that allows direct access to a row in a table. Indexes can be classified  based on their logical design or physical implementation. For example, some classifications are:

Creating table index is for faster access of data. A cost-based index guideline is that if the total number of query results is less than 5% of the total number of rows in the table, then it may be worth of building an index.


Creating Index

The syntax of creating a B-tree index is

    CREATE [UNIQUE] INDEX <index name>  ON <table name> (<column name>, [column name]...)  ...

The syntax of creating a bitmap index is

    CREATE BITMAP INDEX <index name>  ON <table name> (<column name>, [column name]...) ...

There are other optional clauses after the ON clause which specify the detailed storage characteristics of an index. They are omitted here for simplicity.

Examples:

    SQL> CREATE INDEX info ON employee (empno);

    


Rebuilding Indexes

Rebuilding an index is needed if an index contains many deleted entries. For example, an index on the order number of an orders table should be rebuilt, when completed orders are deleted and new orders with higher numbers are added to the table.

The syntax of building index is:

    ALTER INDEX <index name> REBUILD ...

For example,

    SQL> ALTER INDEX order_no_index REBUILD;

Rebuilding indexes can be done with minimal table locking allowing concurrent operations on the base table with ONLINE clause.

For example,

    SQL> ALTER INDEX order_no_index REBUILD;


Dropping Indexes

To drop an index that is no longer needed, use the following command:

    DROP INDEX <index name>

For example,

    SQL> DROP INDEX foo;


Obtaining Index Information

To verify the name, type and status of the indexes, retrieve from DBA_INDEXES data dictionary.

For example,

    SQL> SELECT index_name, tablespace_name, index_type, uniqueness, status

         FROM dba_indexes

         WHERE owner='scott';

To show the tables and columns which the indexes are built, retrieve from DBA_IND_COLUMNS data dictionary.

    SQL> SELECT index_name, table_owner, table_name, column_name

         FROM dba_ind_columns

         WHERE index_owner='scott'

         ORDER BY index_name, column_position;