Index  Definition

An index  is a performance-tuning method of allowing faster retrieval of records. An index creates an entry for each value that appears in the indexed columns. By default, Oracle creates B-tree indexes.

Bitmapped indexes

In a Bitmap index, a 2 dimensional array is created. The array represents the index value multiplied by number of rows. One column is allotted for every row in the table being indexed. When a row is retrieved, the bitmap is decompressed into the RAM data buffer to rapidly scan for matching values. Each matching value is returned as a ROW ID which can be used to access the desired information.

B-tree index

B-tree indexes are usually created on columns containing the most unique values. It is an ordered set of entries in which each entry is a search key value and and a pointer to a specifc row with that value. When a server finds a search key value matching a constraint, the pointer is used to fetch the row.

Bitmap Index

A. Performance Considerations for Bitmap Indexes
Bitmap indexes can substantially improve performance of queries that have all of the following characteristics:

• The WHERE clause contains multiple predicates on low- or medium-cardinality columns.
• The individual predicates on these low- or medium-cardinality columns select a large number of rows.
• Bitmap indexes have been created on some or all of these low- or medium-cardinality columns.
• The tables being queried contain many rows.