Ask the Expert

Rule of thumb for bitmap indexes

Is there a rule of thumb for cardinality versus number of rows for a bitmap index, and what datatypes are best/worst suited for it?

    Requires Free Membership to View

The rule of thumb is that bitmap indexes should be used for low cardinality columns. To figure out the cardinality, count the number of distinct values from the column of a table. If I have a table with 1 million rows, and a column only has 10 distinct values, then this is low cardinality.

The one thing I caution people is to not use bitmap indexes with too low of cardinality. Many people like to use bitmap indexes on a GENDER column, with only two values (M=male, F=female). Assuming an even distribution, about 50% of the rows will be male and 50% will be female. A bitmap index on this column is most often not desired, even though the cardinality is very low. If I query for all of the females in the table, then reading any index on this column will result in too much overhead. It is better to do a full table scan in this example.

For More Information


This was first published in November 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: