CBO 查询变化(1):子查询展开机能（Subquery Unnesting）
编者按：
本文作者系杨昱明，现就职于甲骨文公司，从事数据库方面的技术支持。希望能通过发表文章，把一些零散的知识再整理整理。个人主页:https://blog.csdn.net/weixin_50513167，经其本人授权发布。
【免责声明】本公众号文章仅代表个人观点，与任何公司无关。
有时候用户写的 SQL，对于优化器来说并不一定是最好的，可能作出的执行计划不会用到合适的结合处理。
所以，CBO 会在作执行计划之前，用一堆十分难懂的机能去转换用户作的 SQL。对于这些转换机能想做一些浅显的整理总结，也希望同时学习的小伙伴们给与斧正。
首先来说说相对简单一点子查询展开机能（Subquery Unnesting）。
子查询展开机能（Subquery Unnesting）
通常情况下，SQL 的特点是用到了 IN，NOT IN, EXISTS, NOT EXISTS 子句。
举个例子来说明，假如不使用子查询展开机能的话，执行计划就会像下面处理一样，先会对子查询进行 filter，之后再用 filter 结果对出查询进行 filter，取出数据集。这个处理过程和用户的逻辑一致吧。
drop table t1 purge;drop table t2 purge;create table t1(c1 number, c2 number not null);create table t2(c1 number primary key, c2 number not null);insert into t1 values (1,1);insert into t1 values (1,2);insert into t2 values (1,2);commit;
SQL> select t1.* from t1 where c2 in (select /*+ NO_UNNEST */ c2 from t2);C1 C2---------- ----------1 2Execution Plan----------------------------------------------------------Plan hash value: 895956251---------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 2 | 52 | 6 (0)| 00:00:01 ||* 1 | FILTER | | | | | || 2 | TABLE ACCESS FULL| T1 | 2 | 52 | 3 (0)| 00:00:01 ||* 3 | TABLE ACCESS FULL| T2 | 1 | 13 | 3 (0)| 00:00:01 |---------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------1 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "T2" "T2" WHERE"C2"=:B1))3 - filter("C2"=:B1)SQL> select t1.* from t1 where c2 not in (select /*+ NO_UNNEST */ c2 from t2);C1 C2---------- ----------1 1Execution Plan----------------------------------------------------------Plan hash value: 895956251---------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 26 | 6 (0)| 00:00:01 ||* 1 | FILTER | | | | | || 2 | TABLE ACCESS FULL| T1 | 2 | 52 | 3 (0)| 00:00:01 ||* 3 | TABLE ACCESS FULL| T2 | 1 | 13 | 3 (0)| 00:00:01 |---------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------1 - filter( NOT EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "T2" "T2"WHERE "C2"=:B1))3 - filter("C2"=:B1)
这种情况下，显然没有直接用 JOIN 来 access 更有效。
所以，子查询展开机能隆重登场，下面我们来看一下，利用子查询展开机能后，上面处理会变成什么样。
IN 子句里面的表直接同主查询的表进行了 SEMI 结合，SEMI 结合可以理解为满足 access(“C2”=“C2”) 条件的数据集。
SQL> select t1.* from t1 where c2 in (select c2 from t2);C1 C2---------- ----------1 2Execution Plan----------------------------------------------------------Plan hash value: 1713220790---------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 39 | 6 (0)| 00:00:01 ||* 1 | ***HASH JOIN SEMI*** | | 1 | 39 | 6 (0)| 00:00:01 || 2 | TABLE ACCESS FULL| T1 | 2 | 52 | 3 (0)| 00:00:01 || 3 | TABLE ACCESS FULL| T2 | 1 | 13 | 3 (0)| 00:00:01 |---------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------1 - access("C2"="C2")
NOT IN 子句里面的表直接同主查询的表进行了 ANTI 结合，ANTI 结合可以理解为不满足 access(“C2”=“C2”) 条件的数据集。
但是，这里埋了一个不小的雷，就是这里没有涉及到 NULL (C2 列是 NOT NULL)，之后我们再聊 NULL 的情况。
SQL> select t1.* from t1 where c2 not in (select c2 from t2);C1 C2---------- ----------1 1Execution Plan----------------------------------------------------------Plan hash value: 2706079091---------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 2 | 78 | 6 (0)| 00:00:01 ||* 1 | ***HASH JOIN ANTI*** | | 2 | 78 | 6 (0)| 00:00:01 || 2 | TABLE ACCESS FULL| T1 | 2 | 52 | 3 (0)| 00:00:01 || 3 | TABLE ACCESS FULL| T2 | 1 | 13 | 3 (0)| 00:00:01 |---------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------1 - access("C2"="C2")
上面2个例子，假如取一下 10053 trace 的话，会发现用户的 SQL 都会转换成下面这个 SQL，T1 和 T2 进行 JOIN。作出来的执行计划里，IN 的时候是 SEMI JOIN ，NOT IN 的时候是 ANTI JOIN。
Final query after transformations:******* UNPARSED QUERY IS *******SELECT "T1"."C1" "C1","T1"."C2" "C2" FROM "U1"."T2" "T2","U1"."T1" "T1" WHERE "T1"."C2"="T2"."C2"
大家感受到子查询展开机能的效果了吗-。
那如何关闭子查询展开机能呢？有以下两种方法：
隐含参数 _UNNEST_SUBQUERY 设置成 falseOR最开始例子里面用到的 NO_UNNEST hint。
后续文章更加精彩，欢迎关注本公众号或访问【阅读原文】。
——End——
专注于技术不限于技术！
用碎片化的时间，一点一滴地提高数据库技术和个人能力。
欢迎关注！
数据库基础系列（从基础了解数据库）：
通过寄存服务来“理解”Oracle数据库基本体系结构和动作流程
手把手系列（帮助个人技术成长）：
原文作者：SQL和数据库技术；如有侵权，请联系管理员删除。
