Bitmap Indexes in Datawarehousing

Bitmap indexes are widely used in data warehousing environments. These environments typically have large amounts of data and ad hoc queries, but a low level of concurrent DML transaction

What is a bitmap index:

A bitmap index is a specialized variation of a B-tree index. If the degree of cardinality is high for the attribute, means that there are more unique number of values for a particular attribute. Low cardinality attribute is not suitable for bitmap index because more number of records are locked which result in the locking of a whole table, leading to the lock on a whole database. For eg. A gender column, which has only two distinct values (male and female), is optimal for a bitmap index. However, data warehouse administrators also build bitmap indexes on columns with higher cardinalities.

You can use a bitmap index when both of the following conditions are true:

• The key values in the index contain many duplicates.
• More than one column in the table has an index that the optimizer can use to improve performance on a table scan.

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. Bitmap indexes store the bitmaps in a compressed way. If the number of distinct key values is small, bitmap indexes compress better and the space saving benefit compared to a B-tree index becomes even better

Note:When creating bitmap indexes, you should use NOLOGGING and COMPUTE STATISTICS. In addition, you should keep in mind that bitmap indexes are usually easier to destroy and re-create than to maintain.

ADVANTAGES

• The Advantages of using bitmap indexes are greatest for columns in which the ratio of the number of distinct values to the number of rows in the table is small

• Space requirements for indexes in a warehouse are often significantly larger than the space needed to store the data, especially for the fact table and particularly if the indexes are B*trees.Hence, you may want to keep indexing on the fact table to a minimum. Typically, you may have one or two concatenated B*tree indexes on the fact table; however, most of your indexes should be bitmap indexes. Bitmap indexes also take up much less space than B*tree indexes and so should be preferred

What is B-tree index

B-tree indexes are most commonly used in a data warehouse to enforce unique keys. In many cases, it may not even be necessary to index these columns in a data warehouse, because the uniqueness was enforced as part of the preceding ETL processing, and because typical data warehouse queries may not work better with such indexes. B-tree indexes are more common in environments using third normal form schemas. In general, bitmap indexes should be more common than B-tree indexes in most data warehouse environments.