Oracle: bitmap conversion to rowid 

One of my colleagues recently have a question about bitmap conversion in Oracle. Here is something for you my friend.

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.


Thanks,
NJ