Cardinality rule for bitmap indexes

Question

The Oracle documentation includes the following advice:

A bitmap index should be built on each of the foreign key columns of the fact table or tables

In that reference, there is even a bitmap index on the date column. Whatever happened to cardinality rule for using bitmap indexes? Date columns defy that rule the most, but other columns like customer_key are also a little too huge to be considered candidates for bitmap indexes. I can sort of understand putting one on item_key if you don't have thousands of items.

If not a bitmap index, then what - especially for a date column that has a foreign key to a time dimension - typical stuff - month, year, day, etc? Obviously, it's queried often.

I asked this question on Stack Overflow a couple days ago, but I'm going to delete it, since it received no replies.

score 5 · Accepted Answer · answered Dec 13 '12 at 22:58

There was never a rule that bitmap indexes were only useful on columns that had relatively few distinct values. That was a myth that derived from the fact that bitmap indexes aren't appropriate for columns that are unique or mostly unique and that a lot of the columns that you would want to put bitmap indexes on happen to have relatively few distinct values.

Richard Foote (who probably knows more about indexes in Oracle than any other person on the planet) has a nice article on bitmap indexes with many distinct values that walks through why this is perfectly reasonable and appropriate in much more detail. A followup article comparing bitmap and b-tree indexes on columns with many distinct values is also well worth reading.

score 2 · Answer 2 · answered Dec 14 '12 at 16:11

As Justin's said (and the links in his post prove), the cardinality rule is a myth. This aside, there's a good reason to use bitmap indexes on fact tables: separate bitmap indexes on can easily be combined by the optimizer to reduce the numbers of rows to access.

This is very useful with fact tables with a large number of dimensions. While any single dimension may return a large percentage of the data, when combined with others this may fall dramatically. For example, you may have thousands of orders per day and thousands of customers (with hundreds of orders each), but a given customer is likely to only have 1-2 orders on any given day.

This saves you having to create multi-column b-tree indexes. As (ignoring some skip-scan conditions), the leading column in an index must be referenced in the where clause to be used. So with three dimensions you need to create six multi-column b-tree indexes to ensure an index is available for every query your users may throw at you ( ind1: col1, col2, col3; ind2: col1, col3, col2; ind3: col2, col1, col3 etc.)

With bitmaps, you just need three single column indexes and can leave the optimizer to decide whether it's beneficial to combine them or not.

This example shows how the two single column bitmap indexes are combined, but the b-tree indexes aren't (note Oracle can convert b-tree indexes to bitmaps, but this is rare):

create table big_fact_table (first_date date, second_date date, junk varchar2(20));

insert into big_fact_table
  SELECT trunc(sysdate)+floor(level/100), trunc(sysdate)+mod(level,100),
         dbms_random.string('x', 20)
  FROM dual connect by level <= 1000;

create bitmap index fd_i on big_fact_table (first_date);
create bitmap index sd_i on big_fact_table (second_date);

commit;

exec dbms_stats.gather_table_stats(user, 'big_fact_table', cascade => true);

explain plan for 
  SELECT * FROM big_fact_table
  where  first_date = trunc(sysdate)+1
  and    second_date = trunc(sysdate)+1;

-- both indexes can be combined easily to narrow the output
SELECT * FROM table(dbms_xplan.display(null, null, 'ROWS -COST -BYTES'));

--------------------------------------------------------------------------                                                                                                                                                                                                                                   
| Id  | Operation                    | Name           | Rows  | Time     |                                                                                                                                                                                                                                   
--------------------------------------------------------------------------                                                                                                                                                                                                                                   
|   0 | SELECT STATEMENT             |                |     1 | 00:00:01 |                                                                                                                                                                                                                                   
|   1 |  TABLE ACCESS BY INDEX ROWID | BIG_FACT_TABLE |     1 | 00:00:01 |                                                                                                                                                                                                                                   
|   2 |   BITMAP CONVERSION TO ROWIDS|                |       |          |                                                                                                                                                                                                                                   
|   3 |    BITMAP AND                |                |       |          |                                                                                                                                                                                                                                   
|*  4 |     BITMAP INDEX SINGLE VALUE| SD_I           |       |          |                                                                                                                                                                                                                                   
|*  5 |     BITMAP INDEX SINGLE VALUE| FD_I           |       |          |                                                                                                                                                                                                                                   
--------------------------------------------------------------------------        

drop index fd_i;
drop index sd_i;
create index fd_i on big_fact_table (first_date);
create index sd_i on big_fact_table (second_date);

exec dbms_stats.gather_table_stats(user, 'big_fact_table', cascade => true);

explain plan for 
  SELECT * FROM big_fact_table
  where  first_date = trunc(sysdate)+1
  and    second_date = trunc(sysdate)+1;

-- the optimizer will only use one of the two available indexes
SELECT * FROM table(dbms_xplan.display(null, null, 'ROWS -COST -BYTES'));

-------------------------------------------------------------------------                                                                                                                                                                                                                                    
| Id  | Operation                   | Name           | Rows  | Time     |                                                                                                                                                                                                                                    
-------------------------------------------------------------------------                                                                                                                                                                                                                                    
|   0 | SELECT STATEMENT            |                |     1 | 00:00:01 |                                                                                                                                                                                                                                    
|*  1 |  TABLE ACCESS BY INDEX ROWID| BIG_FACT_TABLE |     1 | 00:00:01 |                                                                                                                                                                                                                                    
|*  2 |   INDEX RANGE SCAN          | FD_I           |   100 | 00:00:01 |                                                                                                                                                                                                                                    
-------------------------------------------------------------------------

Cardinality rule for bitmap indexes

2 Answers2