Oracle Database Discussions

I have a query with FULL hint that is behaving in a strange manner. The query fetches around 700000 of data. Sometimes it fetches the data with the hint and sometimes it does not fetch any data with the hint and then I have to remove the hint and have to fetch the data.I am totally confused. Please tell me any remedy out of this. Below is the query,

select /*+ FULL(COMP_TM) FULL(TRANS_TM) FULL(INVC_TM) */
CUST_BE_ID ,
DISTR_BE_ID ,
FG_BE_ID ,
KIT_BE_ID ,
BG_ID_NO_BE_ID ,
ACTL_TERR_BE_ID ,
CORE_TERR_BE_ID ,
sum( JNJ_LIST_AMT ) AS JNJ_LIST_AMT,
sum( JNJ_PYMT_AMT ) AS JNJ_PYMT_AMT,
sum( JNJ_QTY ) AS JNJ_QTY,
sum( JNJ_REB_AMT ) AS JNJ_REB_AMT,
sum( JNJ_SLS_AMT ) AS JNJ_SLS_AMT,
sum( KIT_LIST_AMT ) AS KIT_LIST_AMT,
sum( KIT_QTY ) AS KIT_QTY,
sum( KIT_SLS_AMT ) AS KIT_SLS_AMT,
sum( FG_PYMT_AMT ) AS FG_PYMT_AMT,
sum( FG_QTY ) AS FG_QTY,
sum( FG_REB_AMT ) AS FG_REB_AMT,
sum( FG_SLS_AMT ) AS FG_SLS_AMT,
sum( FG_LIST_AMT ) AS FG_LIST_AMT,
to_date('15'||substr(COMP_TM.FISC_MO_CD,8,2)||substr(COMP_TM.FISC_MO_CD,3,4),'DDMMYYYY') AS TRANS_MO_DATE,
to_number(substr(COMP_TM.FISC_MO_CD,3,4) ) AS PRD_YR_CD,
to_number(substr(COMP_TM.FISC_MO_CD,8,2) ) AS PRD_MO_CD,
CONTR_PRD_TIER_NO,
COMP_TM.FISC_MO_OID AS COMP_MO_BE_ID,
CLSD_YR_FLG,
ADJM_TRANS_CD,
INVC_TM.FISC_MO_OID AS INVC_MO_BE_ID,
ORD_TYP_CD,
TRANS_TM.FISC_MO_OID AS TRANS_MO_BE_ID
from
FACT_DLY_ALGND_SLS F, DIM_TM_MV TRANS_TM,
DIM_TM_MV INVC_TM, DIM_TM_MV COMP_TM
-- WHERE (F.PRD_YR_CD, F.PRD_MO_CD)
WHERE to_char(F.PRD_YR_CD)||'_'||lpad(to_char(F.PRD_MO_CD),2,'0')
in ( /* Logic to extract data for all the subpartions where there is a change*/
select
-- substr(YR_MO,1,4), to_number(substr(YR_MO,6,2))
YR_MO
from
mdm_dba.FACT_MLY_PART_TRUNC
)
AND F.COMP_DT_BE_ID=COMP_TM.BE_ID
AND F.TRANSACTION_DATE = TRANS_TM.DAY_STRT_PRD_OF_TM
AND TRANS_TM.DAY_OID = TRANS_TM.BE_ID
AND F.INVC_DT = INVC_TM.DAY_STRT_PRD_OF_TM
AND INVC_TM.DAY_OID = INVC_TM.BE_ID
group by
CUST_BE_ID ,
DISTR_BE_ID ,
FG_BE_ID ,
KIT_BE_ID ,
BG_ID_NO_BE_ID ,
ACTL_TERR_BE_ID ,
CORE_TERR_BE_ID ,
to_date('15'||substr(COMP_TM.FISC_MO_CD,8,2)||substr(COMP_TM.FISC_MO_CD,3,4),'DDMMYYYY'),
to_number(substr(COMP_TM.FISC_MO_CD,3,4) ),
to_number(substr(COMP_TM.FISC_MO_CD,8,2) ),
CONTR_PRD_TIER_NO,
COMP_TM.FISC_MO_OID ,
CLSD_YR_FLG,
ADJM_TRANS_CD,
INVC_TM.FISC_MO_OID ,
ORD_TYP_CD,
TRANS_TM.FISC_MO_OID

One more issue is there. The statistics gathering activity of FACT_DLY_ALGND_SLS table takes around 5 hours to complete. It is a range partitioned table with subpartitions. Please help about the possible reasons and way outs.

Regards,
Indrajit