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?

Do the following, EXPLAIN PLAN FOR <URQUERY> and then SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY) .. Paste the output here.. — Maheswaran Ravisankar
– Maheswaran Ravisankar, Commented Oct 8, 2014 at 12:00

Lalit Kumar B · Accepted Answer · 2014-10-08 12:07:13Z

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.

edited Oct 8, 2014 at 12:07

answered Oct 8, 2014 at 10:27

Lalit Kumar B

49.5k13 gold badges104 silver badges131 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

saran Over a year ago

Thanks lalit. I have modified but still the issue persists.

Lalit Kumar B Over a year ago

Post the execution plan as requested.

saran Over a year ago

i couldn't add my plan as image :(

Lalit Kumar B Over a year ago

image is not required. See my update in answer to see how to post an execution plan.

saran Over a year ago

i have posted the execution plan

|

Collectives™ on Stack Overflow

How to replace user defined function into SELECT clause?

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related