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?

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

Dig deeper on Oracle database design and architecture



Enjoy the benefits of Pro+ membership, learn more and join.

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?

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

Your password has been sent to: