When should I use a bitmappped index?


Les Barbusinski’s Answer: Bitmap indexes are recommended when the following conditions are met:

  • There are many queries and/or BI reports that access the base table by the candidate column
  • The base table is relatively large
  • The base table is either infrequently updated or totally rebuilt on a nightly basis
  • The candidate column has a low cardinality (i.e., only a few possible values, such as a gender code whose valid values consist solely of M, F or ?).

Please note that bitmap indexes are very expensive to maintain (i.e., your ETL scripts will run considerably longer), so use them only when the benefits outweigh the costs.

Steve Hoberman’s Answer: I can offer my thoughts on your second question, When should I use a bitmappped index? as we’ve added a number of bitmapped indexes during a recent mart implementation. Bitmapped indexes work great when there is a small subset of distinct values for a given field. For example, a gender code which only contains the values "M" or "F" would be a good candidate for a bitmapped index. A general rule of thumb – if there are 20 or less distinct values for a field, a bitmapped index might be the way to go. However, although retrieval is extremely efficient with a bitmap, inserting and changing data is extremely inefficient. As one almost humorous example, we went from loading 9 rows a second during our initial data load to several hundred rows a second simply by removing the bitmaps! So to summarize, use them when there are a handful of distinct values and when you are retrieving data and rarely inserting and changing data.

Chuck Kelley’s Answer: I use bitmap indexes for the surrogate keys and any field that doesn’t have a chance for a lot of duplicates. For example, address line and product description (assuming there aren’t similar name).

Joe Oates’ Answer: Bitmapped indexes were primarily developed for data warehouse access requirements. Bitmapped indexes were designed to be used for data with few unique values such as Boolean values, status codes and not, for example, a Customer ID column where there could be millions of values and each row has a unique value. Bitmapped indexes are also best for non-volatile data, such as data warehouse data. Modifying bitmapped indexes usually incurs a great deal of overhead, which is why they are not normally used for transaction processing systems.

Clay Rehm’s Answer: You should use a bitmapped index since they deliver dramatic performance benefits to data warehouse tables. They coexist with and complement other available indexing schemes, including standard B-tree indexes, clustered tables and hash clusters. While a B-tree index may be the most efficient way to retrieve data using a unique identifier, bitmapped indexes are most efficient when retrieving data based on a much wider criteria (like in a data warehouse).

Register or login for access to this item and much more

All Information Management content is archived after seven days.

Community members receive:
  • All recent and archived articles
  • Conference offers and updates
  • A full menu of enewsletter options
  • Web seminars, white papers, ebooks

Don't have an account? Register for Free Unlimited Access