如何将用户定义的函数替换为SELECT子句？

Question

我的查询中有一个性能问题，需要超过2分钟才能执行。目前主表f_trans有300万条记录。

我在where子句和join条件中有索引列。

查询:

    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;  

函数：

    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 |
-------------------------------------------------------------------------------------------

能帮我把函数重新组织成select子句吗？

Answer 1

和ftr.v_date >=‘24-8月-2014年’ 和ftr.v_date <=‘27-8月-2014年’

首先，这种将日期与文字进行比较的方法太糟糕了。v_date是DATE数据类型，必须将其与日期而不是文字进行比较。使用TO_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')

请自言自语，从不依赖于隐式数据转换。总是明确地去做。

并请张贴查询的执行计划，以进一步查看性能是否可以提高。

更新

参见如何在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>

复制粘贴执行计划并在这里发布，使用代码标记来保留格式.