Question: I'm testing a identical query against an index-only table
(IOT) with two indexing options, one with two separate bitmap indexes and the
same query with a single composite bitmap index. The results are
identical, but the performance of the composite index is vast. The
composite index completes in under one second while the two single indexes take
over 20 minutes. Why are composite indexes so much faster than two
separate bitmap indexes?
One would think that since the SQL contains an "AND" condition, Oracle would do
an index scan on the first condition and use that result set to run the next
condition, thereby querying a much smaller subset of records, and not scan
through almost the entire table.
With
composite index
SQL> select count(1) from whdata.customer_dim where current_record_flg = 'Y'
and effective_start_dt = to_date('19000101','YYYYMMDD');
COUNT(1)
----------
1712628
Elapsed: 00:00:01.57
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1 Bytes=8)
1 0 SORT (AGGREGATE)
2 1 PARTITION RANGE (SINGLE) (Cost=1 Card=7183 Bytes=57464)
3 2 BITMAP CONVERSION (COUNT) (Cost=1 Card=7183 Bytes=5746
4)
4 3 BITMAP INDEX (SINGLE VALUE) OF 'COMPOSITE_IDX_TEMP_B' (INDEX (BIT
MAP))
Statistics
----------------------------------------------------------
7903 recursive calls
0 db block gets
2449 consistent gets
249 physical reads
0 redo size
218 bytes sent via SQL*Net to client
280 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
197 sorts (memory)
0 sorts (disk)
1 rows processed
Without composite index
SQL> select count(1) from whdata.customer_dim where current_record_flg = 'Y'
and effective_start_dt = to_date('19000101','YYYYMMDD');
COUNT(1)
----------
1712628
Elapsed: 00:18:56.62
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=8)
1 0 SORT (AGGREGATE)
2 1 PARTITION RANGE (SINGLE) (Cost=2 Card=7183 Bytes=57464)
3 2 INDEX (UNIQUE SCAN) OF 'CUSTOMER_DIM_PK' (INDEX (UNIQU
E)) (Cost=2 Card=7183 Bytes=57464)
4 3 BITMAP CONVERSION (TO ROWIDS)
5 4 BITMAP AND
6 5 BITMAP INDEX (SINGLE VALUE) OF 'CUSTOMER_DIM_B5_
R' (INDEX (BITMAP))
7 5 BITMAP INDEX (SINGLE VALUE) OF 'CUSTOMER_DIM_B1_
R' (INDEX (BITMAP))
Statistics
----------------------------------------------------------
395 recursive calls
0 db block gets
5142876 consistent gets
87890 physical reads
0 redo size
234 bytes sent via SQL*Net to client
280 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
13 sorts (memory)
0 sorts (disk)
1 rows processed
Answer: You have shown a giant difference, and at first blush, I
wonder if you are hitting a bug in the SQL optimizer with multiple single column
bitmap indexes. Usually, the "fastest" SQL is the one that fetches the
right results with the least buffer touches (consistent gets), but the optimizer
appears to be doing too much work with the separate bitmaps.
We must also recall that Oracle changed the requirements for the STAR
transformation from using single column bitmaps to a single composite bitmap
(with all fact table columns included), so perhaps this is related to the huge
performance differences that you have observed.
However, it's clear that the composite index has done some of the matching for
for you when the multi column bitmap index was created, but this does not
explain such a huger difference in performance.
.
and include the URL for the page.
