转载

SQL效率之where子句中的子查询和函数

工作中，曾有同事问我以下 sql 的效率如何，这里扩展一下这个问题并进行分析，主要说明 where 子句中的子查询和函数执行次数及索引使用情况。

select * from trd_fundjour a

where oc_date = (select collect_date from hscon.sys_arg);

首先来看一下表的数据分布情况：

表 trd_fundjour 是分区表，里面是按月分区的，以oc_date为索引。

先看一下 201605 分区全扫描产生多少逻辑读。

SQL效率之where子句中的子查询和函数

这里产生了 88 个一致读，接下来执行前面的 sql 。

SQL效率之where子句中的子查询和函数

这里比前一次多出了 7 个一致读，也多出了对 sys_arg 表的扫描，单独查询 sys_arg 表看看。

SQL效率之where子句中的子查询和函数

一致读刚好是 7 个，从这里可以看出，子查询只执行了一次，而不是针对分区中的每条记录比较一次，因为这个分区中有 3279 条记录。

下面来看能否用上索引，这里强制走索引看看。

SQL效率之where子句中的子查询和函数

显然，在用 hint 强制的情况下索引是可以走的。就本例而言，走索引比全表扫描效率会更高，但在不用 hint 的情况下 Oracle 选的却是全表扫描。虽然从表面上看是选择了较差的执行计划，其实是正常的，因为在生成执行计划的时候 Oracle 得不到子查询的值，无法判断全表扫描和索引扫描哪个更优，因此选择全表扫描就无可厚非了。所以，在可能的情况下应该将这些非相关子查询执行结果赋给变量，把上面的 sql 拆成两个语句来执行。

下面来看一下函数的表现， hscon.f_get_collectdate 的实现逻辑就是封装前面 sql 中的子查询，返回查询结果。

SQL效率之where子句中的子查询和函数

将子查询改成函数，可以看到递归调用和一致读都大了很多，是哪里产生的呢？我们来单独查一下函数看看。

SQL效率之where子句中的子查询和函数

从这里可以看出，函数调用一次就产生 1 个递归调用和 7 个一致读。 3286*7 （函数执行一次 7 个一致读）加上 88 （分区全扫描产生的一致读）刚好是 23090 ，也就是说在前面的语句中函数执行了 3286 次！上文这一数字其实可以被拆分成 3279+7 两部分来看， 3279 是分区的记录数， 7 应该是和分区消除相关。我们可以得出这样的结论：针对分区中的每一条记录，函数都会调用一次进行比较。