Let’s create our test harness first:
CREATE TABLE IDXTEST AS select 1e6 + rownum id, 1e6 + trunc(rownum/1000)*2 id2, rpad(‘a’,399,’a’) filler
from dual connect by level <2e6
order by dbms_random.value;CREATE BITMAP INDEX IDXTEST_IDX1 ON IDXTEST (id2);
I’ve created a table with column id2 having a single unique value per 1000 rows.
Let’s have a look at 2 execution plans (of 2 a bit different queries):
1st one uses bitmap index to return all rows for given indexed column
SQL_ID bv039034q966g, child number 1
-------------------------------------
select id2 FROM IDXTEST
Plan hash value: 1842016195
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 900 (100)| |
| 1 | BITMAP CONVERSION TO ROWIDS | | 1999K| 11M| 900 (0)| 00:00:01 |
| 2 | BITMAP INDEX FAST FULL SCAN| IDXTEST_IDX1 | | | | |
---------------------------------------------------------------------------------------------
Second one returns distinct value of bitmap indexed column:
SQL_ID 0gtn94fcg82hj, child number 0
-------------------------------------
select distinct id2 FROM IDXTEST
Plan hash value: 473080087
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 949 (100)| |
| 1 | HASH UNIQUE | | 2000 | 12000 | 949 (6)| 00:00:01 |
| 2 | BITMAP INDEX FAST FULL SCAN| IDXTEST_IDX1 | 1999K| 11M| 900 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
You can see that Oracle estimate the 2nd query a bit higher and it doesn’t estimate “BITMAP CONVERSION TO ROWIDS” at all which is very suspicious for me as that operation on big datasets can be both CPU and IO(temp) intensive.
Let’s see what is in reality?
Let’s repeat our exercise one more time with ALTER SESSION SET statistics_level=ALL
And show execution plan as select * from TABLE(dbms_xplan.display_cursor(null,null,’typical +iostats last’));
instead of select * from TABLE(dbms_xplan.display);
So here is my results:
1st execution:
SQL_ID bv039034q966g, child number 2
-------------------------------------
select id2 FROM IDXTEST
Plan hash value: 1842016195
---------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 900 (100)| | 1999K|00:00:05.93 | 2012 |
| 1 | BITMAP CONVERSION TO ROWIDS | | 1 | 1999K| 11M| 900 (0)| 00:00:01 | 1999K|00:00:05.93 | 2012 |
| 2 | BITMAP INDEX FAST FULL SCAN| IDXTEST_IDX1 | 1 | | | | | 2000 |00:00:00.05 | 2012 |
---------------------------------------------------------------------------------------------------------------------------------------
2nd execution:
SQL_ID 0gtn94fcg82hj, child number 1
-------------------------------------
select distinct id2 FROM IDXTEST
Plan hash value: 473080087
---------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 949 (100)| | 2000 |00:00:00.02 | 1012 |
| 1 | HASH UNIQUE | | 1 | 2000 | 12000 | 949 (6)| 00:00:01 | 2000 |00:00:00.02 | 1012 |
| 2 | BITMAP INDEX FAST FULL SCAN| IDXTEST_IDX1 | 1 | 1999K| 11M| 900 (0)| 00:00:01 | 2000 |00:00:00.01 | 1012 |
---------------------------------------------------------------------------------------------------------------------------------------
You can see that in reality 2nd execution is faster more than 100 times (0.02 sec vs 5 sec).
It seems a severe limitation of Oracle optimizer that can block potentially very beneficial (from performance perspective) execution plans. I’m going to publish some “near real life” example in my next blog.
