经典等价改写5（避免函数调用）

笔者接下来要描述的，是一个典型的优化场景，关于对SQL中带函数的写法的一些认识。一般来说，我们尽量避免SQL中带函数，可以考虑将函数的写法转化成表连接的写法。因为前者会产生大量的递归调用，往往表的记录有多少条，函数就被调用多少次，性能会特别糟，请看构造的例子如下：

首先是构造环境。

drop table people purge;

drop table sex purge;

create table people (first_name varchar2(200),last_name varchar2(200),sex_id number);

create table sex (name varchar2(20), sex_id number);

insert into people (first_name,last_name,sex_id) select object_name,object_type,1 from dba_objects;

insert into sex (name,sex_id) values (‘男’,1);

insert into sex (name,sex_id) values (‘女’,2);

insert into sex (name,sex_id) values (‘不详’,3);

commit;

create or replace function get_sex_name(p_id sex.sex_id%type) return sex.name%type is

v_name sex.name%type;

begin

select name

into v_name

from sex

where sex_id=p_id;

return v_name;

end;

/

接下来以下两种写法是等价的，都是为了查询people 表信息，同时通过sex 表，获取人员的性别信息，前者是调用函数，后者是直接两表连接。

写法1

select sex_id,

first_name||’ '||last_name full_name,

get_sex_name(sex_id) gender

from people;

写法2

select p.sex_id,

p.first_name||’ '||p.last_name full_name,

sex.name

from people p, sex

where sex.sex_id=p.sex_id;

通过autotrace 比较观察发现两种写法性能上存在巨大差异，首先跟踪调用函数的写法：

set autotrace traceonly statistics

select sex_id,

first_name||’ '||last_name full_name,

get_sex_name(sex_id) gender

from people;

已选择111120行。

统计信息

---

 111120  recursive calls

      0  db block gets

 785917  consistent gets

      0  physical reads

      0  redo size

3974937  bytes sent via SQL*Net to client

  81893  bytes received via SQL*Net from client

   7409  SQL*Net roundtrips to/from client

      0  sorts (memory)

      0  sorts (disk)

 111120  rows processed 

接下来跟踪表连接的写法。

select p.sex_id,

p.first_name||’ '||p.last_name full_name,

sex.name

from people p, sex

where sex.sex_id=p.sex_id;

已选择111120行。

统计信息

---

      0  recursive calls

      0  db block gets

   8084  consistent gets

      0  physical reads

      0  redo size

3974935  bytes sent via SQL*Net to client

  81893  bytes received via SQL*Net from client

   7409  SQL*Net roundtrips to/from client

      0  sorts (memory)

      0  sorts (disk)

 111120  rows processed 

结论：

这里性能差异非常明显，究其本质原因，就是函数写法进行了过多的函数调用，影响了性能。