oracle创建函数慢,【讨论】运用自定义函数，sql非常慢，求大神帮忙优化一下！！...

自定函数：

fc_ls_get_ub：

create or replace function fc_ls_get_ub

(

subscriberkey  in number,

accountkey     in number,

credittype     in number

--  iv_date        in varchar2

)

return number

is

vv_subscriberkey   number;

vv_accountkey      number;

vv_credittype      number;

--vv_date            varchar2(8);

v_amt0             number;

begin

vv_subscriberkey:= subscriberkey;

vv_credittype   := credittype;

--vv_date         :=iv_date;

vv_accountkey   :=accountkey;

if vv_credittype=1 then

select abs(mid.amount) into v_amt0 from lbi_ls_umniah.t_l_acct_subscreditused_mid mid

where mid.subscriberkey=vv_subscriberkey;

-- and cred.data_date=vv_date;

--end if;

elsif vv_credittype=0 then

select abs(mid.amount) into v_amt0 from lbi_ls_umniah.t_l_acct_acctcreditused_mid mid

where mid.accountkey=vv_accountkey

--and mid.accountkey=vv_accountkey

--and cred.data_date=vv_date

and rownum=1;                  ---+1

end if;

return nvl(v_amt0,0);

end fc_ls_get_ub;

执行计划：

SQL> select

2        mid.subscriberkey         ,

mid.msisdn                ,

3    4        mid.accountkey            ,

5        mid.usertype              ,

mid.credittype            ,

6    7        nvl(fc_ls_get_ub(mid.subscriberkey,mid.accountkey,mid.credittype),0)

8     from lbi_ls_umniah.t_l_acct_cust_sub_credit_mid mid

9  /

117649 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 3251901932

--------------------------------------------------------------------------------------------------

| Id  | Operation         | Name                         | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |                              |   147K|    11M|   289   (2)| 00:00:04 |

|   1 |  TABLE ACCESS FULL| T_L_ACCT_CUST_SUB_CREDIT_MID |   147K|    11M|   289   (2)| 00:00:04 |

--------------------------------------------------------------------------------------------------

Note

-----

- dynamic sampling used for this statement

Statistics

----------------------------------------------------------

117654  recursive calls

0  db block gets

30684759  consistent gets

0  physical reads

0  redo size

4358480  bytes sent via SQL*Net to client

86793  bytes received via SQL*Net from client

7845  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

117649  rows processed