Bitmap Conversion From Rowid

Several times while checking the explain Plan, I find bitmap conversion to rowid which could slow down the performance of the query. The "bitmap conversion to ROWIDS" execution plan step was introduced in Oracle 9i when the default for _b_tree_bitmap_plans changed from "false" to "true". The "BITMAP CONVERSION (TO ROWIDS)" plan is not always an optimal step when converting from b-tree indexes, and it can be very inefficient access plan in some cases.

Bitmap conversion to ROWIDS does not require bitmap indexes, and it's sometimes found in cases of SQL with complex WHERE clause conditions.

Below use case will demo how we can get rid of bitmap conversion from rowid.

CREATE TABLE T2 (
  C1 NUMBER,
  C2 NUMBER,
  C3 VARCHAR2(100),
  PRIMARY KEY(C1));

INSERT INTO
  T2
SELECT
  ROWNUM C1,
  MOD(ROWNUM-1,20)+1 C2,
  RPAD('A',100,'A')
FROM
  DUAL
CONNECT BY
  LEVEL<=100000;

COMMIT;

CREATE INDEX IND_T2_C2 ON T2(C2);

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'HR',TABNAME=>'T2');
 END;

SELECT 
  C1,
  C2,
  C3
FROM
  T2
WHERE
  C1 BETWEEN 1 AND 10000
  AND C2=1;
----------------------------------------------------------------------------------------------------------------
Plan Hash Value  : 1160936309 

-----------------------------------------------------------------------------------------------
| Id  | Operation                             | Name        | Rows  | Bytes | Cost | Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |             |   500 | 54500 |  130 | 00:00:01 |
|   1 |   TABLE ACCESS BY INDEX ROWID BATCHED | T2          |   500 | 54500 |  130 | 00:00:01 |
|   2 |    BITMAP CONVERSION TO ROWIDS        |             |       |       |      |          |
|   3 |     BITMAP AND                        |             |       |       |      |          |
|   4 |      BITMAP CONVERSION FROM ROWIDS    |             |       |       |      |          |
| * 5 |       INDEX RANGE SCAN                | IND_T2_C2   | 10000 |       |   10 | 00:00:01 |
|   6 |      BITMAP CONVERSION FROM ROWIDS    |             |       |       |      |          |
|   7 |       SORT ORDER BY                   |             |       |       |      |          |
| * 8 |        INDEX RANGE SCAN               | SYS_C008058 | 10000 |       |   20 | 00:00:01 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 5 - access("C2"=1)
* 8 - access("C1">=1 AND "C1"<=10000)

Let us now try to tune it.

SELECT /*+ FULL(t2) parallel(t2 , 32) OPT_PARAM('_B_TREE_BITMAP_PLANS','FALSE') */
  C1,
  C2,
  C3
FROM
  T2
WHERE
  C1 BETWEEN 1 AND 10000
  AND C2=1;
-------------------------------------------------------------------------------------------------------------------
 Plan Hash Value  : 1216610266 

----------------------------------------------------------------------------
| Id  | Operation              | Name     | Rows | Bytes | Cost | Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |          |  500 | 54500 |   15 | 00:00:01 |
|   1 |   PX COORDINATOR       |          |      |       |      |          |
|   2 |    PX SEND QC (RANDOM) | :TQ10000 |  500 | 54500 |   15 | 00:00:01 |
|   3 |     PX BLOCK ITERATOR  |          |  500 | 54500 |   15 | 00:00:01 |
| * 4 |      TABLE ACCESS FULL | T2       |  500 | 54500 |   15 | 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):