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

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

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

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

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

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

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

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

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

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

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

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

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

既然前面的写法会导致函数频繁调用，我们修改一下sql写法，先把函数生成一个结果集。

从上图看，情况更加糟糕，但其实并不是我们的想法错了，而是Oracle太“聪明”了。从执行计划的filter中可以看出，Oracle重写了我们的sql，合并了子查询，基本上相当于又给改回了原样。下面加个hint来防止Oracle的这种自作聪明。

情况有了很大的改善。虽说与直接使用子查询方式相比，使用函数在一致读上还是大了一些，这个差别怀疑是和分区表有关（非分区表应该没有差别，未做验证），并且实际上可以通过调整执行计划来达到无差别，这里不做详细说明。

下面看一下索引的使用情况。

同样地，也是可以走索引的，但Oracle不会主动使用，需要用hint强制，理由前面已经提过了。需要注意的是，这里递归调用只有8次，和分区全扫描时的表现完全不同。

再看下面的语句。

这应该是比较合理的执行计划了。

综上所述，通常使用子查询的效率比用函数都要来得好一些，个别情况下用函数的效率很糟糕。

最高效的方法就是尽可能拆分成两个语句，用变量来代替子查询和函数。

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