子查询中的filter剖析

在oracle 8i时候，子查询展开为表连接有很多的限制，所以经常我们会听见in和exist的写法会影响sql的性能，会看见很多文章提到一个论点是：in操作是优先对子查询做操作，然后驱动外部的查询，exists则是优先进行外部的查询，然后去驱动子查询， 这个结论真的

在oracle 8i时候，子查询展开为表连接有很多的限制，所以经常我们会听见in和exist的写法会影响sql的性能，会看见很多文章提到一个论点是：in操作是优先对子查询做操作，然后驱动外部的查询，exists则是优先进行外部的查询，然后去驱动子查询，

这个结论真的正确吗，我们来看看如果我们关闭子查询展开为表连接的特性，再来看看是否真的如上述所述：
SQL> create table xiaoyu03 as select * from dba_objects;
SQL> create table xiaoyu04 as select * from dba_objects where rownum<10;
SQL> explain plan for select /*+optimizer_features_enable('8.1.7')*/* from xiaoyu04 a where a.object_id in (select /*+no_unnest*/object_id from xiaoyu03 b where b.object_type='TABLE');

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2861815236

---------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 16 | 3312 | 1 |
|* 1 | FILTER | | | | |
| 2 | TABLE ACCESS FULL| XIAOYU04 | 16 | 3312 | 1 |
|* 3 | TABLE ACCESS FULL| XIAOYU03 | 1 | 11 | 24 |
---------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "XIAOYU03" "B"
WHERE "OBJECT_ID"=:B1 AND "B"."OBJECT_TYPE"='TABLE'))
3 - filter("OBJECT_ID"=:B1 AND "B"."OBJECT_TYPE"='TABLE')

Note
-----
- cpu costing is off (consider enabling it)

我们发现了虽然关闭了子查询为表连接，优化器也回到了8I的版本，这里只是表连接的方式改为了filter，然后依然是以外部的表作为驱动表，子查询的表作为被驱动表来做关联的，这个对于之前我们推断in一定先走子查询为驱动表是不合理的：
1 子查询展开为表连接下，此时是两个结果集的关联，优化器为选择少的结果集为驱动表，in的方式不能保证此时优化器一定先选择子查询为驱动表
2 子查询不展开表连接下，此时优化器会选择filter关联方式，优化器选择了主查询的表为驱动表，做filter关联子查询的结果集，in的方式依然不能保证此时优化器一定先走子表

可能有朋友对上面的论点2有异议：优化器这里做了一个查询转换，将in改写为了exists的方式，这个好像是为了满足优化器的驱动准则，就是用小的结果集去做驱动表，这里xiaoyu04比较少，所以这里选择用xiaoyu04去做驱动表是最佳的，而in的写法优化器得先走子表xiaoyu03，然后再走主表xiaoyu04，查询转换in为exists后，优化器可以先走xiaoyu04然后走xiaoyu03了，这个貌似看起来非常合理了，事实真是如下？

这里我们改变一下查询的关系，xiaoyu03是大表在主查询中，xiaoyu04是小表在子查询中，优化器回到11g的版本，而且此时我们还建立xiaoyu03的object_id的一个高效索引，再来看下no_unnest hint作用下的关联顺序：
SQL> create index ind_objid_xiaoyu03 on xiaoyu03(object_id);

Index created.
SQL> explain plan for select * from xiaoyu03 a where a.object_id in(select /*+no_unnest*/b.object_id from xiaoyu04 b where b.object_type='TABLE');

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2805971548

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 88 | 14764 (1)| 00:02:58 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL| XIAOYU03 | 9811 | 843K| 43 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| XIAOYU04 | 1 | 10 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "XIAOYU04" "B"
WHERE "B"."OBJECT_ID"=:B1 AND "B"."OBJECT_TYPE"='TABLE'))
3 - filter("B"."OBJECT_ID"=:B1 AND "B"."OBJECT_TYPE"='TABLE')

17 rows selected.
还是以主查询的表为驱动表做filter关联。

同样我们再来看看exists是否也是如此，关闭子查询为表连接后，优化器回到11g的版本，exists的sql语句能否将小结果集的子查询作为驱动表，而将大结果集的外部表作为被驱动表了：
SQL> explain plan for select * from xiaoyu03 a where exists(select /*+no_unnest*/1 from xiaoyu04 b where b.object_type='TABLE' and a.object_id=b.object_id);

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2805971548

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 88 | 14764 (1)| 00:02:58 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL| XIAOYU03 | 9811 | 843K| 43 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| XIAOYU04 | 1 | 10 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "XIAOYU04" "B"
WHERE "B"."OBJECT_ID"=:B1 AND "B"."OBJECT_TYPE"='TABLE'))
3 - filter("B"."OBJECT_ID"=:B1 AND "B"."OBJECT_TYPE"='TABLE')

17 rows selected.

看来对于关闭子查询为表连接后，exists确实还是只能先扫描外部的表为驱动表，然后去驱动子查询的表。

这里再次要推翻我们之前的理解：就是优化器这里选择驱动表是考虑了选择小的驱动结果集为驱动表，会通过查询转换in和exists来满足小的结果集去做驱动表。

那么优化器为什么无论in还是exists的方式，在关闭子查询为表连接的特性后，都是选择的外部的表作为驱动表了，其实解释这个现象的根本原因是关联方式filter。

filter究竟如何工作：
表关联方式filter跟nested loop是相同的，只是filter维护了一个hash table用于记录了满足主表和子表关联的关联列和这个关联列相对应的这行数据的部分列。

确实很不好解释这个filter，我们举个简单的例子吧：比如查询是select * from xiaoyu03 a where exists(select /*+no_unnest*/1 from xiaoyu04 b where b.object_type='TABLE' and a.object_id=b.object_id);

oracle这里如果走fiter的关联方式，先从xiaoyu03表取出一条数据，然后a.object_id=b.object_id和b.object_type='TABLE'关联，如果满足，则记录a.object_id的值到hash table，返回满足这个关联条件的a.object_id对应的数据列；然后重复的取出所有xiaoyu03表的数据，如果发现下一个a.object_id已经记录到了hash table中则优化器不再需要去和xiaoyu04表做关联，而是直接再次返回a.object_id对应的这行数据列，这种filter关联方式只能选择主表做驱动表，所以我们看见了在关闭子查询转换为表连接后in和exists对应的filter执行计划都是以外部的表去做驱动表。

所以一般而言filter相比nested loop高效一点，对于重复的满足的关联列节省被驱动表的IO消耗。

本文出自：http://www.dbaxiaoyu.com, 原文地址：http://www.dbaxiaoyu.com/archives/2360, 感谢原作者分享。