1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Indexes in Oracle

Discussion in 'SQL PL/SQL' started by SBH, Dec 1, 2010.

  1. SBH

    SBH Forum Expert

    Likes Received:
    Trophy Points:
    1. Overview

    Database performance is one of the primary objectives of database administration. Code performance is degraded when the processing engine makes more I/O on the disk or multiple context switching. One important technique to reduce the performance overhead is Indexing. We shall discuss about Indexes, their types and other important aspects.

    2. Introduction

    Indexes are the database objects which enhance the performance of a SQL query. It is created on the table column(s). Their way of functioning is similar to that of directory index which access path to a NAME differs from one name to other.
    Database Index provides access algorithms for faster access data from the tables. If the indexed column is used in any of the WHERE clause conditions, then the index against the column would be used to reduce the disk I/O by traversing the indexed path scan and finding the data. This enhances the query performance which would have been reduced in case of full table san.


    Code (Text):
    ON table (column [, column]...);
    UNIQUE and BITMAP must be specified only for unique and bitmap indexes. By default, Oracle creates B-Tree indexes for normal indexes.


    1. The SQL below creates B-Tree Index on HIREDATE column of EMPLOYEE table

    Code (SQL):

    INDEX created.
    2. The SQL below creates Unique Index on EMPNO column of EMPLOYEE table

    Code (SQL):


    INDEX created.
    3. The SQL below creates Function Based Index on UPPER(ENAME) column of EMPLOYEE table

    Code (SQL):

    INDEX created.
    3. Notes

    1. Index creation creates an Index segment, which stores the values for the indexed column. Upon reference of the column in the query, oracle server takes the value from the index segment for comparison.
    2. If a table contains a primary key or unique constrained column, unique index is automatically created during creation of constraints.
    3. The index creator must possess CREATE [ANY] INDEX privilege.
    4. Oracle server is intelligent enough to decide upon the situation whether to use Indexed Path or Normal path. If query fetches result 2-5% of total count of rows in the table, then Oracle goes on to make use of Indexes.
    5. Composite Index can be created on more than one column of a table. In such cases, even if one of the columns is used in the query condition, the index gets used.
    6. Appropriate column identification to be indexed is based on its usability in the SQL query, range of values in it, null values.
    7. Prior to Oracle 9i versions, function based indexes were enabled by setting QUERY_REWRITE_ENABLED and QUERY_REWRITE_INTEGRITY parameters as TRUE and TRUSTED respectively.

    4. Types of Indexes

    1. B-Tree index – The default index created by Oracle is preferrable for high cardinality columns. It helps to mobilize the search operation based on its tree and node like orientation where every leaf node comprises of rowid. Access algorithm refers to the most optimum path traversing along these branches to reach a node (data). Note that NULL values of a columns are not included in B-Tree Indexes.
    2. Bitmap index – For low cardinality columns, oracle provides Bitmap Indexes. Column having bit like values like ‘Y’ or ‘N’, ‘0’ or ‘1’, or ‘O’ or ‘N’ are best candidates for Bitmap indexing. They store the rowid with key value as bitmap.
    3. Function Based index – Oracle restricts the use of Index on a column if it is used with a function. To overcome this abstract behaviour, oracle provides Function based Index. They can be created on columns using manipulative functions like UPPER(COLUMN), NVL(COL, VAL) etc.

    Besides the above classification, there are other type of indexes like Cluster indexes (for clustered applications), Global and Local Indexes (for partitioned tables), and Reverse Key Indexes (used in RAC applications).

    5. Removing an Index

    Using DROP command, users can possess DROP ANY USER privilege.