| help me tune this [message #499148] |
Sun, 13 March 2011 18:46  |
xpact83
Messages: 225
Registered: October 2008
Location: philippines
|
Senior Member |
|
|
SELECT a.CURRENCY, a.REPORD, a.REPTYPE,
SUM(a.POLICY_COUNT) POLCOUNT, SUM(a.POLICY_COUNT)/TOTALCOUNT PCTCOUNT,
SUM(a.MODALPREM) MODALPREM, SUM(a.MODALPREM)/TOTALAMOUNT PCTAMOUNT
FROM (SELECT CURRENCY,
CASE REPTYPE
WHEN 'ON DUE DATE' THEN 1
WHEN 'WITHIN CONTRACT GRACE' THEN 2
WHEN 'WITHIN EXTENDED GRACE' THEN 3
WHEN 'BEYOND 45 DAYS' THEN 4
WHEN 'Thru PDO - RPU' THEN 5
WHEN 'Thru PDO - ETI' THEN 6
WHEN 'Thru PDO - APL' THEN 7
WHEN 'Thru PDO - APD' THEN 8
WHEN 'LAPSED' THEN 9
END REPORD,
REPTYPE, COUNT(POLNO) POLICY_COUNT, SUM(MODALPREM) MODALPREM
FROM View_PremiumPayment_Extract
WHERE DUEDATE BETWEEN '01Jul2010' AND '15Jul2010'
GROUP BY CURRENCY, REPTYPE
) a,
(SELECT CURRENCY, COUNT(POLNO) TOTALCOUNT
FROM View_PremiumPayment_Extract
WHERE DUEDATE BETWEEN '01Jul2010' AND '15Jul2010'
GROUP BY CURRENCY) b,
(SELECT CURRENCY, SUM(MODALPREM) TOTALAMOUNT
FROM View_PremiumPayment_Extract
WHERE DUEDATE BETWEEN '01Jul2010' AND '15Jul2010'
GROUP BY CURRENCY) c
WHERE a.CURRENCY = b.CURRENCY
AND a.CURRENCY = c.CURRENCY
and a.currency = 'PHP'
GROUP BY a.CURRENCY, a.REPORD, a.REPTYPE, b.TOTALCOUNT, c.TOTALAMOUNT
ORDER BY CURRENCY, REPORD
|
|
|
|
|
|
| Re: help me tune this [message #499194 is a reply to message #499148] |
Mon, 14 March 2011 00:52  |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
Although it is hard without knowing used data (column types, indexes, cardinality of the view?, ratio of filtered rows), there are some points which could improve that query:
- GROUP BY clause in the main query is useless - data is already aggregated in subqueries.
- Aggregates from B and C subqueries may be achieved with analytic forms of used aggregate functions directly in the main query - no need to scan VIEW_PREMIUMPAYMENT_EXTRACT three times.
- WHERE condition could be moved to subquery - although optimizer may do it itself.
- What is the data type of DUEDATE? As e.g. '14Mar2011' is BETWEEN '01Jul2010' AND '15Jul2010' ('01Jul2010' is string and strings are sorted in alphabetical order - see description e.g. on http://en.wikipedia.org/wiki/Alphabetical_order#Alphabetical_order), I wonder whether it filters data you want.
Of course, relevance of all points except the first one could be obtained from explain plan of that query, which is not present in your post too.
|
|
|
|
| Re: help me tune this [message #499247 is a reply to message #499148] |
Mon, 14 March 2011 04:55 |
michael_bialik
Messages: 621
Registered: July 2006
|
Senior Member |
|
|
Hi.
Flyboy already mentioned it : you are scanning the same data 3 times. Try following:
WITH a AS (SELECT CURRENCY,
CASE REPTYPE
WHEN 'ON DUE DATE' THEN 1
WHEN 'WITHIN CONTRACT GRACE' THEN 2
WHEN 'WITHIN EXTENDED GRACE' THEN 3
WHEN 'BEYOND 45 DAYS' THEN 4
WHEN 'Thru PDO - RPU' THEN 5
WHEN 'Thru PDO - ETI' THEN 6
WHEN 'Thru PDO - APL' THEN 7
WHEN 'Thru PDO - APD' THEN 8
WHEN 'LAPSED' THEN 9
END REPORD,
REPTYPE, COUNT(POLNO) POLICY_COUNT, SUM(MODALPREM) MODALPREM
FROM View_PremiumPayment_Extract
WHERE currency = 'PHP' AND
DUEDATE BETWEEN '01Jul2010' AND '15Jul2010'
GROUP BY CURRENCY, REPTYPE
),
B AS (SELECT CURRENCY, SUM(POLICY_COUNT) TOTALCOUNT,
SUM(MODALPREM) TOTALAMOUNT FROM a )
SELECT a.CURRENCY, a.REPORD, a.REPTYPE,
SUM(a.POLICY_COUNT) POLCOUNT, SUM(a.POLICY_COUNT)/TOTALCOUNT PCTCOUNT,
SUM(a.MODALPREM) MODALPREM, SUM(a.MODALPREM)/TOTALAMOUNT PCTAMOUNT
FROM a,
b
WHERE a.CURRENCY = b.CURRENCY
...
HTH
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
