OraFAQ Forum: Performance Tuning

Home » RDBMS Server » Performance Tuning » Tuning SQL

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Tuning SQL [message #140797]

Wed, 05 October 2005 11:55

vikramjogi
Messages: 71
Registered: June 2005
Location: Scranton

Member

Hello all,

I am pretty new to the tuning an sql. I thought some one will help me out tuning this sql. I know that this is not the way to ask but hope some one will help me out optimizing this sql.

select
tab2.year year,
tab2.pol pol,
tab2.file_number file_number,
sum(tab2.claims) CLAIMS,
sum(tab2.medical_claims_counter) medical_claims_counter,
SUM(MEDICAL) MEDICAL,
SUM(medical_p-r_paid) MEDICAL_P,
SUM(rehabilitation) rehabilitation,
SUM(r_paid) R_PAID,
SUM(compensation+non_rpt) compensation,
SUM(c_amount_paid) c_amount_paid
from
(
select tab.year year,
tab.poltyp pol,
tab.file_number file_number,
COUNT(tab.claims) CLAIMS,
COUNT(tab.medical_claims_counter)medical_claims_counter,
tab.injr_id injr_id,
-- sum(tab.count_clm) count_clm,
sum(NVL(pkg_swif_clm.f_clm_med_rsrv_get(tab.injr_id,:busn_dt),0))medical,
sum( NVL(pkg_swif_clm.f_clm_med_paid_get(tab.injr_id,:busn_dt),0))medical_p, sum(NVL(pkg_swif_clm.f_clm_voc_rsrv_get(tab.injr_id,:busn_dt),0))rehabilitation,
sum( NVL(pkg_swif_clm.f_clm_voc_paid_get(tab.injr_id,:busn_dt),0)) r_paid,
sum(NVL(pkg_swif_clm.f_clm_comp_rsrv_get(tab.injr_id,:busn_dt),0))compensation,
sum(nvl(pkg_swif_clm.f_clm_nonrpt_comp_rsrv_get(tab.injr_id,:busn_dt),0))non_rpt,
sum(NVL(pkg_swif_clm.f_clm_comp_paid_get(tab.injr_id,:busn_dt),0))c_amount_paid
from
(
select to_char(trunc(i.injr_dtm),'yyyy') year,
-- 1 count_clm,
c.injr_id injr_id,
c.clm_no file_number,
CASE
WHEN p.mrkt_typ_cd = 'coal' AND pa.attr_sub_typ_cd = 'n' THEN ' COAL'
WHEN p.mrkt_typ_cd = 'voln' AND pa.attr_sub_typ_cd = 'n' THEN ' COMMERCIAL'
-- WHEN p.mrkt_typ_cd = 'voln' AND pa.attr_sub_typ_cd = 'y' THEN ' COMMONWEALTH'
-- WHEN p.mrkt_typ_cd = 'self' AND pa.attr_sub_typ_cd = 'y' THEN ' COM.SELF-INS'
-- WHEN p.mrkt_typ_cd = 'self' AND pa.attr_sub_typ_cd = 'n' THEN ' SELF-INSURED'
-- when c.cls_typ_cd = 'f' then 'USLandH'
END poltyp,
case when cth.clm_typ_cd = 'wagel' then
c.clm_no
end claims,
case when cth.clm_typ_cd = 'med' then
c.clm_no
end medical_claims_counter
from injury i,
claim c,
policy p,
policy_period pp,
claim_type_history cth,
policy_attribute pa
where trunc(i.injr_dtm) <= :busn_dt
and c.injr_id = i.injr_id
and p.plcy_no = c.plcy_no
and p.mrkt_typ_cd in ('coal','voln')
and p.plcy_id = pp.plcy_id
and cth.injr_id = c.injr_id
and cth.clm_typ_hist_eff_dtm <= :busn_dt
and cth.clm_typ_hist_eff_dtm = (select max(cth1.clm_typ_hist_eff_dtm)
from claim_type_history cth1
where cth1.injr_id = cth.injr_id
and cth1.clm_typ_hist_eff_dtm <=:busn_dt
and nvl(cth1.clm_typ_hist_end_dt,:busn_dt) >= :busn_dt)
--AND pa.plcy_id = p.plcy_id
-- AND pa.attr_typ_cd= 'cwlth'
and pp.plcy_prd_eff_dt <= trunc(i.injr_dtm)
and pp.plcy_prd_end_dt > trunc(i.injr_dtm)
--and trunc(i.injr_dtm) between pp.plcy_prd_eff_dt and pp.plcy_prd_end_dt
and (exists (select csh.injr_id injr_id
from claim_status cs,
claim_status_history csh
where csh.injr_id = i.injr_id
and cs.clm_sts_cd = csh.clm_sts_cd
and cs.clm_sts_cd_par = 'opn' -- open claim .
and trunc(csh.clm_sts_hist_eff_dt) <= :busn_dt
and csh.clm_sts_hist_eff_dt = (select max(csh1.clm_sts_hist_eff_dt)
from claim_status_history csh1
where csh.injr_id = csh1.injr_id
and trunc(csh1.clm_sts_hist_eff_dt) <=:busn_dt
and nvl(trunc(csh1.clm_sts_hist_end_dt),:busn_dt) >= :busn_dt))))tab

where tab.year='1997'
GROUP BY
tab.year,
tab.poltyp,
tab.file_number,
tab.injr_id

union all

select tab1.year year,
tab1.poltyp pol,
tab1.file_number file_number,
COUNT(tab1.claims) CLAIMS,
COUNT(tab1.medical_claims_counter)medical_claims_counter,
tab1.injr_id injr_id,
-- sum(tab.count_clm) count_clm,
sum(NVL(pkg_swif_clm.f_clm_med_rsrv_get(tab.injr_id,:busn_dt),0))medical,
sum( NVL(pkg_swif_clm.f_clm_med_paid_get(tab.injr_id,:busn_dt),0))medical_p,
sum(NVL(pkg_swif_clm.f_clm_voc_rsrv_get(tab.injr_id,:busn_dt),0))rehabilitation,
sum( NVL(pkg_swif_clm.f_clm_voc_paid_get(tab.injr_id,:busn_dt),0)) r_paid,
sum(NVL(pkg_swif_clm.f_clm_comp_rsrv_get(tab.injr_id,:busn_dt),0)compensation,
sum(nvl(pkg_swif_clm.f_clm_nonrpt_comp_rsrv_get(tab.injr_id,:busn_dt),0))non_rpt,
sum(NVL(pkg_swif_clm.f_clm_comp_paid_get(tab.injr_id,:busn_dt),0))c_amount_paid
from
(
select to_char(trunc(i.injr_dtm),'yyyy') year,
-- 1 count_clm,
c.injr_id injr_id,
c.clm_no file_number,
CASE
WHEN p.mrkt_typ_cd = 'coal' AND pa.attr_sub_typ_cd = 'n' THEN ' COAL'
WHEN p.mrkt_typ_cd = 'voln' AND pa.attr_sub_typ_cd = 'n' THEN ' COMMERCIAL'
-- WHEN p.mrkt_typ_cd = 'voln' AND pa.attr_sub_typ_cd = 'y' THEN ' COMMONWEALTH'
WHEN p.mrkt_typ_cd = 'self' AND pa.attr_sub_typ_cd = 'y' THEN ' COM.SELF-INS'
WHEN p.mrkt_typ_cd = 'self' AND pa.attr_sub_typ_cd = 'n' THEN ' SELF-INSURED'
-- when c.cls_typ_cd = 'f' then 'USLandH'
END poltyp,
case when cth.clm_typ_cd = 'wagel' then
c.clm_no
end claims,
case when cth.clm_typ_cd = 'med' then
c.clm_no
end medical_claims_counter

from injury i,
claim c,
policy p,
policy_period pp,
claim_type_history cth,
policy_attribute pa
where trunc(i.injr_dtm) <=:busn_dt
and c.injr_id = i.injr_id
and p.plcy_no = c.plcy_no
and to_char(trunc(i.injr_dtm),'yyyy')='2005'
and p.mrkt_typ_cd in ('coal','voln','self')
and pa.plcy_id = p.plcy_id
-- and pa.attr_typ_cd= 'cwlth'
and p.plcy_id = pp.plcy_id
and pp.plcy_prd_eff_dt <= trunc(i.injr_dtm)
and pp.plcy_prd_end_dt > trunc(i.injr_dtm)
--and trunc(i.injr_dtm) between pp.plcy_prd_eff_dt and pp.plcy_prd_end_dt
and (exists (select csh.injr_id injr_id
from claim_status cs,
claim_status_history csh
where csh.injr_id = i.injr_id
and cs.clm_sts_cd = csh.clm_sts_cd
and cs.clm_sts_cd_par = 'cls' -- open claim .
and trunc(csh.clm_sts_hist_eff_dt) <= :busn_dt
and csh.clm_sts_hist_eff_dt = (select max(csh1.clm_sts_hist_eff_dt)
from claim_status_history csh1
where csh.injr_id = csh1.injr_id
and trunc(csh1.clm_sts_hist_eff_dt) <=:busn_dt and nvl(trunc(csh1.clm_sts_hist_end_dt),:busn_dt) >=:busn_dt))))tab1

where
-- tab1.accident_year='2005'
-- and
((medical-medical_p)+(rehabilation-r_paid)) > 0 or
((compensation-c_amount_paid)+non_rpt) > 0)
GROUP BY
tab1.year,
tab1.poltyp,
tab1.file_number,
tab1.injr_id)tab2
group by
tab2.year,
tab2.pol,
tab2.file_number

I am attaching a document which contains the query. Please some one help me out tuning this query.