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