I haven't seen anyone mention any specific values for "too high" of a cardinality to limit bitmap indexes. But we do know that if a query will return a significant number of rows from a table, then no index should be used. In your example, a distinct value corresponds to an average of 10% of the table. This seems too high considering the numbers that you have mentioned.
Jonathan Lewis discusses bitmap indexes in detail in his Practical Oracle 8i book. I highly suggest this book to anyone! In it, he states, "A single bitmap index is typically fairly useless." and "Bitmaps work well only when several indexes are combined." So make sure that you use his rules of thumb when considering bitmap indexes. The power in bitmap indexes comes from ANDing them together to significantly reduce the rows considered for the result set. Are you using bitmap indexes on other columns as well? And are these columns part of your WHERE clause? If not, then a bitmap index may not be useful. Like I said, the Lewis book gives tons of useful information to help you decide if the bitmap index is important to you.
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 December 2002