oracle 11g performance issue ( BITMAP CONVERSION TO ROWIDS)
792200Jan 9 2012 — edited Jan 10 2012Hi All,
I have table called Directory_listing with record count of 36,213,000. Statistics are gathered for the table. When i was querying the table
select count(1) from directory_listing where R_listing_name = 'SMITH' and LISTING_STATUS_CD = 'A' and ENTRY_FORMAT_CD = 'A'
R_listing_name,LISTING_STATUS_CD & ENTRY_FORMAT_CD column are indexed and analysed.
When i was generating the plan BITMAP CONVERSI is costing more 53035. How it can be avoided.
--------------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time Pstart Pstop
--------------------------------------------------------------------------------
0 SELECT STATEMENT 1 22 53058 (1) 00:10:37
1 SORT AGGREGATE 1 22
2 PARTITION LIST ALL 203K 4382K 53058 (1) 00:10:37 1 7
3 BITMAP CONVERSION COUNT 203K 4382K 53058 (1) 00:10:37
4 BITMAP AND
* 5 BITMAP INDEX SINGLE VALUE DL_LISTING_STATUS_CD_IDX 1 7
6 BITMAP CONVERSION FROM ROWIDS
* 7 INDEX RANGE SCAN DL_R_LISTING_NAME_IDX 784 (1) 00:00:10 1 7
* 8 BITMAP INDEX SINGLE VALUE DL_ENTRY_FORMAT_CD_IDX 1 7
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
--------------------------------------------------------------------------------
5 - access("LISTING_STATUS_CD"='A')
7 - access("R_LISTING_NAME"='SMITH')
8 - access("ENTRY_FORMAT_CD"='A')
Regards,
Mohanarangan K