CBO 查询变化(1):子查询展开机能（Subquery Unnesting）

2022-08-19 20:27:29 浏览数 (1)

编者按：

本文作者系杨昱明，现就职于甲骨文公司，从事数据库方面的技术支持。希望能通过发表文章，把一些零散的知识再整理整理。个人主页:https://blog.csdn.net/weixin_50513167，经其本人授权发布。

【免责声明】本号文章仅代表个人观点，与任何公司无关。

有时候用户写的 SQL，对于优化器来说并不一定是最好的，可能作出的执行计划不会用到合适的结合处理。

所以，CBO 会在作执行计划之前，用一堆十分难懂的机能去转换用户作的 SQL。对于这些转换机能想做一些浅显的整理总结，也希望同时学习的小伙伴们给与斧正。

首先来说说相对简单一点子查询展开机能（Subquery Unnesting）。

子查询展开机能（Subquery Unnesting）

通常情况下，SQL 的特点是用到了 IN，NOT IN, EXISTS, NOT EXISTS 子句。

举个例子来说明，假如不使用子查询展开机能的话，执行计划就会像下面处理一样，先会对子查询进行 filter，之后再用 filter 结果对出查询进行 filter，取出数据集。这个处理过程和用户的逻辑一致吧。

代码语言：javascript复制
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;
代码语言：javascript复制
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”) 条件的数据集。

代码语言：javascript复制
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 的情况。

代码语言：javascript复制
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。

代码语言：javascript复制
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"

大家感受到子查询展开机能的效果了吗-。

那如何关闭子查询展开机能呢？有以下两种方法：

代码语言：javascript复制
隐含参数 _UNNEST_SUBQUERY 设置成 false
OR
最开始例子里面用到的 NO_UNNEST hint。
access sql

0 人点赞