Home » SQL & PL/SQL » SQL & PL/SQL » taking latest current balance using analytical Query (Oracle 10g)
| taking latest current balance using analytical Query [message #575364] |
Tue, 22 January 2013 09:36  |
 |
jollyboy
Messages: 5
Registered: January 2013
|
Junior Member |
|
|
hi all,
I am having a table with 5 lakhs transactions. I want to fetch the last balance for a particular date. So i have have returned a query like below.
SELECT curr_balance
FROM transaction_details
WHERE acct_num = '10'
AND is_deleted = 'N'
AND ( value_date, srl_num ) IN(
SELECT MAX( value_date ), MAX( srl_num )
FROM transaction_details
WHERE TO_DATE( value_date, 'dd/mm/yyyy' )
<= TO_DATE( ADD_MONTHS( '05-APR-2012', 1 ), 'dd/mm/yyyy' )
AND acct_num = '10'
AND is_deleted = 'N'
AND ver_status = 'Y' )
AND ver_status = 'Y';
This has to be executed for incrementing of 12 months to find the last balance for each particular month. But this query is having more cpu cost, 12 times it is taking huge time. Please let me know how to remodify athe above query to get the results in faster way using analytical query. Whether this can be broken into two part in PL/SQL to achive the performance. ?
|
|
|
|
|
|
| Re: taking latest current balance using analytical Query [message #575368 is a reply to message #575364] |
Tue, 22 January 2013 09:58  |
Solomon Yakobson
Messages: 1404
Registered: January 2010
|
Senior Member |
|
|
One thing already pops up. Column value_date is a string, or at least is used as one. This prevents optimizer from using index on value_date (assuming such index exists). And if column value_date data type is, indeed, DATE your query is completely wrong. Also '05-APR-2012' is a string, not a data. I will assume column value_date is DATE. Then:
SELECT curr_balance
FROM transaction_details
WHERE acct_num = '10'
AND is_deleted = 'N'
AND (value_date,srl_num) IN (
SELECT MAX(value_date),
MAX(srl_num)
FROM transaction_details
WHERE value_date <= ADD_MONTHS(DATE '2012-04-05',1)
AND acct_num = '10'
AND is_deleted = 'N'
AND ver_status = 'Y'
)
AND ver_status = 'Y';
But I still believe it is wrong. Subquery returns MAX(value_date) and MAX(srl_num) but they can (and most likely will) come from two different rows. I have a feeling you want srl_num of a rows that has MAX(value_date). Then you need to use KEEP:
SELECT curr_balance
FROM transaction_details
WHERE acct_num = '10'
AND is_deleted = 'N'
AND (value_date,srl_num) IN (
SELECT MAX(value_date),
MAX(srl_num) KEEP(DENSE_RANK LAST ORDER BY value_date)
FROM transaction_details
WHERE value_date <= ADD_MONTHS(DATE '2012-04-05',1)
AND acct_num = '10'
AND is_deleted = 'N'
AND ver_status = 'Y'
)
AND ver_status = 'Y';
But main thing is, query you posted has nothing to do with "I want to fetch the last balance for incrementing of 12 months".
SY.
|
|
|
|
| Re: taking latest current balance using analytical Query [message #575369 is a reply to message #575365] |
Tue, 22 January 2013 10:05 |
|
jollyboy
Messages: 5
Registered: January 2013
|
Junior Member |
|
|
There is only one table which has index on acct_num,value_date, srl_num seperately.
Plan
SELECT STATEMENT ALL_ROWSCost: 44 Bytes: 47 Cardinality: 1 CPU Cost: 364,999 IO Cost: 44
8 TABLE ACCESS BY INDEX ROWID TABLE TRANSACTION_DETAILS Cost: 4 Bytes: 47 Cardinality: 1 CPU Cost: 30,158 IO Cost: 4
4 INDEX RANGE SCAN INDEX IDX_SRL_NUM Cost: 3 Cardinality: 1 CPU Cost: 21,764 IO Cost: 3
3 SORT AGGREGATE Bytes: 43 Cardinality: 1
2 TABLE ACCESS BY INDEX ROWID TABLE TRANSACTION_DETAILS Cost: 20 Bytes: 43 Cardinality: 1 CPU Cost: 167,421 IO Cost: 20
1 INDEX RANGE SCAN INDEX IDX_ACCT_NUM Cost: 3 Cardinality: 28 CPU Cost: 27,164 IO Cost: 3
7 SORT AGGREGATE Bytes: 43 Cardinality: 1
6 TABLE ACCESS BY INDEX ROWID TABLE TRANSACTION_DETAILS Cost: 20 Bytes: 43 Cardinality: 1 CPU Cost: 167,421 IO Cost: 20
5 INDEX RANGE SCAN INDEX IDX_ACCT_NUM Cost: 3 Cardinality: 28 CPU Cost: 27,164 IO Cost: 3
[Updated on: Tue, 22 January 2013 10:05] Report message to a moderator |
|
|
|
|
|
| Re: taking latest current balance using analytical Query [message #575373 is a reply to message #575370] |
Tue, 22 January 2013 10:59 |
Solomon Yakobson
Messages: 1404
Registered: January 2010
|
Senior Member |
|
|
As I said, before looking into performance you need to fix your query logic. Actually, you need to explain it first. You have transaction table with transaction for various dates. You want to get last balance for 12 last months, right? I will assume that there was at least one transaction each month. Now said that:
SELECT value_date,
curr_balance
FROM transaction_details
WHERE acct_num = '10'
AND is_deleted = 'N'
AND value_date >= ADD_MONTHS(TRUNC(SYSDATE,'MM'),-11)
AND ver_status = 'Y'
/
will give you all transaction withing last 12 months (starting February 1, 2012). For each of these months yoy want last balance. So you could use group by month with KEEP to get balance:
SELECT TO_CHAR(TRUNC(value_date,'MM'),'FMMonth, YYYY') yymm,
MAX(curr_balance) KEEP(DENSE_RANK LAST ORDER BY value_date) last_balance
FROM transaction_details
WHERE acct_num = '10'
AND is_deleted = 'N'
AND value_date >= ADD_MONTHS(TRUNC(SYSDATE,'MM'),-11)
AND ver_status = 'Y'
GROUP BY TRUNC(value_date,'MM')
ORDER BY TRUNC(value_date,'MM')
/
Or use analytic ROW_NUMBER:
WITH T AS (
SELECT value_date,
curr_balance,
ROW_NUMBER() OVER(PARTITION BY TRUNC(value_date,'MM') ORDER BY value_date DESC) rn
FROM transaction_details
WHERE acct_num = '10'
AND is_deleted = 'N'
AND value_date >= ADD_MONTHS(TRUNC(SYSDATE,'MM'),-11)
AND ver_status = 'Y'
)
SELECT TO_CHAR(TRUNC(value_date,'MM'),'FMMonth, YYYY') yymm,
value_date last_balance
FROM t
WHERE rn = 1
/
SY.
|
|
|
|
|
|
| Re: taking latest current balance using analytical Query [message #575470 is a reply to message #575456] |
Wed, 23 January 2013 08:25 |
Solomon Yakobson
Messages: 1404
Registered: January 2010
|
Senior Member |
|
|
jollyboy wrote on Wed, 23 January 2013 08:38The curr_balance is for starting from 5th of a month to 5th of next month for n months.
SELECT TO_CHAR(TRUNC(value_date - 4,'MM'),'FMMonth, YYYY') yymm,
MAX(curr_balance) KEEP(DENSE_RANK LAST ORDER BY value_date) last_balance
FROM transaction_details
WHERE acct_num = '10'
AND is_deleted = 'N'
AND value_date >= ADD_MONTHS(TRUNC(SYSDATE,'MM'),-11)
AND ver_status = 'Y'
GROUP BY TRUNC(value_date - 4,'MM')
ORDER BY TRUNC(value_date - 4,'MM')
/
Or:
SELECT TO_CHAR(TRUNC(value_date - 4,'MM'),'FMMonth, YYYY') yymm,
MAX(curr_balance) KEEP(DENSE_RANK LAST ORDER BY value_date) last_balance
FROM transaction_details
WHERE acct_num = '10'
AND is_deleted = 'N'
AND value_date >= ADD_MONTHS(TRUNC(SYSDATE,'MM'),-11)
AND ver_status = 'Y'
GROUP BY TRUNC(value_date -4,'MM')
ORDER BY TRUNC(value_date - 4,'MM')
/
SY.
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Fri May 24 18:40:48 CDT 2013
Total time taken to generate the page: 0.65156 seconds
|
] 
Members
Search
Help
Register
Login
Home
