Hi i have my query and plan as follows. it shows something called bitmap conversion to rowid mean . what does this mean. When will this converison happen? Is it bad for the performance of the query ?
SELECT ux.usr_id, ud.usr_type_cd, ud.usr_lst_nm,
ud.usr_frst_nm, ud.tmzon_cd, cust.pst_cd,
prod.prod_type_nm, prod.prod_shrt_nm, prod.prod_full_nm,
prod.prod_fam_cd, prod.sts_cd, prod.prod_cd,
ult_prnt.prod_cd ult_prnt_prod_cd, cust.cust_id,
cust.mseg_cd, cust.lgl_nm, ud.intl_in,
ud.email_ad, cust.cust_line3_ad, cust.cust_line2_ad,
cust.cust_line1_ad, cust.cntry_cd, cust.city_nm,
cust.logon_type_cd, ud.usr_line1_ad, ud.usr_line2_ad,
ud.usr_line3_ad, usr_phn.media_comm_ad usr_tn,
usr_fax.media_comm_ad usr_tn_fax, ud.pst_cd usr_pst_cd,
ud.city_nm usr_city_nm, ud.cntry_cd usr_country_cd,
cust.rgn_id cust_region
FROM usr_xref ux,
usr_dim ud ,
(SELECT DISTINCT usr_dim_nb, cust_dim_nb, prod_dim_nb
FROM hds_fct
WHERE hds_fct.cust_dim_nb <> 0
AND hds_fct.usr_dim_nb <> 0
AND hds_fct.acct_dim_nb = 0
AND hds_fct.prod_dim_nb <> 0
AND hds_fct.role_dim_nb = 0
AND hds_fct.num_cstr_dim_nb = 0
AND hds_fct.obj_gp_dim_nb = 0
AND hds_fct.sbj_gp_dim_nb = 0
AND hds_fct.obj_cstr_dim_nb = 0
AND hds_fct.srv_dim_nb = 0) cust_usr,
cust_dim cust,
prod_dim prod,
prod_dim ult_prnt,
(SELECT usr_dim_nb, media_comm_ad
FROM usr_media_comm
WHERE lctn_cd = 'work' AND media_comm_type_cd = 'phone') usr_phn,
(SELECT usr_dim_nb, media_comm_ad
FROM usr_media_comm
WHERE lctn_cd = 'fax' AND media_comm_type_cd = 'phone') usr_fax
WHERE ux.sys_id = 'ACCESS'
AND prod.prod_fam_cd = 'ATS'
AND ux.usr_dim_nb = ud.usr_dim_nb
AND ud.usr_dim_nb = usr_phn.usr_dim_nb(+)
AND ud.usr_dim_nb = usr_fax.usr_dim_nb(+)
AND ud.usr_dim_nb = cust_usr.usr_dim_nb
AND cust_usr.cust_dim_nb = cust.cust_dim_nb
AND cust_usr.prod_dim_nb = prod.prod_dim_nb
AND prod.ult_prnt_prod_dim_nb = ult_prnt.prod_dim_nb;
plan
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 757 | 8 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 1 | 757 | 8 (0)| 00:00:01 |
| 3 | NESTED LOOPS OUTER | | 1 | 731 | 7 (0)| 00:00:01 |
| 4 | NESTED LOOPS OUTER | | 1 | 602 | 7 (0)| 00:00:01 |
| 5 | NESTED LOOPS | | 1 | 473 | 7 (0)| 00:00:01 |
| 6 | NESTED LOOPS | | 1 | 454 | 6 (0)| 00:00:01 |
| 7 | NESTED LOOPS | | 1 | 283 | 5 (0)| 00:00:01 |
| 8 | NESTED LOOPS | | 1 | 134 | 4 (0)| 00:00:01 |
|* 9 | TABLE ACCESS BY INDEX ROWID | HDS_FCT | 1 | 36 | 3 (0)| 00:00:01 |
| 10 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 11 | BITMAP AND | | | | | |
| 12 | BITMAP CONVERSION FROM ROWIDS| | | | | |
|* 13 | INDEX RANGE SCAN | HDS_FCT_FK6 | 130 | | 1 (0)| 00:00:01 |
| 14 | BITMAP CONVERSION FROM ROWIDS| | | | | |
|* 15 | INDEX RANGE SCAN | HDS_FCT_FK3 | 130 | | 2 (0)| 00:00:01 |
|* 16 | TABLE ACCESS BY INDEX ROWID | PROD_DIM | 1 | 98 | 1 (0)| 00:00:01 |
|* 17 | INDEX UNIQUE SCAN | PROD_DIM_PK | 1 | | 0 (0)| 00:00:01 |
| 18 | TABLE ACCESS BY INDEX ROWID | CUST_DIM | 1 | 149 | 1 (0)| 00:00:01 |
|* 19 | INDEX UNIQUE SCAN | CUST_DIM_PK | 1 | | 0 (0)| 00:00:01 |
| 20 | TABLE ACCESS BY INDEX ROWID | USR_DIM | 1 | 171 | 1 (0)| 00:00:01 |
|* 21 | INDEX UNIQUE SCAN | USR_DIM_PK | 1 | | 0 (0)| 00:00:01 |
| 22 | TABLE ACCESS BY INDEX ROWID | PROD_DIM | 1 | 19 | 1 (0)| 00:00:01 |
|* 23 | INDEX UNIQUE SCAN | PROD_DIM_PK | 1 | | 0 (0)| 00:00:01 |
|* 24 | TABLE ACCESS BY INDEX ROWID | USR_MEDIA_COMM | 1 | 129 | 0 (0)| 00:00:01 |
|* 25 | INDEX RANGE SCAN | USR_MEDIA_COMM_FK1 | 1 | | 0 (0)| 00:00:01 |
|* 26 | TABLE ACCESS BY INDEX ROWID | USR_MEDIA_COMM | 1 | 129 | 0 (0)| 00:00:01 |
|* 27 | INDEX RANGE SCAN | USR_MEDIA_COMM_FK1 | 1 | | 0 (0)| 00:00:01 |
|* 28 | INDEX RANGE SCAN | USR_XREF_FK1 | 2 | | 0 (0)| 00:00:01 |
|* 29 | TABLE ACCESS BY INDEX ROWID | USR_XREF | 1 | 26 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
9 - filter("HDS_FCT"."PROD_DIM_NB"<>0 AND "HDS_FCT"."USR_DIM_NB"<>0 AND "HDS_FCT"."CUST_DIM_NB"<>0
AND "HDS_FCT"."ROLE_DIM_NB"=0 AND "HDS_FCT"."NUM_CSTR_DIM_NB"=0 AND "HDS_FCT"."OBJ_GP_DIM_NB"=0 AND
"HDS_FCT"."SBJ_GP_DIM_NB"=0 AND "HDS_FCT"."OBJ_CSTR_DIM_NB"=0 AND "HDS_FCT"."SRV_DIM_NB"=0 AND
"HDS_FCT"."RSRC_DIM_NB"=0)
13 - access("HDS_FCT"."PVLG_DIM_NB"=0)
15 - access("HDS_FCT"."ACCT_DIM_NB"=0)
16 - filter("PROD"."PROD_FAM_CD"='ATS')
17 - access("PROD_DIM_NB"="PROD"."PROD_DIM_NB")
filter("PROD"."PROD_DIM_NB"<>0)
19 - access("CUST_DIM_NB"="CUST"."CUST_DIM_NB")
filter("CUST"."CUST_DIM_NB"<>0)
21 - access("UD"."USR_DIM_NB"="USR_DIM_NB")
filter("UD"."USR_DIM_NB"<>0)
23 - access("PROD"."ULT_PRNT_PROD_DIM_NB"="ULT_PRNT"."PROD_DIM_NB")
24 - filter("LCTN_CD"(+)='fax' AND "MEDIA_COMM_TYPE_CD"(+)='phone')
25 - access("UD"."USR_DIM_NB"="USR_DIM_NB"(+))
filter("USR_DIM_NB"(+)<>0)
26 - filter("LCTN_CD"(+)='work' AND "MEDIA_COMM_TYPE_CD"(+)='phone')
27 - access("UD"."USR_DIM_NB"="USR_DIM_NB"(+))
filter("USR_DIM_NB"(+)<>0)
28 - access("UX"."USR_DIM_NB"="UD"."USR_DIM_NB")
filter("UX"."USR_DIM_NB"<>0)
29 - filter("UX"."SYS_ID"='ACCESS')