Forum Stats

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

Discussions

Slow Query

n1248
n1248 Member Posts: 3 Green Ribbon

I'm struggling to optimize a report query. It's quite extensive.. When I use start and end dates of 1/1/2016 to today, it is taking hours to complete. My users are telling me this used to complete within 10-15 minutes.


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

),

--Query completes in 34 secs up to this point, same amount of rows. 19623

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)

),

--1m 17sec

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, @User_B5P7Y

    I'm struggling to optimize a report query.

    Whenever you have a performance question, post the EXPLAIN PLAN output.

    Avoid using functions or operations on table columns in join conditions and WHERE clause conditions. If you have an index on a column, Oracle can't use it unless that column stands alone on one side of the operator. For example:

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

    can't use an index o i.injr.dtm. Since you know that vars.from_dt and vars.to_dt are always midnight, that condition can be re-written as

    	 i.injr_dtm >= vars.from_dt
    AND 	 i.injr_dtm <  vars.to_dt + 1
    

     

    Design your tables for efficiency.

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

    is less efficient than

    ipob1.injr_pob_pri_ind = 'Y'
    

    because the latter has the column all by itself on the left-hand side of the = operator. Of course, those two conditions don't produce the same results if the column has leading or training spaces, or if it contains lower-case letters. Why do you have leading or trailing spaces and lower-case letters? If there is no good reason, don't allow them. Put CHECK constraints in your table to guarantee that the column doesn't have extra spaces or lower-case letters. If the table has already been created and populated, then UPDATE it to get all the data into a standard form, and then add the CHECK constraint.

  • BEDE
    BEDE Oracle Developer Member Posts: 2,283 Gold Trophy

    I think one problem is the filtering conditions you put rather late, like:

    FROM       cteq_vars vars

        INNER JOIN INJURY i

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

    besides the TRUNC(i.injr_dtm) BETWEEN vars.From_Dt AND vars.To_Dt.

    Better put the filtering conditions sooner, like

    cteq_max_pob_no AS (

      SELECT   /*+ leading(vars) use_nl(ipob1) */

        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   cteq_vars vars

        join INJURY_PART_OF_BODY ipob1 on

    ipob1 .injr_dtm>= vars.From_Dt AND ipob1 .injr_dtm <trunc(vars.To_Dt)+1

      WHERE   

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

      GROUP BY 

        ipob1.injr_id,

        ipob1.pob_cd,

        ipob1.pob_loc_cd 

    ),

    This way you will have less data for the sort group by, plus, having the join condition like I put it, Oracle may use an index on INJURY_PART_OF_BODY i.injr_dtm

  • n1248
    n1248 Member Posts: 3 Green Ribbon

    Here's the explain plan. Thanks for the help. I'm new to performance tuning on Oracle.


    Plan

    SELECT STATEMENT ALL_ROWSCost: 134,949,164 Bytes: 27,442,890 Cardinality: 25,062 

    231 TEMP TABLE TRANSFORMATION 

    3 LOAD AS SELECT SYS_TEMP_1FD9E01C5_189DD35D

    2 HASH GROUP BY Cost: 1,783 Bytes: 211,878 Cardinality: 11,771 

    1 TABLE ACCESS FULL TABLE MED.INJURY_PART_OF_BODY Cost: 1,781 Bytes: 211,878 Cardinality: 11,771 

    137 LOAD AS SELECT SYS_TEMP_1FD9E01C6_189DD35D

    136 SORT ORDER BY Cost: 54,746 Bytes: 60,512,556 Cardinality: 25,026 

    135 UNION-ALL 

    43 HASH JOIN RIGHT OUTER Cost: 32,992 Bytes: 11,532,268 Cardinality: 23,827 

    4 TABLE ACCESS FULL TABLE MED.OCCUPATION_TYPE Cost: 4 Bytes: 20,910 Cardinality: 510 

    42 HASH JOIN RIGHT OUTER Cost: 32,988 Bytes: 2,069,696 Cardinality: 4,672 

    5 TABLE ACCESS FULL TABLE MED.EMPLOYMENT_CLASS Cost: 6 Bytes: 24,853 Cardinality: 857 

    41 HASH JOIN RIGHT OUTER Cost: 32,982 Bytes: 1,934,208 Cardinality: 4,672 

    6 TABLE ACCESS FULL TABLE MED.PART_OF_BODY Cost: 3 Bytes: 1,185 Cardinality: 79 

    40 HASH JOIN RIGHT OUTER Cost: 32,979 Bytes: 1,864,128 Cardinality: 4,672 

    7 TABLE ACCESS FULL TABLE MED.INJURY_SOURCE Cost: 4 Bytes: 2,100 Cardinality: 75 

    39 HASH JOIN RIGHT OUTER Cost: 32,975 Bytes: 1,733,312 Cardinality: 4,672 

    8 TABLE ACCESS FULL TABLE MED.NATURE_OF_INJURY Cost: 4 Bytes: 1,218 Cardinality: 58 

    38 HASH JOIN Cost: 32,971 Bytes: 1,635,200 Cardinality: 4,672 

    9 TABLE ACCESS FULL TABLE MED.CLAIM_STATUS Cost: 3 Bytes: 588 Cardinality: 21 

    37 HASH JOIN Cost: 32,968 Bytes: 1,003,030 Cardinality: 3,115 

    32 HASH JOIN Cost: 29,633 Bytes: 1,000,500 Cardinality: 3,335 

    30 HASH JOIN Cost: 28,515 Bytes: 875,315 Cardinality: 3,115 

    28 NESTED LOOPS Cost: 25,028 Bytes: 793,750 Cardinality: 3,125 

    26 NESTED LOOPS Cost: 25,028 Bytes: 793,750 Cardinality: 3,125 

    24 HASH JOIN Cost: 18,774 Bytes: 665,625 Cardinality: 3,125 

    22 HASH JOIN OUTER Cost: 14,339 Bytes: 428,268 Cardinality: 2,406 

    19 NESTED LOOPS Cost: 14,329 Bytes: 353,682 Cardinality: 2,406 

    17 NESTED LOOPS Cost: 14,329 Bytes: 353,682 Cardinality: 2,406 

    14 HASH JOIN Cost: 7,151 Bytes: 268,240 Cardinality: 2,395 

    10 INDEX FAST FULL SCAN INDEX (UNIQUE) MED.PKPLCY_BUSN Cost: 371 Bytes: 45,094 Cardinality: 3,221 

    13 NESTED LOOPS Cost: 6,781 Bytes: 234,612 Cardinality: 2,394 

    11 FAST DUAL Cost: 2 Cardinality: 1  

    12 TABLE ACCESS FULL TABLE MED.INJURY Cost: 6,779 Bytes: 234,612 Cardinality: 2,394  

    16 INDEX RANGE SCAN INDEX (UNIQUE) MED.XPKCLM_STS_HIST Cost: 2 Cardinality: 1 

    15 FAST DUAL Cost: 2 Cardinality: 1 

    18 TABLE ACCESS BY INDEX ROWID TABLE MED.CLAIM_STATUS_HISTORY Cost: 3 Bytes: 35 Cardinality: 1 

    21 VIEW SGRIFFIN. Cost: 10 Bytes: 364,901 Cardinality: 11,771 

    20 TABLE ACCESS FULL TABLE (TEMP) SYS.SYS_TEMP_1FD9E01C5_189DD35D Cost: 10 Bytes: 211,878 Cardinality: 11,771 

    23 TABLE ACCESS FULL TABLE MED.PERSON_HISTORY Cost: 4,431 Bytes: 22,986,915 Cardinality: 656,769 

    25 INDEX UNIQUE SCAN INDEX (UNIQUE) MED.XPKCLM Cost: 1 Cardinality: 1 

    27 TABLE ACCESS BY INDEX ROWID TABLE MED.CLAIM Cost: 2 Bytes: 41 Cardinality: 1 

    29 TABLE ACCESS FULL TABLE MED.LEGAL_ENTITY Cost: 3,481 Bytes: 23,368,851 Cardinality: 865,513 

    31 INDEX FAST FULL SCAN INDEX MED.IDX_CLAIM_TYPE_HISTORY_01 Cost: 1,111 Bytes: 18,829,551 Cardinality: 991,029 

    36 VIEW VIEW SYS.VW_SQ_3 Cost: 3,329 Bytes: 20,361,088 Cardinality: 925,504 

    35 HASH GROUP BY Cost: 3,329 Bytes: 12,957,056 Cardinality: 925,504 

    34 FILTER 

    33 INDEX FULL SCAN INDEX (UNIQUE) MED.XPKCLAIM_TYPE_HISTORY Cost: 3,329 Bytes: 13,874,406 Cardinality: 991,029 

    87 HASH JOIN RIGHT OUTER Cost: 12,362 Bytes: 552,624 Cardinality: 1,191 

    44 TABLE ACCESS FULL TABLE MED.OCCUPATION_TYPE Cost: 4 Bytes: 20,910 Cardinality: 510 

    86 HASH JOIN RIGHT OUTER Cost: 12,358 Bytes: 98,982 Cardinality: 234 

    45 TABLE ACCESS FULL TABLE MED.EMPLOYMENT_CLASS Cost: 6 Bytes: 24,853 Cardinality: 857 

    85 HASH JOIN RIGHT OUTER Cost: 12,352 Bytes: 92,196 Cardinality: 234 

    46 TABLE ACCESS FULL TABLE MED.PART_OF_BODY Cost: 3 Bytes: 1,185 Cardinality: 79 

    84 HASH JOIN RIGHT OUTER Cost: 12,349 Bytes: 88,686 Cardinality: 234 

    47 TABLE ACCESS FULL TABLE MED.INJURY_SOURCE Cost: 4 Bytes: 2,100 Cardinality: 75 

    83 NESTED LOOPS Cost: 12,345 Bytes: 82,134 Cardinality: 234 

    78 NESTED LOOPS Cost: 11,595 Bytes: 85,500 Cardinality: 250 

    75 NESTED LOOPS Cost: 10,900 Bytes: 75,582 Cardinality: 234 

    72 NESTED LOOPS Cost: 10,432 Bytes: 69,264 Cardinality: 234 

    69 HASH JOIN RIGHT OUTER Cost: 9,712 Bytes: 46,980 Cardinality: 180 

    48 TABLE ACCESS FULL TABLE MED.NATURE_OF_INJURY Cost: 4 Bytes: 1,218 Cardinality: 58 

    68 NESTED LOOPS Cost: 9,708 Bytes: 43,200 Cardinality: 180 

    66 NESTED LOOPS Cost: 9,708 Bytes: 43,200 Cardinality: 180 

    64 NESTED LOOPS Cost: 9,347 Bytes: 35,820 Cardinality: 180 

    61 HASH JOIN OUTER Cost: 8,987 Bytes: 33,300 Cardinality: 180 

    58 NESTED LOOPS Cost: 8,977 Bytes: 27,720 Cardinality: 180 

    56 NESTED LOOPS Cost: 8,977 Bytes: 27,720 Cardinality: 866 

    54 HASH JOIN Cost: 7,244 Bytes: 48,496 Cardinality: 866 

    51 NESTED LOOPS Cost: 5 Bytes: 28 Cardinality: 1 

    49 FAST DUAL Cost: 2 Cardinality: 1  

    50 TABLE ACCESS FULL TABLE MED.CLAIM_STATUS Cost: 3 Bytes: 28 Cardinality: 1  

    53 INDEX FAST FULL SCAN INDEX MED.ITD_CLAIM_STATUS_HISTORY_NDX1 Cost: 7,239 Bytes: 323,456 Cardinality: 11,552 

    52 FAST DUAL Cost: 2 Cardinality: 1  

    55 INDEX UNIQUE SCAN INDEX (UNIQUE) MED.XPKINJURY Cost: 1 Cardinality: 1 

    57 TABLE ACCESS BY INDEX ROWID TABLE MED.INJURY Cost: 2 Bytes: 98 Cardinality: 1 

    60 VIEW SGRIFFIN. Cost: 10 Bytes: 364,901 Cardinality: 11,771 

    59 TABLE ACCESS FULL TABLE (TEMP) SYS.SYS_TEMP_1FD9E01C5_189DD35D Cost: 10 Bytes: 211,878 Cardinality: 11,771 

    63 TABLE ACCESS BY INDEX ROWID BATCHED TABLE MED.POLICY_BUSINESS Cost: 2 Bytes: 14 Cardinality: 1 

    62 INDEX RANGE SCAN INDEX MED.XPLCY_BUSN_LGL_ENTY_BUSN Cost: 1 Cardinality: 1 

    65 INDEX UNIQUE SCAN INDEX (UNIQUE) MED.XPKCLM Cost: 1 Cardinality: 1 

    67 TABLE ACCESS BY INDEX ROWID TABLE MED.CLAIM Cost: 2 Bytes: 41 Cardinality: 1 

    71 TABLE ACCESS BY INDEX ROWID BATCHED TABLE MED.PERSON_HISTORY Cost: 4 Bytes: 35 Cardinality: 1 

    70 INDEX RANGE SCAN INDEX MED.ITD_PERSON_HISTORY_NDX1 Cost: 2 Cardinality: 2 

    74 TABLE ACCESS BY INDEX ROWID TABLE MED.LEGAL_ENTITY Cost: 2 Bytes: 27 Cardinality: 1 

    73 INDEX UNIQUE SCAN INDEX (UNIQUE) MED.XPKLGL_ENTY Cost: 1 Cardinality: 1 

    77 TABLE ACCESS BY INDEX ROWID BATCHED TABLE MED.CLAIM_TYPE_HISTORY Cost: 3 Bytes: 19 Cardinality: 1 

    76 INDEX RANGE SCAN INDEX (UNIQUE) MED.XPKCLAIM_TYPE_HISTORY Cost: 2 Cardinality: 1 

    82 VIEW PUSHED PREDICATE VIEW SYS.VW_SQ_4 Cost: 3 Bytes: 9 Cardinality: 1 

    81 FILTER 

    80 SORT AGGREGATE Bytes: 14 Cardinality: 1 

    79 INDEX RANGE SCAN INDEX (UNIQUE) MED.XPKCLAIM_TYPE_HISTORY Cost: 3 Bytes: 14 Cardinality: 1 

    134 HASH JOIN OUTER Cost: 9,383 Bytes: 3,712 Cardinality: 8 

    132 NESTED LOOPS OUTER Cost: 9,379 Bytes: 846 Cardinality: 2 

    129 NESTED LOOPS OUTER Cost: 9,375 Bytes: 788 Cardinality: 2 

    126 NESTED LOOPS OUTER Cost: 9,373 Bytes: 758 Cardinality: 2 

    123 NESTED LOOPS OUTER Cost: 9,371 Bytes: 702 Cardinality: 2 

    120 NESTED LOOPS Cost: 9,369 Bytes: 660 Cardinality: 2 

    117 NESTED LOOPS Cost: 9,365 Bytes: 606 Cardinality: 2 

    112 NESTED LOOPS Cost: 9,359 Bytes: 588 Cardinality: 2 

    109 NESTED LOOPS Cost: 9,353 Bytes: 550 Cardinality: 2 

    106 NESTED LOOPS Cost: 9,349 Bytes: 240 Cardinality: 1 

    103 HASH JOIN OUTER Cost: 9,347 Bytes: 226 Cardinality: 1 

    100 NESTED LOOPS Cost: 9,337 Bytes: 195 Cardinality: 1 

    98 NESTED LOOPS Cost: 9,337 Bytes: 195 Cardinality: 180 

    96 NESTED LOOPS Cost: 8,977 Bytes: 27,720 Cardinality: 180 

    93 HASH JOIN Cost: 7,244 Bytes: 48,496 Cardinality: 866 

    90 NESTED LOOPS Cost: 5 Bytes: 28 Cardinality: 1 

    88 FAST DUAL Cost: 2 Cardinality: 1 

    89 TABLE ACCESS FULL TABLE MED.CLAIM_STATUS Cost: 3 Bytes: 28 Cardinality: 1 

    92 INDEX FAST FULL SCAN INDEX MED.ITD_CLAIM_STATUS_HISTORY_NDX1 Cost: 7,239 Bytes: 323,456 Cardinality: 11,552 

    91 FAST DUAL Cost: 2 Cardinality: 1 

    95 TABLE ACCESS BY INDEX ROWID TABLE MED.INJURY Cost: 2 Bytes: 98 Cardinality: 1 

    94 INDEX UNIQUE SCAN INDEX (UNIQUE) MED.XPKINJURY Cost: 1 Cardinality: 1 

    97 INDEX UNIQUE SCAN INDEX (UNIQUE) MED.XPKCLM Cost: 1 Cardinality: 1 

    99 TABLE ACCESS BY INDEX ROWID TABLE MED.CLAIM Cost: 2 Bytes: 41 Cardinality: 1 

    102 VIEW SGRIFFIN. Cost: 10 Bytes: 364,901 Cardinality: 11,771 

    101 TABLE ACCESS FULL TABLE (TEMP) SYS.SYS_TEMP_1FD9E01C5_189DD35D Cost: 10 Bytes: 211,878 Cardinality: 11,771 

    105 TABLE ACCESS BY INDEX ROWID BATCHED TABLE MED.POLICY_BUSINESS Cost: 2 Bytes: 14 Cardinality: 1 

    104 INDEX RANGE SCAN INDEX MED.XPLCY_BUSN_LGL_ENTY_BUSN Cost: 1 Cardinality: 1 

    108 TABLE ACCESS BY INDEX ROWID BATCHED TABLE MED.PERSON_HISTORY Cost: 4 Bytes: 35 Cardinality: 1 

    107 INDEX RANGE SCAN INDEX MED.ITD_PERSON_HISTORY_NDX1 Cost: 2 Cardinality: 2 

    111 TABLE ACCESS BY INDEX ROWID BATCHED TABLE MED.CLAIM_TYPE_HISTORY Cost: 3 Bytes: 19 Cardinality: 1 

    110 INDEX RANGE SCAN INDEX (UNIQUE) MED.XPKCLAIM_TYPE_HISTORY Cost: 2 Cardinality: 1 

    116 VIEW PUSHED PREDICATE VIEW SYS.VW_SQ_5 Cost: 3 Bytes: 9 Cardinality: 1 

    115 FILTER 

    114 SORT AGGREGATE Bytes: 14 Cardinality: 1 

    113 INDEX RANGE SCAN INDEX (UNIQUE) MED.XPKCLAIM_TYPE_HISTORY Cost: 3 Bytes: 14 Cardinality: 1 

    119 TABLE ACCESS BY INDEX ROWID TABLE MED.LEGAL_ENTITY Cost: 2 Bytes: 27 Cardinality: 1 

    118 INDEX UNIQUE SCAN INDEX (UNIQUE) MED.XPKLGL_ENTY Cost: 1 Cardinality: 1 

    122 TABLE ACCESS BY INDEX ROWID BATCHED TABLE MED.NATURE_OF_INJURY Cost: 1 Bytes: 21 Cardinality: 1 

    121 INDEX RANGE SCAN INDEX MED.IDX_NATURE_OF_INJURY Cost: 0 Cardinality: 1 

    125 TABLE ACCESS BY INDEX ROWID BATCHED TABLE MED.INJURY_SOURCE Cost: 1 Bytes: 28 Cardinality: 1 

    124 INDEX RANGE SCAN INDEX MED.IDX_INJURY_SOURCE Cost: 0 Cardinality: 1 

    128 TABLE ACCESS BY INDEX ROWID BATCHED TABLE MED.PART_OF_BODY Cost: 1 Bytes: 15 Cardinality: 1 

    127 INDEX RANGE SCAN INDEX MED.IDX_PART_OF_BODY Cost: 0 Cardinality: 1 

    131 TABLE ACCESS BY INDEX ROWID BATCHED TABLE MED.EMPLOYMENT_CLASS Cost: 2 Bytes: 29 Cardinality: 1 

    130 INDEX RANGE SCAN INDEX MED.IDX_EMPLOYMENT_CLASS Cost: 1 Cardinality: 1 

    133 TABLE ACCESS FULL TABLE MED.OCCUPATION_TYPE Cost: 4 Bytes: 20,910 Cardinality: 510 

    230 SORT ORDER BY Cost: 134,890,168 Bytes: 27,442,890 Cardinality: 25,062 

    229 HASH JOIN RIGHT OUTER Cost: 134,884,409 Bytes: 27,442,890 Cardinality: 25,062 

    167 VIEW SGRIFFIN. Cost: 134,752,238 Bytes: 1,629,030 Cardinality: 25,062 

    166 NESTED LOOPS OUTER Cost: 134,752,238 Bytes: 1,729,278 Cardinality: 25,062 

    151 NESTED LOOPS OUTER Cost: 45,069,017 Bytes: 1,027,542 Cardinality: 25,062 

    139 VIEW SGRIFFIN. Cost: 1,784 Bytes: 325,806 Cardinality: 25,062 

    138 TABLE ACCESS FULL TABLE (TEMP) SYS.SYS_TEMP_1FD9E01C6_189DD35D Cost: 1,784 Bytes: 25,312,620 Cardinality: 25,062 

    150 VIEW PUSHED PREDICATE SGRIFFIN. Cost: 1,798 Bytes: 28 Cardinality: 1 

    149 SORT GROUP BY Cost: 1,798 Bytes: 52 Cardinality: 1 

    148 FILTER 

    144 HASH JOIN Cost: 1,794 Bytes: 52 Cardinality: 1 

    141 TABLE ACCESS BY INDEX ROWID BATCHED TABLE MED.REMAINING_RESERVES_HISTORY_TMP Cost: 10 Bytes: 196 Cardinality: 7 

    140 INDEX RANGE SCAN INDEX MED.IDX$$_108030001 Cost: 3 Cardinality: 7 

    143 VIEW SGRIFFIN. Cost: 1,784 Bytes: 601,488 Cardinality: 25,062 

    142 TABLE ACCESS FULL TABLE (TEMP) SYS.SYS_TEMP_1FD9E01C6_189DD35D Cost: 1,784 Bytes: 25,312,620 Cardinality: 25,062 

    147 SORT AGGREGATE Bytes: 14 Cardinality: 1 

    146 FIRST ROW Cost: 3 Bytes: 14 Cardinality: 1 

    145 INDEX RANGE SCAN (MIN/MAX) INDEX (UNIQUE) MED.PK_REMAINING_RESERVES_HIST_TMP Cost: 3 Bytes: 14 Cardinality: 1 

    165 VIEW PUSHED PREDICATE SGRIFFIN. Cost: 3,578 Bytes: 28 Cardinality: 1 

    164 SORT GROUP BY Cost: 3,578 Bytes: 64 Cardinality: 2 

    163 NESTED LOOPS Cost: 3,578 Bytes: 64 Cardinality: 2 

    152 FAST DUAL Cost: 2 Cardinality: 1 

    162 VIEW VIEW SYS.VW_JF_SET$412E67FE Cost: 3,576 Bytes: 64 Cardinality: 2 

    161 UNION-ALL 

    156 HASH JOIN Cost: 1,788 Bytes: 48 Cardinality: 1 

    153 INDEX RANGE SCAN INDEX MED.XCFT_INJR_ID_TYP_CD1 Cost: 4 Bytes: 31 Cardinality: 1 

    155 VIEW SGRIFFIN. Cost: 1,784 Bytes: 426,054 Cardinality: 25,062 

    154 TABLE ACCESS FULL TABLE (TEMP) SYS.SYS_TEMP_1FD9E01C6_189DD35D Cost: 1,784 Bytes: 25,312,620 Cardinality: 25,062 

    160 HASH JOIN Cost: 1,788 Bytes: 48 Cardinality: 1 

    157 INDEX RANGE SCAN INDEX MED.XCFT_INJR_ID_TYP_CD1 Cost: 4 Bytes: 31 Cardinality: 1 

    159 VIEW SGRIFFIN. Cost: 1,784 Bytes: 426,054 Cardinality: 25,062 

    158 TABLE ACCESS FULL TABLE (TEMP) SYS.SYS_TEMP_1FD9E01C6_189DD35D Cost: 1,784 Bytes: 25,312,620 Cardinality: 25,062 

    228 HASH JOIN RIGHT OUTER Cost: 132,171 Bytes: 25,813,860 Cardinality: 25,062 

    225 VIEW SGRIFFIN. Cost: 130,387 Bytes: 64 Cardinality: 2 

    224 HASH GROUP BY Cost: 130,387 Bytes: 64 Cardinality: 2 

    223 VIEW SGRIFFIN. Cost: 130,386 Bytes: 64 Cardinality: 2 

    222 UNION-ALL 

    194 NESTED LOOPS Cost: 65,193 Bytes: 142 Cardinality: 1 

    189 HASH JOIN Cost: 65,190 Bytes: 129 Cardinality: 1 

    186 NESTED LOOPS Cost: 63,406 Bytes: 14,000 Cardinality: 125 

    184 NESTED LOOPS Cost: 63,406 Bytes: 14,000 Cardinality: 20,190 

    182 HASH JOIN Cost: 23,004 Bytes: 1,574,820 Cardinality: 20,190 

    180 NESTED LOOPS Cost: 23,004 Bytes: 1,574,820 Cardinality: 20,190 

    177 STATISTICS COLLECTOR 

    176 HASH JOIN Cost: 15,693 Bytes: 1,145,844 Cardinality: 18,188 

    174 NESTED LOOPS Cost: 15,693 Bytes: 1,145,844 Cardinality: 18,188 

    171 STATISTICS COLLECTOR 

    170 HASH JOIN Cost: 11,483 Bytes: 317,386 Cardinality: 8,578 

    168 TABLE ACCESS FULL TABLE MED.ADMISSION_PERIOD Cost: 3,255 Bytes: 198,189 Cardinality: 10,431 

    169 TABLE ACCESS FULL TABLE MED.ADMISSION_PERIOD_RATE_HISTORY Cost: 8,228 Bytes: 341,964 Cardinality: 18,998 

    173 TABLE ACCESS BY INDEX ROWID BATCHED TABLE MED.ADMISSION_SCHEDULE_DETAIL Cost: 4,195 Bytes: 52 Cardinality: 2 

    172 INDEX RANGE SCAN INDEX MED.XADMI_SCH_DET

    175 TABLE ACCESS FULL TABLE MED.ADMISSION_SCHEDULE_DETAIL Cost: 4,195 Bytes: 57,460,416 Cardinality: 2,210,016 

    179 TABLE ACCESS BY INDEX ROWID BATCHED TABLE MED.ADMISSION_SCHEDULE_DETAIL_AMT Cost: 7,296 Bytes: 15 Cardinality: 1 

    178 INDEX RANGE SCAN INDEX (UNIQUE) MED.XPKADMIS_SCH_DTL_AMT

    181 TABLE ACCESS FULL TABLE MED.ADMISSION_SCHEDULE_DETAIL_AMT Cost: 7,296 Bytes: 36,765,225 Cardinality: 2,451,015 

    183 INDEX UNIQUE SCAN INDEX (UNIQUE) MED.XPKCLM_FNCL_TRAN Cost: 1 Cardinality: 1 

    185 TABLE ACCESS BY INDEX ROWID TABLE MED.CLAIM_FINANCIAL_TRAN Cost: 2 Bytes: 34 Cardinality: 1 

    188 VIEW SGRIFFIN. Cost: 1,784 Bytes: 426,054 Cardinality: 25,062 

    187 TABLE ACCESS FULL TABLE (TEMP) SYS.SYS_TEMP_1FD9E01C6_189DD35D Cost: 1,784 Bytes: 25,312,620 Cardinality: 25,062 

    193 VIEW PUSHED PREDICATE VIEW SYS.VW_SQ_1 Cost: 3 Bytes: 13 Cardinality: 1 

    192 FILTER 

    191 SORT AGGREGATE Bytes: 16 Cardinality: 1 

    190 INDEX RANGE SCAN INDEX (UNIQUE) MED.XPKADMIS_PRD_RT_HIST Cost: 3 Bytes: 16 Cardinality: 1 

    221 NESTED LOOPS Cost: 65,193 Bytes: 142 Cardinality: 1 

    216 HASH JOIN Cost: 65,190 Bytes: 129 Cardinality: 1 

    213 NESTED LOOPS Cost: 63,406 Bytes: 11,312 Cardinality: 101 

    211 NESTED LOOPS Cost: 63,406 Bytes: 11,312 Cardinality: 20,190 

    209 HASH JOIN Cost: 23,004 Bytes: 1,574,820 Cardinality: 20,190 

    207 NESTED LOOPS Cost: 23,004 Bytes: 1,574,820 Cardinality: 20,190 

    204 STATISTICS COLLECTOR 

    203 HASH JOIN Cost: 15,693 Bytes: 1,145,844 Cardinality: 18,188 

    201 NESTED LOOPS Cost: 15,693 Bytes: 1,145,844 Cardinality: 18,188 

    198 STATISTICS COLLECTOR 

    197 HASH JOIN Cost: 11,483 Bytes: 317,386 Cardinality: 8,578 

    195 TABLE ACCESS FULL TABLE MED.ADMISSION_PERIOD Cost: 3,255 Bytes: 198,189 Cardinality: 10,431 

    196 TABLE ACCESS FULL TABLE MED.ADMISSION_PERIOD_RATE_HISTORY Cost: 8,228 Bytes: 341,964 Cardinality: 18,998 

    200 TABLE ACCESS BY INDEX ROWID BATCHED TABLE MED.ADMISSION_SCHEDULE_DETAIL Cost: 4,195 Bytes: 52 Cardinality: 2 

    199 INDEX RANGE SCAN INDEX MED.XADMI_SCH_DET

    202 TABLE ACCESS FULL TABLE MED.ADMISSION_SCHEDULE_DETAIL Cost: 4,195 Bytes: 57,460,416 Cardinality: 2,210,016 

    206 TABLE ACCESS BY INDEX ROWID BATCHED TABLE MED.ADMISSION_SCHEDULE_DETAIL_AMT Cost: 7,296 Bytes: 15 Cardinality: 1 

    205 INDEX RANGE SCAN INDEX (UNIQUE) MED.XPKADMIS_SCH_DTL_AMT

    208 TABLE ACCESS FULL TABLE MED.ADMISSION_SCHEDULE_DETAIL_AMT Cost: 7,296 Bytes: 36,765,225 Cardinality: 2,451,015 

    210 INDEX UNIQUE SCAN INDEX (UNIQUE) MED.XPKCLM_FNCL_TRAN Cost: 1 Cardinality: 1 

    212 TABLE ACCESS BY INDEX ROWID TABLE MED.CLAIM_FINANCIAL_TRAN Cost: 2 Bytes: 34 Cardinality: 1 

    215 VIEW SGRIFFIN. Cost: 1,784 Bytes: 426,054 Cardinality: 25,062 

    214 TABLE ACCESS FULL TABLE (TEMP) SYS.SYS_TEMP_1FD9E01C6_189DD35D Cost: 1,784 Bytes: 25,312,620 Cardinality: 25,062 

    220 VIEW PUSHED PREDICATE VIEW SYS.VW_SQ_2 Cost: 3 Bytes: 13 Cardinality: 1 

    219 FILTER 

    218 SORT AGGREGATE Bytes: 16 Cardinality: 1 

    217 INDEX RANGE SCAN INDEX (UNIQUE) MED.XPKADMIS_PRD_RT_HIST Cost: 3 Bytes: 16 Cardinality: 1 

    227 VIEW SGRIFFIN. Cost: 1,784 Bytes: 25,011,876 Cardinality: 25,062 

    226 TABLE ACCESS FULL TABLE (TEMP) SYS.SYS_TEMP_1FD9E01C6_189DD35D Cost: 1,784 Bytes: 25,312,620 Cardinality: 25,062