Problem solve Get help with specific problems with your technologies, process and projects.

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 last published in November 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.

Start the conversation

Send me notifications when other members comment.

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

Please create a username to comment.