Forum Stats

  • 3,759,245 Users
  • 2,251,515 Discussions
  • 7,870,550 Comments

Discussions

performance tuning

n1248
n1248 Member Posts: 3 Green Ribbon

I have a report that is taking hours to run when people use long time spans for the dates. I've tried a few things but cannot get it to complete faster. Any help is very appreciated. I've attached the query and the execution plan.

WITH cteq_vars AS (

  SELECT 

    TO_DATE(TO_CHAR(:From_Date, 'mm/dd/yyyy'), 'mm/dd/yyyy') From_Dt,

    TO_DATE(TO_CHAR(:to_date, 'mm/dd/yyyy'), 'mm/dd/yyyy') To_Dt,

    CASE WHEN NVL(UPPER(TRIM(:account_no)), '* ALL *') = ''

      THEN '* ALL *'

      ELSE NVL(UPPER(TRIM(:account_no)), '* ALL *')

    END account_number,  

    CASE WHEN :Paid_From = '' OR :Paid_From IS NULL

      THEN NULL

      ELSE TO_DATE(:Paid_From, 'mm/dd/yyyy')

    END Paid_From,

    CASE WHEN :Paid_To = '' OR :Paid_To IS NULL

      THEN NULL

      ELSE TO_DATE(:Paid_To, 'mm/dd/yyyy')

    END Paid_To,

    CASE WHEN :Rsrv_Date = '' OR :Rsrv_Date IS NULL

      THEN TRUNC(SYSDATE)

      ELSE TO_DATE(:Rsrv_Date, 'mm/dd/yyyy')

    END reserve_date

  FROM DUAL

),


cteq_max_pob_no AS (

  SELECT   

    MAX(ipob1.injr_pob_no) injr_pob_no,

    ipob1.injr_id injr_id,

    UPPER(TRIM(ipob1.pob_cd)) pob_cd,

    UPPER(TRIM(ipob1.pob_loc_cd)) pob_loc_cd

  FROM   

    INJURY_PART_OF_BODY ipob1

  WHERE   

    UPPER(TRIM(ipob1.injr_pob_pri_ind)) = 'Y'

  GROUP BY 

    ipob1.injr_id,

    ipob1.pob_cd,

    ipob1.pob_loc_cd 

),


cteq_1 AS (

  SELECT 

    c.injr_id injr_id,

    TRIM(UPPER(TRIM(c.clm_no))|| ' ' ||

      CASE UPPER(TRIM(cthvc.clm_typ_cd))

        WHEN 'WAGEL' THEN 'T'

        WHEN 'MED' THEN 'N'

        WHEN 'X' THEN 'X'

        ELSE NULL 

      END

    ) "Claim Number",

     

    CASE WHEN UPPER(TRIM(c.CLM_NDWC_INTL_IND)) = 'Y'

      THEN ''

      ELSE INITCAP(TRIM(ph.prsn_hist_nm_lst)) || ', ' || INITCAP(TRIM(ph.prsn_hist_nm_fst))

    END "IW Name",

    INITCAP(TRIM(NVL(ot.occup_typ_nm, 'Unknown'))) "Occupation",

    UPPER(TRIM(ph.gndr_cd)) "Claimant Gender",

    TRUNC(MONTHS_BETWEEN(TRUNC(i.injr_dtm), TRUNC(ph.prsn_hist_brth_dt)) / 12, 0) "Claimant Age",

    TRUNC(i.injr_dtm) "Injury Date",

    TO_CHAR(i.injr_dtm, 'HH:MI AM') "Injury Time",

    TRIM(REPLACE(REPLACE(REPLACE(i.INJR_ACDN_DESC_TEXT, CHR(10), ' '), CHR(13), ' '), CHR(9), ' ')) "Injury Description",

    INITCAP(TRIM(cs.CLM_STS_NM)) "Claim Status",

    UPPER(TRIM(ipob.pob_cd)) || ' - ' || INITCAP(TRIM(pob.pob_nm)) "Primary Body Part",

    UPPER(TRIM(ipob.POB_LOC_CD)) "Body Side",

    UPPER(TRIM(i.noi_cd)) || ' - ' || INITCAP(TRIM(noi.noi_nm)) "Nature of Injury",

    UPPER(TRIM(si.INJR_SRC_CD)) || ' - ' || INITCAP(TRIM(si.INJR_SRC_NM)) "Injury Cause",

    pb.plcy_no "Account Number",

    INITCAP(TRIM(NVL(le.lgl_enty_drv_nm_alt, le.lgl_enty_drv_nm))) "Employer",

    TRUNC(c.clm_drv_fst_rpt_dt) "WSI Notify Date",

    NVL(c.clm_clmt_empl_no_of_yrs, 0) "Years",

    NVL(c.clm_clmt_empl_no_of_mm, 0) "Months",

    NVL(c.clm_clmt_empl_no_of_dd, 0) "Days",

    UPPER(TRIM(c.empl_cls_cd)) || ' - ' || INITCAP(TRIM(ec.EMPL_CLS_NM)) "Rate Class",

    vars.From_Dt vars_From_Dt,

    vars.To_Dt vars_To_Dt,

    vars.account_number account_number,

    vars.Paid_From vars_Paid_From,

    vars.Paid_To vars_Paid_To,

    vars.reserve_date reserve_date

     

  FROM   

    cteq_vars vars

     

    INNER JOIN INJURY i

    ON TRUNC(i.injr_dtm) BETWEEN vars.From_Dt AND vars.To_Dt

     

    INNER JOIN CLAIM c

    ON c.injr_id = i.injr_id 

    AND c.clm_clmt_empl_no_of_yrs IS NOT NULL

     

    INNER JOIN POLICY_BUSINESS pb

    ON pb.lgl_enty_id_busn = i.lgl_enty_id_emplr

    AND (

      LPAD(UPPER(TRIM(pb.plcy_no)), 7, '0') = LPAD(UPPER(TRIM(vars.account_number)), 7, '0')

      OR vars.account_number = '* ALL *'

    )

    AND c.clm_clmt_empl_no_of_yrs < 200

     

    INNER JOIN CLAIM_STATUS_HISTORY_VIEW_CUR cshv

    ON cshv.injr_id = i.injr_id

    AND cshv.clm_sts_cd != 'x' 

     

    INNER JOIN CLAIM_TYPE_HISTORY_VIEW_CUR cthvc

    ON cthvc.injr_id = i.injr_id 

     

    INNER JOIN PERSON_HISTORY ph

    ON ph.lgl_enty_id_prsn = i.lgl_enty_id_clmt 

    AND ph.prsn_hist_end_dt IS NULL 

     

    INNER JOIN CLAIM_STATUS cs

    ON UPPER(TRIM(cs.CLM_STS_CD)) = UPPER(TRIM(cshv.clm_sts_cd)) 

     

    LEFT OUTER JOIN EMPLOYMENT_CLASS ec

    ON UPPER(TRIM(ec.EMPL_CLS_CD)) = UPPER(TRIM(c.empl_cls_cd))

     

    INNER JOIN LEGAL_ENTITY le

    ON le.LGL_ENTY_ID = i.lgl_enty_id_emplr

     

    LEFT OUTER JOIN NATURE_OF_INJURY noi

    ON UPPER(TRIM(noi.NOI_CD)) = UPPER(TRIM(i.NOI_CD)) 

     

    LEFT OUTER JOIN OCCUPATION_TYPE ot

    ON UPPER(TRIM(ot.occup_typ_cd)) = UPPER(TRIM(i.occup_typ_cd))       

     

    LEFT OUTER JOIN cteq_max_pob_no ipob

    ON ipob.injr_id = i.injr_id

     

    LEFT OUTER JOIN PART_OF_BODY pob

    ON UPPER(TRIM(pob.pob_cd)) = UPPER(TRIM(ipob.pob_cd)) 

     

    LEFT OUTER JOIN INJURY_SOURCE si

    ON UPPER(TRIM(si.INJR_SRC_CD)) = UPPER(TRIM(i.INJR_SRC_CD))

     

  UNION ALL

   

  SELECT 

    c.injr_id injr_id,

    TRIM(UPPER(TRIM(c.clm_no)) || ' ' || 

      CASE UPPER(TRIM(cthvc.clm_typ_cd))

        WHEN 'WAGEL' THEN 'T'

        WHEN 'MED' THEN 'N'

        WHEN 'X' THEN 'X'

        ELSE NULL 

      END

    ) "Claim Number",

     

    CASE WHEN UPPER(TRIM(c.CLM_NDWC_INTL_IND)) = 'Y'

      THEN ''

      ELSE INITCAP(TRIM(ph.prsn_hist_nm_lst)) || ', ' || INITCAP(TRIM(ph.prsn_hist_nm_fst))

    END "IW Name",

    INITCAP(TRIM(NVL(ot.occup_typ_nm, 'Unknown'))) "Occupation",

    UPPER(TRIM(ph.gndr_cd)) "Claimant Gender",

    TRUNC(MONTHS_BETWEEN (TRUNC(i.injr_dtm), TRUNC(ph.prsn_hist_brth_dt)) / 12, 0) "Claimant Age",

    TRUNC(i.injr_dtm) "Injury Date",

    TO_CHAR(i.injr_dtm, 'HH:MI AM') "Injury Time",

    TRIM(REPLACE(REPLACE(REPLACE(REPLACE(i.injr_acdn_desc_text, CHR (10), ' '), CHR (13), ' '), CHR (9), ' '), '"', '''''')) "Injury Description",

    INITCAP(TRIM(cs.CLM_STS_NM)) "Claim Status",

    UPPER(TRIM(ipob.pob_cd)) || ' - ' || INITCAP(TRIM(pob.pob_nm)) "Primary Body Part",

    UPPER(TRIM(ipob.POB_LOC_CD)) "Body Side",

    UPPER(TRIM(i.noi_cd)) || ' - ' || INITCAP(TRIM(noi.noi_nm)) "Nature of Injury",

    UPPER(TRIM(si.INJR_SRC_CD)) || ' - ' || INITCAP(TRIM(si.INJR_SRC_NM)) "Injury Cause",

    pb.plcy_no "Account Number",

    INITCAP(TRIM(NVL(le.lgl_enty_drv_nm_alt, le.lgl_enty_drv_nm))) "Employer",

    TRUNC(c.clm_drv_fst_rpt_dt) "WSI Notify Date",

    TRUNC(MONTHS_BETWEEN(i.injr_dtm, TO_DATE(GREATEST(NVL(c.clm_clmt_empl_no_of_dd, 0), 1) || '/' || GREATEST(NVL(c.clm_clmt_empl_no_of_mm, 0), 1) || '/' || NVL(c.clm_clmt_empl_no_of_yrs, 0), 'DD/MM/YYYY')) / 12) "Years",

    (

        TRUNC(MONTHS_BETWEEN(i.injr_dtm, TO_DATE(GREATEST(NVL(c.clm_clmt_empl_no_of_dd, 0), 1) || '/' || GREATEST(NVL(c.clm_clmt_empl_no_of_mm, 0), 1) || '/' || NVL(c.clm_clmt_empl_no_of_yrs, 0), 'DD/MM/YYYY'))

      - TRUNC((MONTHS_BETWEEN(i.injr_dtm, TO_DATE(GREATEST(NVL(c.clm_clmt_empl_no_of_dd, 0), 1) || '/' || GREATEST(NVL(c.clm_clmt_empl_no_of_mm, 0), 1) || '/' || NVL(c.clm_clmt_empl_no_of_yrs, 0), 'DD/MM/YYYY')) / 12)) * 12)

    ) "Months",    

    (

      TRUNC(i.injr_dtm) 

      - TRUNC(ADD_MONTHS(TO_DATE(GREATEST(NVL(c.clm_clmt_empl_no_of_dd, 0), 1) || '/' || GREATEST(NVL(c.clm_clmt_empl_no_of_mm, 0), 1) || '/' || NVL(c.clm_clmt_empl_no_of_yrs, 0), 'DD/MM/YYYY'), MONTHS_BETWEEN(i.injr_dtm, TO_DATE(GREATEST(NVL(c.clm_clmt_empl_no_of_dd, 0), 1) || '/' || GREATEST(NVL(c.clm_clmt_empl_no_of_mm, 0), 1) || '/' || NVL(c.clm_clmt_empl_no_of_yrs, 0), 'DD/MM/YYYY'))))

    ) "Days",

    UPPER(TRIM(c.empl_cls_cd)) || ' - ' || INITCAP(TRIM(ec.EMPL_CLS_NM)) "Rate Class",

    vars.From_Dt vars_From_Dt,

    vars.To_Dt vars_To_Dt,

    vars.account_number account_number,

    vars.Paid_From vars_Paid_From,

    vars.Paid_To vars_Paid_To,

    vars.reserve_date reserve_date

     

  FROM   

    cteq_vars vars

     

    INNER JOIN INJURY i

    ON Trunc(i.injr_dtm) BETWEEN vars.From_Dt AND vars.To_Dt

     

    INNER JOIN CLAIM c

    ON c.injr_id = i.injr_id 

    AND c.clm_clmt_empl_no_of_yrs IS NOT NULL

    AND c.clm_clmt_empl_no_of_yrs >= 200

     

    INNER JOIN POLICY_BUSINESS pb

    ON pb.lgl_enty_id_busn = i.lgl_enty_id_emplr

    AND (

      LPAD(UPPER(TRIM(pb.plcy_no)), 7, '0') = LPAD(UPPER(TRIM(vars.account_number)), 7, '0')

      OR vars.account_number = '* ALL *'

    )

     

    INNER JOIN CLAIM_TYPE_HISTORY_VIEW_CUR cthvc

    ON cthvc.injr_id = i.injr_id

     

    INNER JOIN CLAIM_STATUS_HISTORY_VIEW_CUR cshv

    ON cshv.injr_id = i.injr_id 

    AND UPPER(TRIM(cshv.clm_sts_cd)) NOT IN ('X')

     

    LEFT OUTER JOIN NATURE_OF_INJURY noi

    ON noi.noi_cd = i.noi_cd

     

    INNER JOIN CLAIM_STATUS cs

    ON UPPER(TRIM(cs.clm_sts_cd)) = UPPER(TRIM(cshv.clm_sts_cd))

     

    INNER JOIN PERSON_HISTORY ph

    ON ph.lgl_enty_id_prsn = i.lgl_enty_id_clmt 

    AND ph.prsn_hist_end_dt IS NULL

     

    INNER JOIN LEGAL_ENTITY le

    ON i.lgl_enty_id_emplr = le.lgl_enty_id

        

    LEFT OUTER JOIN EMPLOYMENT_CLASS ec

    ON UPPER(TRIM(ec.empl_cls_cd)) = UPPER(TRIM(c.empl_cls_cd))

     

    LEFT OUTER JOIN INJURY_SOURCE si

    ON UPPER(TRIM(si.injr_src_cd)) = UPPER(TRIM(i.injr_src_cd))

     

    LEFT OUTER JOIN OCCUPATION_TYPE ot

    ON UPPER(TRIM(ot.occup_typ_cd)) = UPPER(TRIM(i.occup_typ_cd))

     

    LEFT OUTER JOIN cteq_max_pob_no ipob

    ON ipob.injr_id = i.injr_id

     

    LEFT OUTER JOIN PART_OF_BODY pob

    ON UPPER(TRIM(pob.pob_cd)) = UPPER(TRIM(ipob.pob_cd))

     

  UNION ALL

   

  SELECT 

    c.injr_id injr_id,

    TRIM(UPPER(TRIM(c.clm_no)) || ' ' || 

      CASE UPPER(TRIM(cthvc.clm_typ_cd))

        WHEN 'WAGEL' THEN 'T'

        WHEN 'MED' THEN 'N'

        WHEN 'X' THEN 'X'

        ELSE NULL 

      END

    ) "Claim Number",

     

    CASE WHEN UPPER(TRIM(c.CLM_NDWC_INTL_IND)) = 'Y'

      THEN ''

      ELSE INITCAP(TRIM(ph.prsn_hist_nm_lst)) || ', ' || INITCAP(TRIM(ph.prsn_hist_nm_fst))

    END "IW Name",

    INITCAP(TRIM(NVL(ot.occup_typ_nm, 'Unknown'))) "Occupation",

    UPPER(TRIM(ph.gndr_cd)) "Claimant Gender",

    TRUNC(MONTHS_BETWEEN (TRUNC(i.injr_dtm), TRUNC(ph.prsn_hist_brth_dt)) / 12, 0) "Claimant Age",

    TRUNC(i.injr_dtm) "Injury Date",

    TO_CHAR(i.injr_dtm, 'HH:MI AM') "Injury Time",

    TRIM(REPLACE(REPLACE(REPLACE(REPLACE(i.injr_acdn_desc_text, CHR (10), ' '), CHR (13), ' '), CHR (9), ' '), '"', '''''')) "Injury Description",

    INITCAP(TRIM(cs.CLM_STS_NM)) "Claim Status",

    UPPER(TRIM(ipob.pob_cd)) || ' - ' || INITCAP(TRIM(pob.pob_nm)) "Primary Body Part",

    UPPER(TRIM(ipob.POB_LOC_CD)) "Body Side",

    UPPER(TRIM(i.noi_cd)) || ' - ' || INITCAP(TRIM(noi.noi_nm)) "Nature of Injury",

    UPPER(TRIM(si.INJR_SRC_CD)) || ' - ' || INITCAP(TRIM(si.INJR_SRC_NM)) "Injury Cause",

    pb.plcy_no "Account Number",

    INITCAP(TRIM(NVL(le.lgl_enty_drv_nm_alt, le.lgl_enty_drv_nm))) "Employer",

    TRUNC(c.clm_drv_fst_rpt_dt) "WSI Notify Date",

    NVL(c.clm_clmt_empl_no_of_yrs, 0) "Years",

    NVL(c.clm_clmt_empl_no_of_mm, 0) "Months",

    NVL(c.clm_clmt_empl_no_of_dd, 0) "Days",

    UPPER(TRIM(c.empl_cls_cd)) || ' - ' || INITCAP(TRIM(ec.EMPL_CLS_NM)) "Rate Class",

    vars.From_Dt vars_From_Dt,

    vars.To_Dt vars_To_Dt,

    vars.account_number account_number,

    vars.Paid_From vars_Paid_From,

    vars.Paid_To vars_Paid_To,

    vars.reserve_date reserve_date

     

  FROM 

    cteq_vars vars

     

    INNER JOIN INJURY i

    ON Trunc(i.injr_dtm) BETWEEN vars.From_Dt AND vars.To_Dt

     

    INNER JOIN CLAIM c

    ON c.injr_id = i.injr_id 

    AND c.clm_clmt_empl_no_of_yrs IS NULL

     

    INNER JOIN POLICY_BUSINESS pb

    ON pb.lgl_enty_id_busn = i.lgl_enty_id_emplr

    AND (

      LPAD(UPPER(TRIM(pb.plcy_no)), 7, '0') = LPAD(UPPER(TRIM(vars.account_number)), 7, '0')

      OR vars.account_number = '* ALL *'

    ) 

         

    INNER JOIN CLAIM_TYPE_HISTORY_VIEW_CUR cthvc

    ON cthvc.injr_id = i.injr_id

     

    INNER JOIN CLAIM_STATUS_HISTORY_VIEW_CUR cshv

    ON cshv.injr_id = i.injr_id 

    AND UPPER(TRIM(cshv.clm_sts_cd)) NOT IN ('X')

     

    LEFT OUTER JOIN NATURE_OF_INJURY noi

    ON UPPER(TRIM(noi.noi_cd)) = UPPER(TRIM(i.noi_cd))

     

    INNER JOIN CLAIM_STATUS cs

    ON UPPER(TRIM(cs.clm_sts_cd)) = UPPER(TRIM(cshv.clm_sts_cd))

     

    INNER JOIN PERSON_HISTORY ph

    ON ph.lgl_enty_id_prsn = i.lgl_enty_id_clmt 

    AND ph.prsn_hist_end_dt IS NULL

     

    INNER JOIN LEGAL_ENTITY le

    ON i.lgl_enty_id_emplr = le.lgl_enty_id

         

    LEFT OUTER JOIN EMPLOYMENT_CLASS ec

    ON UPPER(TRIM(ec.empl_cls_cd)) = UPPER(TRIM(c.empl_cls_cd))

     

    LEFT OUTER JOIN INJURY_SOURCE si

    ON UPPER(TRIM(si.injr_src_cd)) = UPPER(TRIM(i.injr_src_cd))

     

    LEFT OUTER JOIN OCCUPATION_TYPE ot

    ON UPPER(TRIM(ot.occup_typ_cd)) = UPPER(TRIM(i.occup_typ_cd))

     

    LEFT OUTER JOIN cteq_max_pob_no ipob

    ON ipob.injr_id = i.injr_id

     

    LEFT OUTER JOIN PART_OF_BODY pob

    ON UPPER(TRIM(pob.pob_cd)) = UPPER(TRIM(ipob.pob_cd))

     

  ORDER BY 1

),


cteq_2_1 AS (

  SELECT 

    cft.injr_id,

    UPPER(TRIM(asda.bnft_typ_cd)) bnft,

    ((Trunc(asd.admis_sch_dtl_drv_prd_end_dt) - Trunc(asd.admis_sch_dtl_drv_prd_eff_dt)) + 1 ) ttdays,

    0 tpdays

     

  FROM   

    cteq_1 cte1

     

    INNER JOIN CLAIM_FINANCIAL_TRAN cft

    ON cft.injr_id = cte1.injr_id

    AND UPPER(TRIM(cft.cft_typ_cd)) = 'INDM_PAY_DUE' 

    AND UPPER(TRIM(cft.bnft_typ_cd)) IN ('TT', 'ABP', 'PT', 'RHBT', 'RHBP') 

    AND (

      (cte1.vars_Paid_From IS NULL OR TRUNC(cft.cft_dt) >= cte1.vars_Paid_From)

      AND 

      (cte1.vars_Paid_To IS NULL OR TRUNC(cft.cft_dt) <= cte1.vars_Paid_To)

    )

    INNER JOIN ADMISSION_SCHEDULE_DETAIL_AMT asda

    ON cft.cft_id = asda.cft_id 

     

    INNER JOIN ADMISSION_SCHEDULE_DETAIL asd

    ON asd.admis_sch_id = asda.admis_sch_id 

    AND asd.admis_sch_dtl_no = asda.admis_sch_dtl_no 

     

    INNER JOIN ADMISSION_PERIOD ap

    ON asd.admis_sch_id = ap.admis_sch_id 

    AND UPPER(TRIM(ap.admis_prd_void_ind)) = 'N' 

     

    INNER JOIN ADMISSION_PERIOD_RATE_HISTORY aprh

    ON aprh.injr_id = ap.injr_id 

    AND aprh.admis_no = ap.admis_no 

    AND aprh.admis_prd_no = ap.admis_prd_no 

    AND UPPER(TRIM(aprh.admis_prd_rt_hist_void_ind)) = 'N' 

    AND aprh.admis_prd_rt_hist_no = (SELECT Max(aprh2.admis_prd_rt_hist_no)

                     FROM ADMISSION_PERIOD_RATE_HISTORY aprh2

                     WHERE aprh2.injr_id = aprh.injr_id 

                     AND aprh2.admis_no = aprh.admis_no 

                     AND aprh2.admis_prd_no = aprh.admis_prd_no)

),


cteq_2_2 AS (

  SELECT 

    cft.injr_id injr_id,

    UPPER(TRIM(asda.bnft_typ_cd)) bnft,

    0 ttdays,

    ((Trunc(asd.admis_sch_dtl_drv_prd_end_dt) - Trunc(asd.admis_sch_dtl_drv_prd_eff_dt)) + 1) tpdays

     

  FROM 

    cteq_1 cte1

     

    INNER JOIN CLAIM_FINANCIAL_TRAN cft

    ON cft.injr_id = cte1.injr_id

    AND UPPER(TRIM(cft.cft_typ_cd)) = 'INDM_PAY_DUE' 

    AND UPPER(TRIM(cft.bnft_typ_cd)) IN ('PP', 'PDB', 'PRB', 'TP') 

    AND (

      (cte1.vars_Paid_From IS NULL OR TRUNC(cft.cft_dt) >= cte1.vars_Paid_From)

      AND 

      (cte1.vars_Paid_To IS NULL OR TRUNC(cft.cft_dt) <= cte1.vars_Paid_To)

    )

    INNER JOIN ADMISSION_PERIOD ap

    ON ap.injr_id = cte1.injr_id

    AND UPPER(TRIM(ap.admis_prd_void_ind)) = 'N' 

     

    INNER JOIN ADMISSION_PERIOD_RATE_HISTORY aprh

    ON aprh.injr_id = ap.injr_id 

    AND aprh.admis_no = ap.admis_no 

    AND aprh.admis_prd_no = ap.admis_prd_no

    AND UPPER(TRIM(aprh.admis_prd_rt_hist_void_ind)) = 'N' 

    AND aprh.admis_prd_rt_hist_no = (SELECT Max(aprh2.admis_prd_rt_hist_no)

                     FROM ADMISSION_PERIOD_RATE_HISTORY aprh2

                     WHERE aprh2.injr_id = aprh.injr_id 

                     AND aprh2.admis_no = aprh.admis_no 

                     AND aprh2.admis_prd_no = aprh.admis_prd_no) 

     

    INNER JOIN ADMISSION_SCHEDULE_DETAIL asd

    ON asd.admis_sch_id = ap.admis_sch_id 

     

    INNER JOIN ADMISSION_SCHEDULE_DETAIL_AMT asda

    ON asda.cft_id = cft.cft_id

    AND asda.admis_sch_id = asd.admis_sch_id 

    AND asda.admis_sch_dtl_no = asd.admis_sch_dtl_no 

),


cteq_2_union AS (

  SELECT injr_id       injr_id,

      bnft        bnft,

      ttdays       ttdays,

      tpdays       tpdays

  FROM cteq_2_1

   

  UNION ALL

   

  SELECT injr_id       injr_id,

      bnft        bnft,

      ttdays       ttdays,

      tpdays       tpdays

   FROM cteq_2_2

),


cteq_2 AS (

  SELECT 

    injr_id       injr_id,

    SUM(ttdays)   ttdays,

    SUM(tpdays)   tpdays

  FROM cteq_2_union

  GROUP BY injr_id

  ORDER BY injr_id

),


cteq_3_1 AS (

  SELECT cft.injr_id injr_id,

  TO_NUMBER(NVL(TO_CHAR(cft.cft_amt), 0)) cmppay,

  0 medpay,

  cft.cft_typ_cd,

  cft.cft_dt,

  vars.From_Dt,

  vars.reserve_date,

  cft.cft_id 

   

  FROM 

    cteq_1 cte1

     

    CROSS JOIN cteq_vars vars

     

    INNER JOIN CLAIM_FINANCIAL_TRAN cft 

    ON cft.injr_id = cte1.injr_id

    AND UPPER(TRIM(cft.cft_typ_cd)) = 'INDM_PAY_DUE' 

    AND Trunc(cft.cft_dt) >= vars.From_Dt 

    AND (

      (Trunc(cft.cft_dt) <= TRUNC(SYSDATE) AND vars.reserve_date IS NULL)

      OR 

      (Trunc(cft.cft_dt) <= TRUNC(vars.reserve_date) AND vars.reserve_date IS NOT NULL) 

    )

    AND (

      (cte1.vars_Paid_From IS NULL OR TRUNC(cft.cft_dt) >= cte1.vars_Paid_From)

      AND 

      (cte1.vars_Paid_To IS NULL OR TRUNC(cft.cft_dt) <= cte1.vars_Paid_To)

    )

),


cteq_3_2 AS (

  SELECT 

    cft.injr_id injr_id,

    0 cmppay,

    TO_NUMBER(Nvl(TO_CHAR(cft.cft_amt), 0)) medpay

  FROM 

    cteq_1 cte1

     

    CROSS JOIN cteq_vars vars

     

    INNER JOIN CLAIM_FINANCIAL_TRAN cft

    ON cft.injr_id = cte1.injr_id

    AND UPPER(TRIM(cft.cft_typ_cd)) = 'MED_PAY_DUE' 

    AND Trunc(cft.cft_dt) >= vars.From_Dt 

    AND (

      (Trunc(cft.cft_dt) <= TRUNC(SYSDATE) AND vars.reserve_date IS NULL)

      OR 

      (Trunc(cft.cft_dt) <= TRUNC(vars.reserve_date) AND vars.reserve_date IS NOT NULL) 

    )

    AND (

      (cte1.vars_Paid_From IS NULL OR TRUNC(cft.cft_dt) >= cte1.vars_Paid_From)

      AND 

      (cte1.vars_Paid_To IS NULL OR TRUNC(cft.cft_dt) <= cte1.vars_Paid_To)

    )

),

cteq_3_union

AS

(

  SELECT 

    injr_id injr_id,

    cmppay cmppay,

    medpay medpay

  FROM cteq_3_1

   

  UNION ALL

   

  SELECT 

    injr_id injr_id,

    cmppay cmppay,

    medpay medpay

  FROM cteq_3_2

),


cteq_3 AS (

  SELECT 

    injr_id injr_id,

    SUM(cmppay) cmppay,

    SUM(medpay) medpay

   FROM cteq_3_union

   GROUP BY injr_id

),


cteq_rsrv_1 AS (

  SELECT 

    cte1."Claim Number" "Claim Number",

    a.injr_id injr_id,

    SUM(a.rem_no_time_loss) NTLMed,

    SUM(a.rem_time_loss) TLMed,

    SUM(a.rem_comp_rsrv) TLRV,

    SUM(a.rem_sup_rsrv) SUPRV

     

  FROM   

    cteq_1 cte1 

     

    INNER JOIN REMAINING_RESERVES_HISTORY_TMP a

    ON a.injr_id = cte1.injr_id   

    AND a.rem_rsrv_hist_date = (SELECT MAX(b.rem_rsrv_hist_date) max_hist_date

                  FROM REMAINING_RESERVES_HISTORY_TMP b

                  WHERE b.injr_id = a.injr_id 

                  AND (

                    (TRUNC(b.rem_rsrv_hist_date) <= TRUNC(SYSDATE) AND cte1.reserve_date IS NULL) 

                    OR

                    (TRUNC(b.rem_rsrv_hist_date) <= cte1.reserve_date AND cte1.reserve_date IS NOT NULL) 

                  ) 

    ) 

     

  GROUP BY   

    cte1."Claim Number",

    a.injr_id

     

  ORDER BY  cte1."Claim Number"

),


cteq_rsrv AS (

  SELECT injr_id,

    GREATEST(0, NVL(cte1.TLRV, 0)) + GREATEST(0, NVL(cte1.SupRV, 0)) CMP_RV,

    GREATEST(0, NVL(cte1.NTLMed, 0)) + GREATEST(0, NVL(cte1.TLMed, 0)) MED_RV

   FROM cteq_rsrv_1 cte1

),


cteq_paid AS (

  SELECT 

    cte1.injr_id    injr_id, 

    cte3.cmppay     cmppay,

    cter.CMP_RV     CMP_RV,

    cte3.medpay     medpay,

    cter.MED_RV     MED_RV

     

  FROM 

    cteq_1 cte1

     

    LEFT OUTER JOIN cteq_3 cte3

    ON cte3.injr_id = cte1.injr_id

     

    LEFT OUTER JOIN cteq_rsrv cter

    ON cter.injr_id = cte1.injr_id   

),


cteq_outputfull1 AS (

  SELECT 

    cte1.injr_id injr_id,

    cte1."Claim Number" "Claim Number",

    cte1."IW Name" "IW Name",

    cte1."Occupation" "Occupation",

    cte1."Claimant Gender" "Claimant Gender",

    cte1."Claimant Age" "Claimant Age",

    cte1."Injury Date" "Injury Date",

    cte1."Injury Time" "Injury Time",

    cte1."Injury Description" "Injury Description",

    cte1."Claim Status" "Claim Status",

    cte1."Primary Body Part" "Primary Body Part",

    cte1."Body Side" "Body Side",

    cte1."Nature of Injury" "Nature of Injury",

    cte1."Injury Cause" "Injury Cause",

    cte1."Account Number" "Account Number",

    cte1."Employer" "Employer",

    cte1."WSI Notify Date" "WSI Notify Date",

    cte1."Years" "Years",

    cte1."Months" "Months",

    cte1."Days" "Days",

    cte1."Rate Class" "Rate Class",

    NVL(cte2.ttdays, 0) ttdays,

    NVL(cte2.tpdays, 0) tpdays,

    NVL(ctep.cmppay, 0) cmppay,

    NVL(ctep.CMP_RV, 0) CMP_RV,

    NVL(ctep.medpay, 0) medpay,

    NVL(ctep.MED_RV, 0) MED_RV,

    cte1.vars_From_Dt vars_From_Dt,

    cte1.vars_To_Dt vars_To_Dt,

    cte1.vars_Paid_From vars_Paid_From,

    cte1.vars_Paid_To vars_Paid_To

   FROM 

    cteq_1 cte1

     

    LEFT OUTER JOIN cteq_2 cte2

    ON cte1.injr_id = cte2.injr_id

     

    LEFT OUTER JOIN cteq_paid ctep

    ON ctep.injr_id = cte1.injr_id

)


SELECT 

  "Claim Number"       "Claim Number",   

  "IW Name" "Claimant Name",

  "Claimant Age" "Claimant Age",

  "Claimant Gender" "Claimant Gender",

  "Occupation" "Occupation",

  "Years" "Employment Years",

  "Months" "Employment Months",

  "Days" "Employment Days",

  "WSI Notify Date" "WSI Notify Date",

  "Injury Date" "Injury Date",

  "Injury Time" "Injury Time",

  "Injury Description" "Injury Description",

  "Claim Status" "Claim Status",   

  "Account Number" "Account Number",       

  "Employer" "Employer",

  "Rate Class" "Rate Class",   

  "Primary Body Part" "Primary Body Part", 

  "Body Side" "Body Side",

  "Injury Cause" "Injury Cause",

  "Nature of Injury" "Nature of Injury",

  cmppay "Comp Paid",     

  medpay "Med Paid",     

  CMP_RV "Comp Reserve",   

  MED_RV "Med Reserve",   

  cmppay + medpay + CMP_RV + MED_RV "Total Incurred", 

  ttdays + tpdays "Total TL Days"

   

FROM cteq_outputfull1


ORDER BY "Claim Number"


/**/

Tagged:

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,063 Red Diamond

    Hi, @n1248

     TO_DATE(TO_CHAR(:From_Date, 'mm/dd/yyyy'), 'mm/dd/yyyy') From_Dt,

    TO_DATE (TO_CHAR is almost always a mistake. What is the data type of :from_date? If it is a DATE, then you don't need any functions. (Note that bind variables can't be DATEs in SQL*Plus.) If :from_date is a string, then just use TO_DATE.


    Comparisons like this:

     ON UPPER(TRIM(cs.CLM_STS_CD)) = UPPER(TRIM(cshv.clm_sts_cd)) 

    (which you use in several places) are very inefficient. A lot of guys would have a CHECK constraint (and maybe a trigger) to make sure that clm_sts_cd = UPPER (TRIM (clm_sts_cd)) in both tables. Then the join condition would be a much more efficient:

    ON  cs.clm_sts_cd = cshv.clm_sts_cd
    

    That may make DML a little slower, but it will make queries much, much faster

  • EdStevens
    EdStevens Member Posts: 28,470 Gold Crown

    For future reference, please format all posted code. It will make it much easier to read and comprehend.


  • Sven W.
    Sven W. Member Posts: 10,533 Gold Crown
    edited Sep 22, 2021 3:15PM

    There are several common problems in this query. Which makes me guess that it should be possible to get a major performance improvement when this query is changed.

    However the size and effort needed to tune this is beyond the scope of this forum. Especially since we can not easily test it. And we are all volunteers here.


    So here are just a few general pointers and ideas.

    a) The query reads the same tables again and again and again (tables like injury and claim). Find a way to consolidate all those queries and access the tables only one time.

    b) enable index access paths for most of the tables. The way some filters are currently written prevents index usage on the column. This was already explained by Frank.

    c) for large queries like this reading the execution plan is tricky. Tanel Poder has written something about using flamegraphs (a special type of chart) for execution plans. If you manage to create a flamegraph for this query, you might find out where the most tuning potential is. https://tanelpoder.com/posts/visualizing-sql-plan-execution-time-with-flamegraphs/. It is not easy to setup and most likely you need access to the server to do it.

    d) you seem to have aggregations in it and total rows. You can produce extra set of total rows by using GROUP BY ROLLUP or even better GROUP BY GROUPING SETS without the need to scan the same tables again.


    Best solution: Hire somebody to help you. I would think this is a complex but limited task. Can probably be done in like 2-5 days.