Hi, I've been testing using bitmap indexes on 11.2.0.2 EE. When joining two tables on a column with a BTree index, the index is used in the execution plan. If this index is changed to a bitmap index, Oracle no longer uses the index when executing the query! It assigns a higher cost to using the bitmap index when joining, despite this being a cheaper approach (in terms of consistent gets). To see this, I created a date dimension table with 515 days (rows) and a sales fact table with 1,000 rows for each day: create table date_d ( date_id integer not null primary key, calendar_date date not null unique ); create table sales_fact ( date_id integer not null references date_d (date_id), quantity number not null, total_value number not null ); insert into date_d select rownum, date'2013-01-01'-1+rownum from dual connect by level <= sysdate - date'2013-01-01'; insert into sales_fact with rws as (select * from dual connect by level <= 1000) select d.date_id, round(dbms_random.value(1, 20)), round(dbms_random.value(10, 100), 2) from date_d d cross join rws order by d.date_id; begin dbms_stats.gather_table_stats(user, 'sales_fact'); dbms_stats.gather_table_stats(user, 'date_d'); end; / If I create a BTree index on SALES_FACT.DATE_ID, then join from the date dim to the fact table, restricting to two days, Oracle uses the index on the fact table as I would expect (as we're fetching 2,000 of 515,000 rows): create index safa_date_id on sales_fact (date_id); set autotrace trace select s.* from sales_fact s join date_d d on d.date_id = s.date_id where calendar_date between date'2013-12-31' and date'2014-01-01'; set autotrace off Execution Plan ---------------------------------------------------------- Plan hash value: 2189554905 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 3002 | 69046 | 21 (0)| 00:00:01 | | 1 | NESTED LOOPS | | | | | | | 2 | NESTED LOOPS | | 3002 | 69046 | 21 (0)| 00:00:01 | | 3 | TABLE ACCESS BY INDEX ROWID| DATE_D | 3 | 36 | 3 (0)| 00:00:01 | |* 4 | INDEX RANGE SCAN | SYS_C0037151 | 3 | | 2 (0)| 00:00:01 | |* 5 | INDEX RANGE SCAN | SAFA_DATE_ID | 1000 | | 3 (0)| 00:00:01 | | 6 | TABLE ACCESS BY INDEX ROWID | SALES_FACT | 1000 | 11000 | 6 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("D"."CALENDAR_DATE">=TO_DATE(' 2013-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "D"."CALENDAR_DATE"<=TO_DATE(' 2014-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) 5 - access("D"."DATE_ID"="S"."DATE_ID") Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 290 consistent gets 6 physical reads 0 redo size 36195 bytes sent via SQL*Net to client 1839 bytes received via SQL*Net from client 135 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 2000 rows processed However, if I drop the normal index and re-create it as a bitmap index the query above changes to a FTS on the SALES_FACT table: set autotrace off drop index safa_date_id; create bitmap index safa_date_id on sales_fact (date_id); set autotrace trace select s.* from sales_fact s join date_d d on d.date_id = s.date_id where calendar_date between date'2013-12-31' and date'2014-01-01'; Execution Plan ---------------------------------------------------------- Plan hash value: 525754326 --------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 3002 | 69046 | 315 (2)| 00:00:04 | |* 1 | HASH JOIN | | 3002 | 69046 | 315 (2)| 00:00:04 | | 2 | TABLE ACCESS BY INDEX ROWID| DATE_D | 3 | 36 | 3 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | SYS_C0037151 | 3 | | 2 (0)| 00:00:01 | | 4 | TABLE ACCESS FULL | SALES_FACT | 515K| 5532K| 310 (1)| 00:00:04 | --------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("D"."DATE_ID"="S"."DATE_ID") 3 - access("D"."CALENDAR_DATE">=TO_DATE(' 2013-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "D"."CALENDAR_DATE"<=TO_DATE(' 2014-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 1267 consistent gets 0 physical reads 0 redo size 36195 bytes sent via SQL*Net to client 1839 bytes received via SQL*Net from client 135 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 2000 rows processed If we hint the query to use the bitmap index, we can see it has a higher cost. The autotrace stats report significantly fewer consistent gets though: Execution Plan ---------------------------------------------------------- Plan hash value: 1520624055 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 3002 | 69046 | 416 (0)| 00:00:05 | | 1 | NESTED LOOPS | | | | | | | 2 | NESTED LOOPS | | 3002 | 69046 | 416 (0)| 00:00:05 | | 3 | TABLE ACCESS BY INDEX ROWID| DATE_D | 3 | 36 | 3 (0)| 00:00:01 | |* 4 | INDEX RANGE SCAN | SYS_C0037151 | 3 | | 2 (0)| 00:00:01 | | 5 | BITMAP CONVERSION TO ROWIDS| | | | | | |* 6 | BITMAP INDEX SINGLE VALUE | SAFA_DATE_ID | | | | | | 7 | TABLE ACCESS BY INDEX ROWID | SALES_FACT | 1000 | 11000 | 416 (0)| 00:00:05 | ---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("D"."CALENDAR_DATE">=TO_DATE(' 2013-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "D"."CALENDAR_DATE"<=TO_DATE(' 2014-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) 6 - access("D"."DATE_ID"="S"."DATE_ID") Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 152 consistent gets 1 physical reads 0 redo size 36195 bytes sent via SQL*Net to client 1839 bytes received via SQL*Net from client 135 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 2000 rows processed If you remove the join to the date dim and just use the date ids, Oracle uses the index as expected: select * from sales_fact where date_id between 365 and 366; Execution Plan ---------------------------------------------------------- Plan hash value: 2749560877 --------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 3002 | 33022 | 221 (0)| 00:00:03 | | 1 | TABLE ACCESS BY INDEX ROWID | SALES_FACT | 3002 | 33022 | 221 (0)| 00:00:03 | | 2 | BITMAP CONVERSION TO ROWIDS| | | | | | |* 3 | BITMAP INDEX RANGE SCAN | SAFA_DATE_ID | | | | | --------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("DATE_ID">=365 AND "DATE_ID"<=366) Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 144 consistent gets 0 physical reads 0 redo size 36195 bytes sent via SQL*Net to client 1839 bytes received via SQL*Net from client 135 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 2000 rows processed Looking at the 10053 trace, I can see this is because the bm index join cost is calculated as higher than the FTS of SALES_FACT: NL Join Outer table: Card: 3.00 Cost: 3.00 Resp: 3.00 Degree: 1 Bytes: 12 Access path analysis for SALES_FACT Inner table: SALES_FACT Alias: S Access Path: TableScan NL Join: Cost: 929.82 Resp: 929.82 Degree: 1 Cost_io: 920.00 Cost_cpu: 317629069 Resp_io: 920.00 Resp_cpu: 317629069 ****** trying bitmap/domain indexes ****** Access Path: index (AllEqJoinGuess) Index: SAFA_DATE_ID resc_io: 1.00 resc_cpu: 8171 ix_sel: 0.001942 ix_sel_with_filters: 0.001942 NL Join : Cost: 6.00 Resp: 6.00 Degree: 1 Cost_io: 6.00 Cost_cpu: 47359 Resp_io: 6.00 Resp_cpu: 47359 Bitmap nodes: Used SAFA_DATE_ID Cost = 6.001464, sel = 0.001942 Access path: Bitmap index - accepted Cost: 416.121488 Cost_io: 415.999594 Cost_cpu: 3943626.145192 Sel: 0.001942 Not Believed to be index-only ****** finished trying bitmap/domain indexes ****** Best NL cost: 416.12 resc: 416.12 resc_io: 416.00 resc_cpu: 3943626 resp: 416.12 resp_io: 416.00 resc_cpu: 3943626 However, same section of the 10053 trace shows the following when using a BTree index on the SALES_FACT.DATE_ID column: NL Join Outer table: Card: 3.00 Cost: 3.00 Resp: 3.00 Degree: 1 Bytes: 12 Access path analysis for SALES_FACT Inner table: SALES_FACT Alias: S Access Path: TableScan NL Join: Cost: 929.82 Resp: 929.82 Degree: 1 Cost_io: 920.00 Cost_cpu: 317629069 Resp_io: 920.00 Resp_cpu: 317629069 Access Path: index (AllEqJoinGuess) Index: SAFA_DATE_ID resc_io: 6.00 resc_cpu: 433579 ix_sel: 0.001942 ix_sel_with_filters: 0.001942 NL Join : Cost: 21.04 Resp: 21.04 Degree: 1 Cost_io: 21.00 Cost_cpu: 1323580 Resp_io: 21.00 Resp_cpu: 1323580 Best NL cost: 21.04 resc: 21.04 resc_io: 21.00 resc_cpu: 1323580 resp: 21.04 resp_io: 21.00 resc_cpu: 1323580 Why does this happen? Is this a bug or expected behaviour? Thanks, Chris www.sqlfail.com