Oracle 10g.
----------------------
Query
UPDATE FACETS_CUSTOM.MMR_DTL
SET
CAPITN_PRCS_IND = 2,
FIL_RUN_DT = Current_fil_run_dt,
ROW_UPDT_DT = dta_cltn_end_dttm
WHERE CAPITN_PRCS_IND = 5
AND HSPC_IND ='Y'
AND EXISTS (SELECT 1
FROM FACETS_STAGE.CRME_FUND_DTL_STG STG_CRME
WHERE STG_CRME.MBR_CK = MMR_DTL.MBRSHP_CK
AND MMR_DTL.PMT_MSA_STRT_DT BETWEEN STG_CRME.ERN_FROM_DT AND STG_CRME.ERN_THRU_DT
AND STG_CRME.FUND_ID IN ('AAB1', '1AA2', '1BA2', 'AAB2', '1AA3', '1BA3', '1B80', '1A80') )
AND EXISTS (SELECT 1
FROM FACETS_CUSTOM.FCTS_TMS_MBRID_XWLK XWLK
WHERE XWLK.MBR_CK = MMR_DTL.MBRSHP_CK
AND MMR_DTL.PMT_MSA_STRT_DT BETWEEN XWLK.HSPC_EVNT_EFF_DT AND XWLK.HSPC_EVNT_TERM_DT);
Explain plan of the Query
-----------------------------------------------
Plan hash value: 3109991485
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | 148 | 12431 (2)| 00:02:30 |
| 1 | UPDATE | MMR_DTL | | | | |
| 2 | NESTED LOOPS SEMI | | 1 | 148 | 12431 (2)| 00:02:30 |
|* 3 | HASH JOIN RIGHT SEMI | | 49 | 5488 | 12375 (2)| 00:02:29 |
| 4 | TABLE ACCESS FULL | FCTS_TMS_MBRID_XWLK | 6494 | 64940 | 24 (0)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | MMR_DTL | 304K| 29M| 12347 (2)| 00:02:29 |
|* 6 | TABLE ACCESS BY INDEX ROWID| CRME_FUND_DTL_STG | 1 | 36 | 5 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | IE1_CRME_FUND_DTL_STG | 8 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("XWLK"."MBR_CK"="MMR_DTL"."MBRSHP_CK")
filter("XWLK"."HSPC_EVNT_EFF_DT"<=INTERNAL_FUNCTION("MMR_DTL"."PMT_MSA_STRT_DT") AND
"XWLK"."HSPC_EVNT_TERM_DT">=INTERNAL_FUNCTION("MMR_DTL"."PMT_MSA_STRT_DT"))
5 - filter("CAPITN_PRCS_IND"=5 AND "HSPC_IND"='Y')
6 - filter(("STG_CRME"."FUND_ID"='1A80' OR "STG_CRME"."FUND_ID"='1AA2' OR
"STG_CRME"."FUND_ID"='1AA3' OR "STG_CRME"."FUND_ID"='1B80' OR "STG_CRME"."FUND_ID"='1BA2' OR
"STG_CRME"."FUND_ID"='1BA3' OR "STG_CRME"."FUND_ID"='AAB1' OR "STG_CRME"."FUND_ID"='AAB2') AND
"STG_CRME"."ERN_FROM_DT"<=INTERNAL_FUNCTION("MMR_DTL"."PMT_MSA_STRT_DT") AND
"STG_CRME"."ERN_THRU_DT">=INTERNAL_FUNCTION("MMR_DTL"."PMT_MSA_STRT_DT"))
7 - access("STG_CRME"."MBR_CK"="MMR_DTL"."MBRSHP_CK")
I couldnt optimize this query for better performance and optimized cost.. Can some one guide me on this.
Thanks,
DS