经典等价改写6（减少函数调用）

虽然说我们要设法避免进行函数调用，但是很多时候我们是无法避免的，比如有一些逻辑特别复杂，核心人员对其做了函数封装，成为开发公约的模式交给开发人员去调用，这时基本上开发人员就很难转化成普通的表关联方式了。

不过避免不了调用的情况下，我们还可以设法减少调用，请看构造的系列例子：

首先是构造环境

drop table t1 purge;

drop table t2 purge;

create table t1 as select * from dba_objects;

create table t2 as select * from dba_objects;

update t2 set object_id=rownum;

commit;

create or replace function f_deal1(p_name in varchar2)

return varchar2 deterministic

is

v_name varchar2(200);

begin

– select substr(upper(p_name),1,4) into v_name from dual;

v_name:=substr(upper(p_name),1,4);

return v_name;

end;

/

create or replace function f_deal2(p_name in varchar2)

return varchar2 deterministic

is

v_name varchar2(200);

begin

select substr(upper(p_name),1,4) into v_name from dual;

– v_name:=substr(upper(p_name),1,4);

return v_name;

end;

/

来观察如下两个等价语句的性能差异，首先看写法1：

set autotrace traceonly statistics

set linesize 1000

select name from (select rownum rn ,f_deal2(t1.object_name) name from t1) where rn<=12;

统计信息

---

 111113  recursive calls

      0  db block gets

   1724  consistent gets

      0  physical reads

      0  redo size

    558  bytes sent via SQL*Net to client

    416  bytes received via SQL*Net from client

      2  SQL*Net roundtrips to/from client

      0  sorts (memory)

      0  sorts (disk)

     12  rows processed 

接下来看写法2：

select f_deal2(t1.object_name) name from t1 where rownum<=12;

统计信息

---

     12  recursive calls

      0  db block gets

      5  consistent gets

      0  physical reads

      0  redo size

    558  bytes sent via SQL*Net to client

    416  bytes received via SQL*Net from client

      2  SQL*Net roundtrips to/from client

      0  sorts (memory)

      0  sorts (disk)

rows processed

写法1的recursive calls为111113，写法2为12，虽然都无法避免递归调用，但是差距却是有天壤之别，显然写法2性能要好的多！这里构造的奥妙就在于前者是全部记录进行调用，而后者则是对结果集进行调用。类似的例子可以是聚合查询，如果在聚合查询前调函数，基表数据巨大，调用函数就很多。如果是在聚合后调用函数，则调用的次数就会少的多！

读者可能会说如果是where 条件带函数是什么情况呢？很好，观察的很仔细，确实前面的例子都没有出现在where过滤条件中。实际情况是，如果能建一个函数索引，可以大幅度降低递归调用。请看脚本3体如下：

脚本3：函数在where 条件的情况

当函数在where 条件调用，且在无建函数索引时，结果如下：

select * from t1 where f_deal2(t1.object_name)=‘AAAA’;

统计信息

---

 111111  recursive calls

      0  db block gets

   1723  consistent gets

      0  physical reads

      0  redo size

   1124  bytes sent via SQL*Net to client

    405  bytes received via SQL*Net from client

      1  SQL*Net roundtrips to/from client

      0  sorts (memory)

      0  sorts (disk)

      0  rows processed 

接下来，我们对f_deal2(t1.object_name)建函数，具体方法为：create index idx_func_deal2 on t1(f_deal2(t1.object_name))。当函数在where 条件调用，且有建函数索引时，结果如下：

统计信息

---

     0  recursive calls

     0  db block gets

  1723  consistent gets

     0  physical reads

     0  redo size

  1124  bytes sent via SQL*Net to client

   405  bytes received via SQL*Net from client

     1  SQL*Net roundtrips to/from client

     0  sorts (memory)

     0  sorts (disk)

     0  rows processed 


  递归调用从111111瞬间降低为0，递归调用被避免了，函数索引可以理解为一种空间换时间的优化方式，在这里威力甚大！