You just answered a question about cardinality versus the number of rows for a bitmap index. I have a question that pertains exactly to this subject... what is too high of a cardinality? You address the too low, but not the too high. I have heard that 1/10 of 1% of the total table rows or as high as 1% of total rows. For example, I am at a site with tables that have 2,000,000 rows and 200,000 distinct values on a column, and they have created a bitmap index. I don't think the bitmap is at all appropriate in this situation and should be a b-tree instead.
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.