Shanmugam

• Jul 5th, 2006

 

Bitmap indexing a indexing technique to tune the performance of SQL queries. The default type is B-Tree indexers which is of high cardinality (normalized data). You can use bitmap indexers for de-normalized data or low cardinalities. The condition is the amount of DISTINCT rows should be less than 4% of the total rows. If it satisfies the given condition then bitmap indexers will optimize the performance for this kind of tables.

Anindya1281

 Profile Answers by Anindya1281 

• May 16th, 2010

 

Bitmap indexes are widely used in data warehousing environments. The environments typically have large amounts of data and ad hoc queries, but a low level of concurrent DML transactions. For such applications, bitmap indexing provides:

• Reduced response time for large classes of ad hoc queries.

• Reduced storage requirements compared to other indexing techniques.

• Dramatic performance gains even on hardware with a relatively small number of CPUs or a small amount of memory.

• Efficient maintenance during parallel DML and loads.

Traditional B-tree index can be prohibitively expensive in terms of disk space because the indexes can be several times larger than the data in the table. Bitmap indexes are typically only a fraction of the size of the indexed data in the table.

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

Bit Map Indexes are primarily intended for data warehousing applications where users query the data rather than update it. They are not suitable for OLTP applications with large numbers of concurrent transactions modifying the data.

Bit Map Indexes are easier to create and drop

Bit Map Indexes (BMI) are generally used for data with low cardinality (no of unique recs) as many column in DWH is repeated for example gender either M or F this type of data supports BMI