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 Go to next message
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 #575365 is a reply to message #575364] Tue, 22 January 2013 09:38 Go to previous messageGo to next message
BlackSwan
Messages: 21978
Registered: January 2009
Senior Member
Since NOBODY can optimize SQL just by looking at it, we need a few more details.
http://www.orafaq.com/forum/m/433888/136107/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof
Re: taking latest current balance using analytical Query [message #575368 is a reply to message #575364] Tue, 22 January 2013 09:58 Go to previous messageGo to next message
Solomon Yakobson
Messages: 1806
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 Go to previous messageGo to next message
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 #575370 is a reply to message #575368] Tue, 22 January 2013 10:09 Go to previous messageGo to next message
jollyboy
Messages: 5
Registered: January 2013
Junior Member
Thanks for your reply,

I want to get the latest balance for a given date (i.e. 05) for particular month.

I have removed the to_date in value_date since it is date datatype still the cost is not reduced

[Updated on: Tue, 22 January 2013 10:11]

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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 1806
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 #575456 is a reply to message #575373] Wed, 23 January 2013 07:38 Go to previous messageGo to next message
jollyboy
Messages: 5
Registered: January 2013
Junior Member
Thankx for all replies...Sorry for late reply..

The curr_balance is for starting from 5th of a month to 5th of next month for n months.
So the task is to get the curr_balance for srl_num and value_date equal to max(srl_num),max(value_date) from value date less than 05-MM-YYYY (lastest srl_num and last value date).

ex:

srl_num value_date curr_balance
------- ---------- ------------
1 01/02/2012 1000
2 04/02/2012 2000
3 04/02/2012 3000
4 10/02/2012 4000
5 15/02/2012 5000
6 30/02/2012 10000

The expected output is 3000. Also Query will be executed on 30 date or any date greated than 5th for a given month.



Re: taking latest current balance using analytical Query [message #575470 is a reply to message #575456] Wed, 23 January 2013 08:25 Go to previous messageGo to next message
Solomon Yakobson
Messages: 1806
Registered: January 2010
Senior Member
jollyboy wrote on Wed, 23 January 2013 08:38
The 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.
Re: taking latest current balance using analytical Query [message #575566 is a reply to message #575470] Thu, 24 January 2013 03:21 Go to previous messageGo to next message
jollyboy
Messages: 5
Registered: January 2013
Junior Member
Thanks its working fine....Whether the cost can be reduced further ?
explain plan for the same is
Plan
SELECT STATEMENT ALL_ROWSCost: 22 Bytes: 42 Cardinality: 1 CPU Cost: 10,598,022 IO Cost: 20
4 SORT ORDER BY Cost: 22 Bytes: 42 Cardinality: 1 CPU Cost: 10,598,022 IO Cost: 20
3 SORT GROUP BY Cost: 22 Bytes: 42 Cardinality: 1 CPU Cost: 10,598,022 IO Cost: 20
2 TABLE ACCESS BY INDEX ROWID TABLE TRANSACTION_DETAILS Cost: 20 Bytes: 42 Cardinality: 1 CPU Cost: 167,403 IO Cost: 20
1 INDEX RANGE SCAN INDEX IDX_ACCT_NUM Cost: 3 Cardinality: 28 CPU Cost: 27,164 IO Cost: 3
Re: taking latest current balance using analytical Query [message #575567 is a reply to message #575566] Thu, 24 January 2013 03:25 Go to previous message
Michel Cadot
Messages: 57650
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.

Regards
Michel

Previous Topic: difference between dates
Next Topic: decode using multiple values
Goto Forum:
  


Current Time: Thu Apr 24 15:45:31 CDT 2014

Total time taken to generate the page: 0.18443 seconds