ORACLE INDEX

 

 

What is an Index?

 

To explain in plain English, an Index could be considered as a unique bookmark to a database cell (of those columns

which are indexed).

For each cell in an indexed column, there will be a unique identifer, which is known as the index. Indexes are stored in the Index segments in Oracle database (unless it is an Index Organized table (IOT) which we are not discussing here). In Oracle 9i, Indexes contain an entry for each cell value that appears in the indexed column(s) of the table or cluster.

 

So, when an index is used in a query, Oracle Engine first  searches through the indexes and reaches the index of the particluar cell value, it is searching for. Then it goes to the table and fetches only that particular value. Thus we could avoid Full table scans. Thus an index is a performance-tuning method which allows faster retrieval of records.

Oracle supports different types of indexes:

          B-Tree indexes (Normal)

          Bitmap indexes

          Partitioned indexes

          Function-based indexes

          Domain indexes

Here we are going to discuss about B-Tree and Bitmap Indexes.

I. Oracle - Binary Tree Index:

           

            As the name suggests it makes use of the B-Tree parsing method to find the value being searched for. When it gets a value to be searched, it gets the nearest possible index value. Then it goes to the left node if the value it lesser or to the right if the value is higher. It goes on doing this until it reaches the leaf node which contains the value it is searching for.

 

Fig 1.1 Binary Tree

 

How to Create an Index?

 

CREATE  INDEX        IDX_NAME
ON                               TABLE_NAME (Col_nam1,Col_nam2, )
COMPUTE STATISTICS ;                                                       --optional

 

** Note: by default, Oracle creates B-tree indexes

How to Rename an Index?

    ALTER INDEX            IDX_NAME
     RENAME TO              NEW_IDX_NAME;

How to Collect Statistics on an Index

            ALTER INDEX            IDX_NAME  

            COMPUTE STATISTICS;

 

How to Drop an Index

DROP INDEX IDX_NAME;

 

II. Oracle - Bitmap Index:

 

Oracle Bitmap Indexes are different from Standard B-Tree Indexes in the way they store the information about the indexes. Bitmap Indexes stores the information about the value in bits; with 0s and 1s; thus called Bitmaps. Something like this:

 

 

Fig 1.2  Show how Bitmap Indexes are stored.
 

Bitmap Index creates a two-dimensional array with one column for every row in the table being indexed. If the value being indexed is found in the row, it will specify a  1  and if not found then a  0 . So while searching, Oracle takes only the information from those rows which shows a  1  in the Bitmap Index. That means, the records which are pointed by the  1 s in the Index, have got the searched/indexed value.

 

And from a performance point of view, during the data retrieval time, Oracle decompresses the bitmap into the RAM data buffers so that it can rapidly scan for matching values. These matching values are the given to the Oracle engine in the form of a ROWID list, and ROWIDs as we know are unique identifiers to records/rows.

 

Q. How to Create a Bitmap Index?

 

CREATE  BITMAP    INDEX             IDX_NAME
ON                                                       TABLE_NAME (Col_nam1,Col_nam2, )
COMPUTE STATISTICS ;  

 

*********************************************************

B-Tree Versus - Bitmap Indexes

*********************************************************

 

 

                                                These are some differences between B-Tree and Bitmap Indexes in Structure and performance and their usage.

1. DML Operations:

            If you have too much DML operations (insert/update/delete) on a table on which the columns are indexed, it is better to use B-Tree indexes.

For eg: if you work on a batch processing environment, then it is obvious that you would have frequent DML operations on the tables involved.

            Updating bitmapped indexes consumes lot of resources. So, it is better to use Bitmap Indexes on columns of  Tables  which are updated/inserted/deleted  less frequently .

2. Performance:

Bitmap Indexes have highly compressed structure, making them fast to read and their structure makes it possible for the system to combine multiple indexes together for fast access to the underlying table/s.

3. Structure:

  Compressed structure in Bitmap Indexes makes it faster to read from disk; but utilises more CPU to decompress the information.

   Uncompressed structure in B-Tree involves a lesser CPU load compared to Bitmap but it requires more bandwidth to read since it is uncompressed data.

4. Cardinality:

Cardinality means, the ratio of distinct values to the total number of records. So, a low cardinality means, distinct values are very less.
In such a scenario, Bitmap indexes are considered to be more efficient thatB-Tree indexes.