Simple life

A bitmap index is a special kind of database index that uses bitmaps.

Bitmap indexes have traditionally been considered to work well for data which has a small number of distinct values but with many occurrences, such as column region on table person, only with values East, North, West or South but each of them has many occurrences.

However, some researchers argue that Bitmap indexes are also useful for unique valued data which is not updated frequently. Bitmap indexes have a significant space and performance advantage over other structures for such data.

        Oracle has 2 kinds of Index: B-tree index and Bitmap Index.

        B-Tree indexes are the regular type that OLTP systems make much use of, and bitmap indexes are a highly compressed index type that tends to be used primarily for data warehouses.

        An index provides pointers to the rows in a table that contain a given key value. A regular index stores a list of rowids for each key corresponding to the rows with that key value. In a bitmap index, a bitmap for each key value replaces a list of rowids.

        Each bit in the bitmap corresponds to a possible rowid, and if the bit is set, it means that the row with the corresponding rowid contains the key value. A mapping function converts the bit position to an actual rowid, so that the bitmap index provides the same functionality as a regular index. If the number of different key values is small, bitmap indexes save space.

        Bitmap indexes are most effective for queries that contain multiple conditions in the WHERE clause. Rows that satisfy some, but not all, conditions are filtered out before the table itself is accessed. This improves response time, often dramatically.

An alphabetical listing of the Oracle system tables that are commonly used.