慎用自定义函数

Careful Use of User-Written Functions

将自定义函数（User-Written Function）嵌到SQL语句后，它可能被调用相当多次。如果在select语句的选出项列表中使用自定义函数，则每返回一行数据就会调用一次该函数。如果自定义函数出现在 where 子句中，则每一行数据要成功通过过滤条件都会调用一次该函数；如果此时其他过滤条件的筛选能力不够强，自定义函数被调用的次数就非常可观了。

如果自定义函数内部还要执行一个查询，会发生什么情况呢？每次函数调用都将执行此内部查询。实际上，这和关联子查询（correlated subquery）效果相同，只不过自定义函数的方式阻碍了基于开销的优化器（cost-based optimizer，CBO）对整个查询的优化效果，因为“子查询”隐藏在函数中，数据库优化器鞭长莫及。

下面举例说明将SQL语句隐藏在自定义函数中的危险性。表flights描述商务航班，有航班号、起飞时间、到达时间及机场 IATA 代码（注5）等字段。IATA代码均为三个字母，有9 000多个，它们的解释保存在参照表中，包含城市名称（若一个城市有多个机场则应为机场名称）、国家名称等。显然，显示航班信息时，应该包含目的城市的机场名称，而不是简单的 IATA 代码。

在此就遇到了现代软件工程中的矛盾之一。被认为是“优良传统”的模块化编程一般情况下非常适用，但对数据库编程而言，代码是开发者和数据库引擎的共享活动（shared activity），模块化要求并不明确。例如，我们可以遵循模块化原则编写一个小函数来查找 IATA 代码，并返回完整的机场名称：

create or replace function airport_city(iata_code in char)

return varchar2

is

  city_name  varchar2(50);

begin

  select city

  into city_name

  from iata_airport_codes

  where code = iata_code;

  return(city_name);

end;

/

对于不熟悉 Oracle 语法的读者，在此做个说明，以下查询中trunc(sysdate)的返回值为“今天的 00:00 a.m.”，日期计算以天为单位；所以起飞时间的条件是指今天 8:30 a.m. 至 4:00 p.m. 之间。调用airport_city函数的查询可以非常简单，例如：

select flight_number,

       to_char(departure_time, 'HH24:MI') DEPARTURE,

       airport_city(arrival) "TO"

from flights

where departure_time between trunc(sysdate) + 17/48

                         and trunc(sysdate) + 16/24

order by departure_time

/

这个查询的执行速度令人满意；在我机器上的随机样本中，返回77行数据只用了0.18 秒（多次执行的平均值），用户对这样的速度肯定满意（统计数据表明，此处理访问了

303个数据块，53个是从磁盘读出的——而且每行数据有个递归调用）。

我们还可以用join来重写这段代码，作为查找函数的替代方案，当然它看起来会稍微复杂些：

select f.flight_number,

       to_char(f.departure_time, 'HH24:MI') DEPARTURE,

       a.city "TO"

from flights f,

     iata_airport_codes a

where a.code = f.arrival

  and departure_time between trunc(sysdate) + 17/48

                         and trunc(sysdate) + 16/24

order by departure_time

/

这个查询只用了 0.05 秒（统计数据同前，但没有递归调用）。对于执行时间不到 0.2 秒的查询来说，速度快了3倍似乎无关紧要，但在大型系统中，这些查询每天经常执行数十万次——假设以上查询每天只执行五万次，于是查询的总耗时为 2.5 小时。若不使用上述查找函数（lookup function）则只需要不到 42 分钟，速度提高超过300%，这对大数据量的系统意义重大，最终带来经济上的节约。通常，使用查找函数会使批处理程序的性能极差。而且查询时间的增加，会使同一台机器支持的并发用户数减少，我们将在第9章对此展开讨论。

总结：优化器对自定义函数的代码无能为力。