改写的力量--SQL函数转表关联优化案例一则

在对某客户一经营分析系统做优化的时候，通过检查超长时间SQL发现了它，这是一个执行时间与数据处理量不相匹配的SQL，sql monitor显示如下：

查看sql，发现在返回列使用了一个函数，SQL代码简化如下：

insert into ...

select

......

fun_get_bas1_id (1, a.other_area, 'T_STD1_0007') OTHER_AREA_CODE,

......

FROM jsdss.bs_cdr_sms_loc_03 A,

(SELECT file_name

FROM BS_CDR_040200_FILE_TASK_INFO t

WHERE t.day_number = 2 AND cdr_type = 'SMS' AND GROUP_ID = 2

) b

WHERE a.file_name = b.file_name

AND ......;

函数的代码如下：

--主函数代码，里面涉及子函数的调用：

function fun_get_bas1_id(

i_rule_id in number,

i_bas2_id in varchar2,

i_t_table_name varchar2

) return varchar2 is

ret_value varchar2(10);

TYPE RefCurTyp IS REF CURSOR;

cv RefCurTyp;

r_bas1_id varchar2(10);

v_other varchar2(10);

BEGIN

if i_bas2_id is null then

v_other:=fun_get_other_id(i_t_table_name,i_rule_id);

ret_value:=v_other;

return(ret_value);

else

OPEN cv FOR 'SELECT bas1_id FROM '|| i_t_table_name||' where bas2_id=''' ||i_bas2_id||''' and rule_id='''||i_rule_id||''' and rownum<2';

FETCH cv INTO r_bas1_id;

if r_bas1_id is null or cv%notfound then

v_other:=fun_get_other_id(i_t_table_name,i_rule_id);

ret_value:=v_other;

else

ret_value:= r_bas1_id;

end if;

CLOSE cv;

return(ret_value);

end if;

end ;

--主函数使用的子函数代码：

function fun_get_other_id(

i_t_table_name in varchar2,

i_rule_id in number

) return varchar2 is

ret_value varchar2(10);

TYPE RefCurTyp IS REF CURSOR;

cv RefCurTyp;

r_bas1_id varchar2(10);

BEGIN

OPEN cv FOR 'SELECT bas1_id FROM '|| i_t_table_name||' where (trim(bas1_desc)=''其它'' or trim(bas1_desc)=''其他'') and rule_id='||i_rule_id;

FETCH cv INTO r_bas1_id;

if cv%notfound then

ret_value:=' ';

else

ret_value:= r_bas1_id;

end if;

CLOSE cv;

return(ret_value);

end ;

根据子函数的代码，如果other_area为null，则函数使用的sql为：

SELECT bas1_id FROM cmdss.T_STD1_0007 where (trim(bas1_desc)='其它' or trim(bas1_desc)='其他') and rule_id='1';

这个sql返回的固定值为900，接下来在改写的时候需要用到。

根据以上信息，可以用对该SQL进行改写：

1、将select 部分的fun_get_bas1_id (1, a.other_area, 'T_STD1_0007') OTHER_AREA_CODE

改为：decode(a.other_area,null,900,nvl(c.bas1_id,900)) OTHER_AREA_CODE

2、将cmdss.T_STD1_0007剔重(row_number()函数）后作为c表与a表做外关联：

(select * from (SELECT bas1_id,bas2_id,row_number() over (partition by bas2_id order by rowid) as rn FROM cmdss.T_STD1_0007 where rule_id='1') where rn=1) c ， a.other_area=c.bas2_id(+)

改写后的SQL代码如下（红色部分第一部分是替换，第二部分是增加）：

insert into ...

select

......

--fun_get_bas1_id (1, a.other_area, 'T_STD1_0007') OTHER_AREA_CODE,

decode(a.other_area,null,900,nvl(c.bas1_id,900)) OTHER_AREA_CODE,

......

FROM jsdss.bs_cdr_sms_loc_03 A,

(SELECT file_name

FROM BS_CDR_040200_FILE_TASK_INFO t

WHERE t.day_number = 2 AND cdr_type = 'SMS' AND GROUP_ID = 2

) b,

(select * from (SELECT bas1_id,bas2_id,row_number() over (partition by bas2_id order by rowid) as rn FROM cmdss.T_STD1_0007 where rule_id='1') where rn=1) c

WHERE a.file_name = b.file_name

AND a.other_area=c.bas2_id(+)

AND ......;

使用select部分对SQL改写前后的性能进行对比：改写前41.6分钟，改写后7.03秒，性能提升347倍。

总结：

SQL代码中，应尽可能使用表关联而不是使用自定义的一些包含select的一些函数。这个案例的性能对比充分的证明了这一点。