Pages

Tuesday, 7 August 2012

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 COMPUTESTATISTICS.
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.