Ask the Expert

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.

    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: