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

2 comments:

  1. Hi
    This is nice post.

    I do have a question.

    Say we have partition the DB table using any attribute.

    And still if I want to go ahead and create indexes in other attributes column, Should I go for Btree index or the Bitmap index?

    Again it depends on the granularity I guess, But let me know your thoughts.

    Regards
    Prakash

    ReplyDelete
  2. It is a known fact that ILLUMINATI consist of Multi Millionaires,Billionaires who have major influence regarding global affairs, and planning of a New world order. Many world leaders, Presidents, prime ministers, royalty and senior executives are members of ILLUMINATI. follow the steps to Excel in Life. you'll have numerous benefits,for the FIRST TIME IN HISTORY, we are opening our doors to those who believe. If you would like to join ILLUMINATI , to sign up Text: leovincey08@gmail.com or whatsapp +2349033667873 now if you are really serious to help your self

    ReplyDelete

Related Posts Plugin for WordPress, Blogger...

ShareThis