Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Do I need to mention the caveat that bitmap indexes are generally unsuitable for tables that get updated, or are you well aware of that? –  Jack Douglas Dec 14 '12 at 17:19
    
@JackDouglas I am well aware of that. :) –  user1831003 Dec 14 '12 at 23:34
add comment

2 Answers

up vote 5 down vote accepted

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.

share|improve this answer
2  
Might be worth summarizing the main points from those links to raise this answer from very good to awesome? –  Paul White Dec 14 '12 at 6:43
add comment

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 |                                                                                                                                                                                                                                    
-------------------------------------------------------------------------  
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.