Re: Bitmap index not used when joining tables
- From: K Gopalakrishnan <kaygopal@xxxxxxxxx>
- To: "chris.saxon" <chris.saxon@xxxxxxxxx>
- Date: Mon, 2 Jun 2014 14:36:17 -0500
Chris,
Without going in to deeper details, the fix is to enable event 10170 to
enable the old costing algorithms.
Set the event 10170 (for session level), this is a change behavior event,
quite safe.
-Gopal
On Sat, May 31, 2014 at 10:21 AM, Chris Saxon <chris.saxon@xxxxxxxxx> wrote:
> 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
>
Other related posts:
