Oracle filter 函数,oracle执行计划中的filter

filter这个操作在《Cost Based Oracle Fundamental》此书第九章有介绍。filter的操作是对外表的每一行，都要对内表执行一次全表扫描，所以很多时候提到filter都会感到可怕。他其实很像我们熟悉的neested loop，但它的独特之处在于会维护一个hash table。其实filter 的性能实际上跟列值distinct数有关，oracle在执行的时候实际上做了很大优化，最坏情况下才会出现对外表每一行执行一次filter操作，如果distinct值比较少，那执行效率还是非常高的。甚至有可能比nl更高。

针对filter用一个简单的实例来解释一下：

假如表TMP_LIUHC_1和TMP_LIUHC_2，如果执行如下语句：

如果TMP_LIUHC_1里取出object_id=1，那么对于TMP_LIUHC_2来说即select 1 from TMP_LIUHC_2 where TMP_LIUHC_2.object_id*10=1,如果条件满足，那么对于子查询，输入输出对，即为(1(TMP_LIUHC_1.object_id),1(常量))。他存储在hash table里，并且由于条件满足，TMP_LIUHC_1.object_id=1被放入结果集。然后接着从TMP_LIUHC_1取出object_id=2，如果子查询依旧条件满足，那么子查询产生另一个输入和输出，即(2,1)，被放入hash table里；并且TMP_LIUHC_1.object_id=2被放入结果集。接着假设TMP_LIUHC_1里有重复的object_id，例如我们第三次从TMP_LIUHC_1取出的object_id=2,那么由于我们对于子查询来说，已经有输入输出对(2,1)在hash table里了，所以就不用去再次全表扫描TMP_LIUHC_2了，ORACLE非常聪明地知道object_id=2是结果集。这里，filter和neested loop相比,省去了一次全表扫描TMP_LIUHC_2。这个hash table是有大小限制的，当被占满的时候，后续新的TMP_LIUHC_1.object_id的FILTER就类似neested loop了。由此可见，从buffer gets层面上来看，FILTER是应该优于neested loop的，尤其当外部查询需要传递给子查询的输入(此例中为TMP_LIUHC_1.object_id)的distinct value非常小时，FILTER就会显得更优。即使在我这个例子中，TMP_LIUHC_1.object_id的distinct value上万，我对比了一下neested loop，FILTER仍然略优：

查看关联列的distinct值

SQL> select count(distinct object_type) from TMP_LIUHC_1;

COUNT(DISTINCTOBJECT_TYPE)

--------------------------

40

SQL> select count(distinct object_id) from TMP_LIUHC_1;

COUNT(DISTINCTOBJECT_ID)

------------------------

60947

下面使用filter

下面使用嵌套(NL)

完全跑不出来。。。。

由此可见，当外围

输入值(就是关联值)的distinct的值很少时，使用filter大大的提高查询效率。