Hi
I have a query with will take almost 12:40 min/sec. I want to tune this. Can anyone help me in this regard.
SELECT
stgc_ccr_cc_claim.source_system_code
, ins_home_claim_drv.id
, DECODE(stgc_ccr_cc_claim.retired, 0, 'N', 'Y')
, stgc_ccr_cc_claim.claimnumber
, stgc_ccr_cc_claim.sc_tempaccommodation
, stgc_ccr_cc_claim.policyid
, stgc_ccr_cc_claim.id
, stgc_ccr_cc_claim.state
, stgc_ccr_cc_claim.sc_claimdecision
, stgc_ccr_cc_claim.sc_claimtype
, stgc_ccr_cc_claim.sc_closedoutcome
, stgc_ccr_cc_claim.catastropheid
, stgc_ccr_cc_claim.strategy
, stgc_ccr_cc_claim.losscause
, CASE
WHEN edw_home_claim.lodgement_date IS NOT NULL
THEN CASE WHEN DECODE(stgc_ccr_cctl_sc_yesno.typecode, 'Yes', 'Y', 'N') = 'N'
THEN edw_home_claim.lodgement_date
ELSE NULL
END
ELSE
CASE
WHEN (edw_home_claim.src_id IS NULL
AND DECODE(stgc_ccr_cctl_sc_yesno.typecode, 'Yes', 'Y', 'N') = 'N')
THEN stgc_ccr_cc_claim.reporteddate
WHEN (edw_home_claim.src_id IS NOT NULL
AND DECODE(stgc_ccr_cctl_sc_yesno.typecode, 'Yes', 'Y', 'N') = 'N')
THEN stgc_ccr_cc_claim.updatetime
ELSE NULL
END
END
, stgc_ccr_cc_policylocation.addressid
, stgc_ccr_cc_claim.createtime
, stgc_ccr_cc_claim.updatetime
, stgc_ccr_cc_claim.closedate
, DECODE(stgc_ccr_cctl_sc_yesno.typecode, 'Yes', 'Y', 'N')
, stgc_ccr_cc_incident_latest_v.sc_totaloriginalvalue
, stgc_ccr_cc_claim.cdc_action
, ins_home_claim_drv.cdc_timestamp
, stgc_ccr_cc_claim.mds_stage_acquisition_sk
FROM ins_home_claim_drv ins_home_claim_drv
JOIN stgc_ccr_cc_claim stgc_ccr_cc_claim
ON ins_home_claim_drv.id = stgc_ccr_cc_claim.id
AND ins_home_claim_drv.cdc_timestamp = stgc_ccr_cc_claim.cdc_timestamp
LEFT OUTER JOIN stgc_ccr_cc_policy stgc_ccr_cc_policy
ON stgc_ccr_cc_claim.policyid = stgc_ccr_cc_policy.id
AND ins_home_claim_drv.cdc_timestamp BETWEEN stgc_ccr_cc_policy.dss_start_date AND stgc_ccr_cc_policy.dss_end_date
LEFT OUTER JOIN stgc_ccr_cctl_sc_lineofbusiness stgc_ccr_cctl_sc_lineofbusiness
ON stgc_ccr_cc_policy.sc_lineofbusiness = stgc_ccr_cctl_sc_lineofbusiness.id
AND stgc_ccr_cctl_sc_lineofbusiness.dss_current_flag = 'Y'
LEFT OUTER JOIN stgc_ccr_cctl_sc_yesno stgc_ccr_cctl_sc_yesno
ON stgc_ccr_cc_claim.sc_incidentreport = stgc_ccr_cctl_sc_yesno.id
AND ins_home_claim_drv.cdc_timestamp BETWEEN stgc_ccr_cctl_sc_yesno.dss_start_date AND stgc_ccr_cctl_sc_yesno.dss_end_date
LEFT OUTER JOIN stgc_ccr_cctl_claimstate stgc_ccr_cctl_claimstate
ON stgc_ccr_cc_claim.state = stgc_ccr_cctl_claimstate.id
AND ins_home_claim_drv.cdc_timestamp BETWEEN stgc_ccr_cctl_claimstate.dss_start_date AND stgc_ccr_cctl_claimstate.dss_end_date
LEFT OUTER JOIN stgc_ccr_cctl_losstype stgc_ccr_cctl_losstype
ON stgc_ccr_cc_claim.losstype = stgc_ccr_cctl_losstype.id
AND ins_home_claim_drv.cdc_timestamp BETWEEN stgc_ccr_cctl_losstype.dss_start_date AND stgc_ccr_cctl_losstype.dss_end_date
LEFT OUTER JOIN stgc_ccr_cc_policylocation stgc_ccr_cc_policylocation
on stgc_ccr_cc_claim.sc_policylocationfk = stgc_ccr_cc_policylocation.id
AND ins_home_claim_drv.cdc_timestamp BETWEEN stgc_ccr_cc_policylocation.dss_start_date AND stgc_ccr_cc_policylocation.dss_end_date
AND stgc_ccr_cc_policylocation.retired = 0
LEFT OUTER JOIN edw_home_claim edw_home_claim
ON stgc_ccr_cc_claim.source_system_code = edw_home_claim.source_system_code
AND stgc_ccr_cc_claim.id = edw_home_claim.src_id
AND edw_home_claim.dss_current_flag = 'Y'
LEFT OUTER JOIN stgc_ccr_cc_incident_latest_v stgc_ccr_cc_incident_latest_v
ON stgc_ccr_cc_claim.id = stgc_ccr_cc_incident_latest_v.claimid
AND ins_home_claim_drv.cdc_timestamp BETWEEN stgc_ccr_cc_incident_latest_v.dss_start_date AND stgc_ccr_cc_incident_latest_v.dss_end_date
WHERE stgc_ccr_cctl_sc_lineofbusiness.typecode = 'PI'
AND stgc_ccr_cctl_claimstate.typecode != 'draft'
AND stgc_ccr_cctl_losstype.typecode = 'PR'
;
Thank u
AR