Oracle Database Discussions

Hi,

Can you please explan me about BITMAP CONVERSION TO ROWIDS.Why the below query going for 3 times BITMAP CONVERSION TO ROWIDS on the same table.

In my two database instances query plan is different eventhough record are same.the second instance plan showing with BITMAP CONVERSION TO ROWIDS

Problem instance query plan:
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 194 | 479 | | |
| 1 | TABLE ACCESS BY LOCAL INDEX ROWID | Tab2 | 1 | 17 | 3 | 4 | 4 |
| 2 | NESTED LOOPS | | 1 | 194 | 479 | | |
| 3 | PARTITION RANGE ALL | | | | | 1 | 7 |
| 4 | TABLE ACCESS BY LOCAL INDEX ROWID| Tab1 | 1 | 177 | 475 | 1 | 7 |
| 5 | BITMAP CONVERSION TO ROWIDS | | | | | | |
| 6 | BITMAP AND | | | | | | |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
| 7 | BITMAP CONVERSION FROM ROWIDS | | | | | | |
| 8 | INDEX RANGE SCAN | Tab1_indx4 | 11625 | | 105 | 1 | 7 |
| 9 | BITMAP CONVERSION FROM ROWIDS | | | | | | |
| 10 | INDEX RANGE SCAN | Tab1_indx3 | 11625 | | 113 | 1 | 7 |
| 11 | BITMAP CONVERSION FROM ROWIDS | | | | | | |
| 12 | INDEX RANGE SCAN | Tab1_indx1 | 11625 | | 402 | 1 | 7 |
| 13 | INDEX RANGE SCAN | Tab2_indx1 | 1 | | 2 | 4 | 4 |
----------------------------------------------------------------------------------

Other instance query plan


----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 282 | 12 | | |
| 1 | TABLE ACCESS BY LOCAL INDEX ROWID | tab2 | 1 | 17 | 3 | 4 | 4 |
| 2 | NESTED LOOPS | | 1 | 282 | 12 | | |
| 3 | PARTITION RANGE ALL | | | | | 1 | 7 |
| 4 | TABLE ACCESS BY LOCAL INDEX ROWID| tab1 | 1 | 265 | 9 | 1 | 7 |
| 5 | INDEX RANGE SCAN | tab1_indx1 | 9211 | | 8 | 1 | 7 |
| 6 | INDEX RANGE SCAN | tab2_indx1 | 1 | | 2 | 4 | 4 |


Thanks very much for your help in advance.

Thanks
Anand