What is too high of a cardinality for a bitmap index?

You just answered a question about cardinality versus the number of rows for a bitmap index. I have a question...

that pertains exactly to this subject... what is too high of a cardinality? You address the too low, but not the too high. I have heard that 1/10 of 1% of the total table rows or as high as 1% of total rows. For example, I am at a site with tables that have 2,000,000 rows and 200,000 distinct values on a column, and they have created a bitmap index. I don't think the bitmap is at all appropriate in this situation and should be a b-tree instead.

I haven't seen anyone mention any specific values for "too high" of a cardinality to limit bitmap indexes. But we do know that if a query will return a significant number of rows from a table, then no index should be used. In your example, a distinct value corresponds to an average of 10% of the table. This seems too high considering the numbers that you have mentioned.

Jonathan Lewis discusses bitmap indexes in detail in his Practical Oracle 8i book. I highly suggest this book to anyone! In it, he states, "A single bitmap index is typically fairly useless." and "Bitmaps work well only when several indexes are combined." So make sure that you use his rules of thumb when considering bitmap indexes. The power in bitmap indexes comes from ANDing them together to significantly reduce the rows considered for the result set. Are you using bitmap indexes on other columns as well? And are these columns part of your WHERE clause? If not, then a bitmap index may not be useful. Like I said, the Lewis book gives tons of useful information to help you decide if the bitmap index is important to you.

For More Information

This was first published in December 2002

Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.



Forgot Password?


Your password has been sent to: