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
- Dozens more answers to tough Oracle questions from Brian Peasland are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
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.