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.
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 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;
To drop an index that is no longer needed, use the following command:
DROP INDEX <index name>
For example,
SQL> DROP INDEX foo;
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;