Oracle: bitmap conversion to rowid Oracle Database Tips by Donald Burleson

 

Question:  I have a query against tables without a bitmap index, and get I see "bitmap conversion to ROWID" in the execution plan.  How does bitmap conversion to ROWIDS work?  I want to understand optimizer behavior i.e. BITMAP CONVERSION TO ROWIDS and BITMAP CONVERSION FROM ROWIDS. What does this mean and how can i change it to simple rowid access?
 

 

Answer: The "bitmap conversion to ROWIDS" execution plan step was generally 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.

 

The bitmap conversion to rowids is sometimes seen with star transformations (by setting star_transformation_enabled = true).  You can also turn-off bitmap conversion to ROWIDS in your init.ora by re-setting this hidden parm:

 

_b_tree_bitmap_plans=false

 

You can also turn-off bitmap conversion at the session level, for testing:

 

alter session set "_b_tree_bitmap_plans"=false
 

As always, notify Oracle technical support before employing any hidden parameters, as they can make your database unsupported.

Notes:

There is some Oracle 9i bugs ( bug 2546446 and bug 2742886 ) that invoke inappropriate "BITMAP conversion to ROWID" access path.
 

Get the Complete Oracle SQL Tuning Information  The landmark book "Advanced Oracle SQL Tuning  The Definitive Reference"  is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.