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
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大大的提高查询效率。