Home » RDBMS Server » Performance Tuning » help me tune this (oracle 11g)
| help me tune this [message #498768] |
Fri, 11 March 2011 00:58  |
 |
newbie69
Messages: 2
Registered: March 2011
Location: manila
|
Junior Member |
|
|
SELECT a.polno, parsename_temp (f.namestr, 'LFM', 'FML') insured,
parsename_temp (h.namestr, 'LFM', 'FML') owner, d.poldate,
fngetrefdesc (b.paymode) paymode,
fngetmodalpremptd (a.polno, i.duedate)
+ fngetmodaldstptd (a.polno, i.duedate) modalprem,
fngetpolicyyr (i.duedate, d.poldate) polyr,
fngetrefdesc (c.planmstdesc) PLAN,
fngetrefdesc (c.currency) currency, i.duedate, i.valuedate,
i.premamt, fngetrefdesc (a.polstat) polstat,
CASE a.polstat
WHEN fngetrefseqno ('POLICYSTAT_D_DEATH_CLAIM')
THEN fngetpoldate (a.polno,
NULL,
'POLICYDATE_ASSURED_DEATH_DATE'
)
WHEN fngetrefseqno ('POLICYSTAT_E_EXTENDED')
THEN fngetpoldate (a.polno, NULL,
'POLICYDATE_ETI_EFFECTIVITY')
WHEN fngetrefseqno ('POLICYSTAT_I_INFORCE')
THEN fngetpoldate (a.polno,
NULL,
'POLICYDATE_POLICY_EFFECTIVITY_DATE'
)
WHEN fngetrefseqno ('POLICYSTAT_L_LAPSED')
THEN fngetpoldate (a.polno,
NULL,
'POLICYDATE_POLICY_LAPSE_DATE'
)
WHEN fngetrefseqno ('POLICYSTAT_M_MATURED')
THEN fngetpoldate (a.polno, NULL, 'POLICYDATE_MATURITY_DATE')
WHEN fngetrefseqno ('POLICYSTAT_P_PAID_UP')
THEN fngetpoldate (a.polno, NULL, 'POLICYSTAT_P_PAID_UP')
WHEN fngetrefseqno ('POLICYSTAT_R_REDUCE_PAID_UP')
THEN fngetpoldate (a.polno, NULL, 'POLICYDATE_RPU_DATE')
WHEN fngetrefseqno ('POLICYSTAT_S_SURRENDERED')
THEN fngetpoldate (a.polno,
NULL,
'POLICYDATE_POLICY_SURRENDER_DATE'
)
WHEN fngetrefseqno ('POLICYSTAT_T_POLICY_STATUS')
THEN fngetpoldate (a.polno,
NULL,
'POLICYDATE_TERMINATION_DATE'
)
WHEN fngetrefseqno ('POLICYSTAT_W_POLICY_STATUS')
THEN fngetpoldate (a.polno,
NULL,
'POLICYDATE_WAIVED_PREMIUM_EFFDATE'
)
WHEN fngetrefseqno ('POLICYSTAT_C_CANCELLED')
THEN fngetpoldate (a.polno, NULL, 'POLICYDATE_DATE_CANCELLED')
WHEN fngetrefseqno ('POLICYSTAT_V_CONVERTED')
THEN fngetpoldate (a.polno, NULL,
'POLICYDATE_CONVERSION_DATE')
WHEN fngetrefseqno ('POLICYSTAT_R_RESCINDED')
THEN fngetpoldate (a.polno, NULL, 'POLICYDATE_RESCIND_DATE')
WHEN fngetrefseqno ('POLICYSTAT_CB_AVAILING')
THEN fngetpoldate (a.polno,
NULL,
'POLICYDATE_CB_AVAILING_DATE'
)
WHEN fngetrefseqno ('POLICYSTAT_AS_AUTO_SURRENDERED')
THEN fngetpoldate (a.polno,
NULL,
'POLICYDATE_POLICY_SURRENDER_DATE'
)
WHEN fngetrefseqno ('POLICYSTAT_E_EXPIRED')
THEN fngetpoldate (a.polno, NULL, 'POLICYDATE_EXPIRED')
WHEN fngetrefseqno ('POLICYSTAT_D_DEATH_CLAIM_PENDING')
THEN fngetpoldate (a.polno,
NULL,
'POLICYDATE_DEATH_CLAIM_PENDING'
)
WHEN fngetrefseqno ('POLICYSTAT_D_DEATH_CLAIM_DECLINED')
THEN fngetpoldate (a.polno,
NULL,
'POLICYDATE_DEATH_CLAIM_DECLINED'
)
WHEN fngetrefseqno ('POLICYSTAT_DP_DEATH_CLAIM_PROCESSED')
THEN fngetpoldate (a.polno,
NULL,
'POLICYDATE_DEATH_CLAIM_PROCESSED'
)
ELSE NULL
END statdate,
parsename_temp (l.namestr, 'LFM', 'FML') AGENT,
fngetagentbasebranchname (k.agtno) branch,
CASE
WHEN INSTR (i.remarks, 'APL ') > 0
THEN 'Thru PDO - APL'
WHEN INSTR (i.remarks, 'APD ') > 0
THEN 'Thru PDO - APD'
WHEN INSTR (i.remarks, 'RPU') > 0
THEN 'Thru PDO - RPU'
WHEN INSTR (i.remarks, 'ETI') > 0
THEN 'Thru PDO - ETI'
WHEN i.remarks IN
('LAPSED POLICY', 'DAILYMODE COMPLETED')
THEN 'LAPSED'
WHEN TRUNC (i.duedate) = TRUNC (i.valuedate)
THEN 'ON DUE DATE'
WHEN TRUNC (i.valuedate) - TRUNC (i.duedate) <= 31
THEN 'WITHIN CONTRACT GRACE'
WHEN TRUNC (i.valuedate) - TRUNC (i.duedate) <= 45
THEN 'WITHIN EXTENDED GRACE'
WHEN TRUNC (i.valuedate) - TRUNC (i.duedate) > 45
THEN 'BEYOND 45 DAYS'
ELSE 'OTHERS'
END reptype
FROM inb_policymst a,
inb_polcov_trn b,
cnb_planmst c,
inb_poldate_trn d,
inb_polrole_trn e,
cnb_namelst_trn f,
inb_polrole_trn g,
cnb_namelst_trn h,
(SELECT polno, valuedate duedate, --trandate valuedate,
(CASE a1.reftype
WHEN 61700
THEN TRUNC (a1.trandate)
WHEN 61701
THEN TRUNC (a1.trandate)
WHEN 60893
THEN TRUNC (a1.trandate)
WHEN 60894
THEN (CASE
WHEN TRUNC (a1.timestmp) < '11APR2007'
THEN TRUNC (a1.trandate)
ELSE (CASE TO_CHAR
((SELECT opno
FROM xac_opmst
WHERE opno =
a1.REFERENCE
AND timestmp >=
'11APR2007')
)
WHEN TO_CHAR (a1.REFERENCE)
THEN TRUNC
((SELECT valuedate
FROM xac_opmst
WHERE opno =
a1.REFERENCE
AND timestmp >=
'11APR2007')
)
ELSE TRUNC (a1.trandate)
END
)
END
)
WHEN 70520
THEN (CASE
WHEN TRUNC (a1.timestmp) < '11APR2007'
THEN TRUNC (a1.trandate)
ELSE (CASE TO_CHAR
((SELECT DISTINCT orno
FROM xbc_ormst
WHERE orno =
a1.REFERENCE
AND timestmp >=
'11APR2007')
)
WHEN TO_CHAR (a1.REFERENCE)
THEN TRUNC
((SELECT MAX (paydate)
FROM xbc_ormst
WHERE orno =
a1.REFERENCE
AND timestmp >=
'11APR2007')
)
ELSE TRUNC (a1.trandate)
END
)
END
)
WHEN 70521
THEN (CASE
WHEN TRUNC (a1.timestmp) < '11APR2007'
THEN TRUNC (a1.trandate)
ELSE (CASE a1.source_type
WHEN 60893
THEN TRUNC (a1.trandate)
WHEN 70521
THEN (CASE a1.REFERENCE
WHEN TO_CHAR (0)
THEN TRUNC
(a1.trandate
)
WHEN 'MM'
THEN TRUNC
(a1.trandate
)
WHEN 'RA'
THEN TRUNC
(a1.trandate
)
WHEN 'EA'
THEN TRUNC
(a1.trandate
)
WHEN 'AB'
THEN TRUNC
(a1.trandate
)
WHEN 'RE'
THEN TRUNC
(a1.trandate
)
WHEN 'LO'
THEN TRUNC
(a1.trandate
)
WHEN 'JP'
THEN TRUNC
(a1.trandate
)
ELSE (CASE TO_CHAR
((SELECT jvno
FROM xac_jvmst
WHERE jvno =
a1.REFERENCE
AND status IN
(60088,
60087)
AND timestmp >=
'11APR2007')
)
WHEN TO_CHAR
(a1.REFERENCE
)
THEN TRUNC
((SELECT valuedate
FROM xac_jvmst
WHERE jvno =
a1.REFERENCE
AND status IN
(60088,
60087)
AND timestmp >=
'11APR2007')
)
ELSE TRUNC
(a1.trandate
)
END
)
END
)
ELSE TRUNC (a1.trandate)
END
)
END
)
END
) valuedate,
CASE reftype
WHEN 60893
THEN (SELECT a2.remarks
FROM cxx_autoproc_jobreport a2
WHERE a2.polno = a1.polno
AND a2.jobhist_seqno = a1.REFERENCE
AND a2.ptd = a1.valuedate)
WHEN 70521
THEN CASE a1.source_type
WHEN 60893
THEN (SELECT a4.remarks
FROM cxx_autoproc_jobreport a4
WHERE a4.polno = a1.polno
AND TO_CHAR (a4.jobhist_seqno) =
a1.source_no
AND a4.ptd = a1.valuedate)
ELSE (SELECT a3.explanation
FROM xac_jvmst a3
WHERE a3.jvno = a1.REFERENCE
AND ROWNUM = 1)
END
ELSE 'REGULAR PREMIUM PAYMENT'
END remarks,
reftype, source_type,
SUM (NVL (premamt, 0) + NVL (dst, 0)) premamt
FROM xbc_prempay_trn a1
WHERE NVL (reverse_flag, 'N') = 'N' AND reftype <> '60894'
GROUP BY polno,
reftype,
REFERENCE,
valuedate,
trandate,
source_type,
source_no,
TRUNC (timestmp)
UNION ALL
SELECT b1.polno, b1.poldate duedate, b2.valuedate,
NVL
(b2.remarks,
CASE
WHEN fngetpolicystatus (b1.polno) = 101
THEN 'FULLY-PAID POLICY'
ELSE 'NOT YET DUE'
END
) remarks,
NULL reftype, NULL source_type,
fngetmodalpremium (b1.polno)
+ fngetmodaldst (b1.polno) premamt
FROM inb_poldate_trn b1 LEFT OUTER JOIN cxx_autoproc_jobreport b2
ON b1.polno = b2.polno
AND b1.poldate = b2.ptd
AND b2.remarks IN
('LAPSED POLICY', 'ETI PROCESS COMPLETED',
'RPU PROCESS COMPLETED')
WHERE b1.datetype = 148
AND b1.poldate NOT IN (SELECT b3.valuedate
FROM xbc_prempay_trn b3
WHERE b3.polno = b1.polno)) i,
inb_polrole_trn j,
xag_profile k,
cnb_namelst_trn l
WHERE a.polno = b.polno
AND b.enddate IS NULL
AND b.plancode = c.plancode
AND c.typecode = 367
AND a.polno = d.polno
AND d.datetype = 147
AND a.polno = e.polno
AND e.enddate IS NULL
AND e.pertype = 180
AND e.nameid = f.nameid
AND a.polno = g.polno
AND g.enddate IS NULL
AND g.pertype = 180
AND g.nameid = h.nameid
AND a.polno = i.polno
AND a.polno = j.polno
AND i.duedate between '01jul2010' and '15jul2010'
AND j.pertype = 174
AND j.enddate IS NULL
AND j.nameid =
CASE (SELECT DISTINCT COUNT (*)
FROM inb_polrole_trn
WHERE polno = a.polno
AND pertype = 174
AND enddate IS NULL)
WHEN 1
THEN j.nameid
ELSE CASE (SELECT COUNT (DISTINCT dist)
FROM inb_polrole_trn
WHERE polno = a.polno
AND pertype = 174
AND enddate IS NULL)
WHEN 1
THEN j.nameid
ELSE (SELECT nameid
FROM inb_polrole_trn
WHERE polno = a.polno
AND pertype = 174
AND enddate IS NULL
AND dist = 100)
END
END
AND j.nameid = k.nameid
AND j.nameid = l.nameid
AND fnagentstattodate (k.agtno, SYSDATE) IN (78, 79, 85, 607)
AND (d.poldate > i.duedate or d.poldate < i.duedate)
AND i.remarks NOT IN
('NOT YET DUE', 'FULLY-PAID POLICY',
'Change in Mode ' || a.polno)
AND ( ( fngetmodalpremptd (a.polno, i.duedate)
+ fngetmodaldstptd (a.polno, i.duedate)
)
- i.premamt <= .01
OR i.reftype = 70520
OR i.source_type = 60893
);
|
|
|
|
| Re: help me tune this [message #498799 is a reply to message #498768] |
Fri, 11 March 2011 04:07  |
cookiemonster
Messages: 13965
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
You want us to help tune a 374 line sql statement, with lots of function calls and complicated case statemnts, while we know nothing of the tables, data or indexes involved?
Read the sticky at the top of this forum and post the required information and we'll see what we can do. One thing I did notice:
The distinct is pointless.
|
|
|
|
|
|
Goto Forum:
Current Time: Wed Feb 19 14:34:34 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
