ORACLE CBO 的 SQL 自动转换（Cost Based Transformations）之一

有时候用户写的 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          2


Execution 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          1


Execution 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          2


Execution 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          1


Execution 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 设置成 false

OR

最开始例子里面用到的 NO_UNNEST hint。