The Secrets of Oracle Bitmap Indexes

Overview

Oracle’s two major index types are Bitmap indexes and B-Tree indexes. 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.

Characteristic of Bitmap Indexes

* For columns with very few unique values (low cardinality)

Columns that have low cardinality are good candidates (if the cardinality of a column is <= 0.1 % that the column is ideal candidate, consider also 0.2% – 1%)

* Tables that have no or little insert/update are good candidates (static data in warehouse)

* Stream of bits: each bit relates to a column value in a single row of table


Advantage of Bitmap Indexes

The advantages of them are that they have a 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.

Compressed indexes, like bitmap indexes, represent a trade-off between CPU usage and disk space usage. A compressed structure is faster to read from disk but takes additional CPU cycles to decompress for access – an uncompressed structure imposes a lower CPU load but requires more bandwidth to read in a short time.

One belief concerning bitmap indexes is that they are only suitable for indexing low-cardinality data. This is not necessarily true, and bitmap indexes can be used very successfully for indexing columns with many thousands of different values.

Disadvantage of Bitmap Indexes

The reason for confining bitmap indexes to data warehouses is that the overhead on maintaining them is enormous. A modification to a bitmap index requires a great deal more work on behalf of the system than a modification to a b-tree index. In addition, the concurrency for modifications on bitmap indexes is dreadful.

http://www.akadia.com/services/ora_bitmapped_index.html

Summary

* With bitmap indexes, the optimizer can efficiently answer queries that include AND, OR, or XOR. (Oracle supports dynamic B-tree-to-bitmap conversion, but it can be inefficient.)
* With bitmaps, the optimizer can answer queries when searching or counting for nulls. Null values are also indexed in bitmap indexes (unlike B-tree indexes).
* Most important, bitmap indexes in DSS systems support ad hoc queries, whereas B-tree indexes do not. More specifically, if you have a table with 50 columns and users frequently query on 10 of them—either the combination of all 10 columns or sometimes a single column—creating a B-tree index will be very difficult. If you create 10 bitmap indexes on all these columns, all the queries can be answered by these indexes, whether they are queries on all 10 columns, on 4 or 6 columns out of the 10, or on a single column. The AND_EQUAL hint provides this functionality for B-tree indexes, but no more than five indexes can be used by a query. This limit is not imposed with bitmap indexes.

http://www.oracle.com/technology/pub/articles/sharma_indexes.html

From : http://www.dba-oracle.com
You will want a bitmap index when:

1 – Table column is low cardinality – As a ROUGH guide, consider a bitmap for any index with less than 100 distinct values

select region, count(*) from sales group by region;

2 – The table has LOW DMLYou must have low insert./update/delete activity.  Updating bitmapped indexes take a lot of resources, and bitmapped indexes are best for largely read-only tables and tables that are batch updated nightly.

3 – Multiple columns – Your SQL queries reference multiple, low cardinality values in there where clause.  Oracle cost-based SQL optimizer (CBO) will scream when you have bitmap indexes on .

You will want a bitmap index when:

1 – Table column is low cardinality – As a ROUGH guide, consider a bitmap for any index with less than 100 distinct values

select region, count(*) from sales group by region;

2 – The table has LOW DML – You must have low insert./update/delete activity.  Updating bitmapped indexes take a lot of resources, and bitmapped indexes are best for largely read-only tables and tables that are batch updated nightly.

3 – Multiple columns – Your SQL queries reference multiple, low cardinality values in there where clause.  Oracle cost-based SQL optimizer (CBO) will scream when you have bitmap indexes on .

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s