往期回顾：

• SQL优化经典等价改写1——case when 改造

• SQL优化经典等价改写2——rownum分页  

• SQL优化经典等价改写3——插入提速 

• SQL优化经典等价改写4——只取所需列 

• SQL优化经典等价改写5——避免函数调用 

1. 引言

虽然我们应尽量避免在SQL中使用函数调用，但在实际开发中，有些复杂逻辑已被封装为函数，成为开发规范，开发人员难以避免使用。面对这种情况，即使无法完全避免函数调用，我们仍可通过减少调用次数来显著提升性能。

2. 测试环境准备

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;
/

3. 案例一：函数调用位置优化

低效写法：对全部记录调用函数后再筛选

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

高效写法：先筛选记录后调用函数

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)
     12  rows processed

通过调整函数调用位置，递归调用从111,113次降至12次，差距达9,259倍！这是因为前者对全表数据调用函数，而后者仅对符合条件的12行调用函数。

4. 案例二：函数索引优化

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

创建函数索引

create index idx_func_deal2 on t1(f_deal2(t1.object_name));

WHERE条件中使用函数（有函数索引）

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

统计信息:

统计信息
--------
     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

创建函数索引后，递归调用从111,111次完全消除为0次！函数索引相当于用空间换时间，预先计算并存储函数结果，避免查询时重复计算。

5. 优化策略总结

1）调整函数调用位置

先过滤数据，再对结果集调用函数

适用于能够通过简单条件先减少数据量的场景

2）创建函数索引

对WHERE条件中频繁使用的函数创建索引

完全消除递归调用，显著提升性能

  3）聚合与函数调用顺序

类似案例一，在聚合查询中，应先聚合后调用函数

例如，对大表先做聚合再调用函数，而非对每行调用函数后再聚合

6. 结论

当我们无法避免使用函数时，可通过两种主要方式减少函数调用次数：

• 调整函数调用位置，先过滤再调用

• 创建函数索引，预计算函数结果

测试案例清晰显示：简单的调用位置调整可使性能提升近万倍，而函数索引则可完全消除递归调用。这两种方法在不改变既定开发规范的前提下，能大幅提升含函数SQL的性能。

未完待续...

     SQL优化经典等价改写5——避免使用触发器

公众号：收获不止数据库

微信：ljbyxl1213

• “大白话人工智能” 系列

• “数据库拍案惊奇” 系列

• “世事洞明皆学问” 系列