Managing Indexes

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:

Single column and composite indexes. Composite index is built on multiple columns in a table whereas single column index has only one column . The maximum number of columns in a composite key is 32.
Unique and non-unique key. A unique index guarantees no two rows of a table have duplicate values.
Function-based indexes. Function-based indexes can be created as either a B-tree or a bitmap index. B-tree is the default index created by Oracle and bitmap indexes can be used when a table has millions of rows and there are few distinct values for the column to be indexed. For example, bitmap indexes may be more preferable for the gender and martial status column of a passport table.

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;