How to replace user defined function into SELECT clause?

Question

I have a performance issue in my query which takes more than 2 minutes to execute. Currently the main table f_trans has 3 million records.

I have indexed columns in where clause and join condition.

Query :

    SELECT f.no AS refno,  
               f.v_date AS v_date,  
               (SELECT fnbalance  
                                     (  
                                      f.acnt_code,  
                                      f.v_date,                                      
                                     )  
                    FROM DUAL) AS balance,  

        FROM   f_trans f JOIN glr_temp glr  
             ON f.acnt_code = glr.acnt_code  
             AND ftr.v_date >= '24-Aug-2014'  
             AND ftr.v_date <= '27-Aug-2014'  
               JOIN glm_gl_mast glm  
               ON f.acnt_code = glm.acnt_code  
             AND glr.acnt_code = glm.acnt_code;  

function:

    CREATE OR REPLACE function fnbalance (  
p_glcode in   number,p_dtdate in   date,  
)  
   return number  
as  
   openbal   number;  
   dramt     number;  
   cramt     number;  

begin  

   dramt := 0;  
   cramt := 0;  
   balamt := 0;  
                  select nvl (sum (f.dr_amt), 0), nvl (sum (f.cr_amt), 0)  
                    into dramt, cramt  
                    from ftrans ftr  
                   where f.v_source <> 'FFT'    
                     and f.acnt_code = p_glcode  
                     and ftr.ftr_vou_date < p_dtdate;  

   select nvl(( dramt - cramt),0) into balamt from dual;  
   return balamt;  
end  


    Plan hash value: 1037159964

-------------------------------------------------------------------------------------------
| Id  | Operation              | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                  |    53 |  1537 | 23581   (1)| 00:04:43 |
|   1 |  FAST DUAL             |                  |     1 |       |     2   (0)| 00:00:01 |
|*  2 |  HASH JOIN             |                  |    53 |  1537 | 23581   (1)| 00:04:43 |
|*  3 |   HASH JOIN            |                  |  7342 | 73420 |    12   (9)| 00:00:01 |
|   4 |    INDEX FAST FULL SCAN| IND_GLR_ACC      |  5214 | 26070 |     5   (0)| 00:00:01 |
|   5 |    INDEX FAST FULL SCAN| GLM_AC_INDEX     |  7342 | 36710 |     6   (0)| 00:00:01 |
|*  6 |   TABLE ACCESS FULL    | F_TRANS |   181K|  3364K| 23568   (1)| 00:04:43 |
-------------------------------------------------------------------------------------------

Could help me to re organize the function into select clause?

Answer 1

AND ftr.v_date >= '24-Aug-2014'
AND ftr.v_date <= '27-Aug-2014'

First of all, this way of comparing a date with a literal is too bad. v_date being a DATE data type, you must compare it with dates and not literals. Use TO_DATE to convert a literal into date.

AND ftr.v_date >= TO_DATE('24-Aug-2014', 'DD-Mon-YYYY')
AND ftr.v_date <= TO_DATE('27-Aug-2014', 'DD-Mon-YYYY')

Say this to yourself, never rely on implicit data conversions. Always do it explicitly.

And please post the execution plan of the query to further see if the performance could be improved.

Update

See how to generate an execution plan in SQL*Plus

SQL> explain plan for select * from dual;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 272002086

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

8 rows selected.

SQL>

Copy paste the execution plan and post it here using code tags to preserve the formatting.