深入SQL执行计划之CBO查询转换(6):子查询关联集展开机能（unnest correlation set subquery）

SQLplusDB

发布于 2022-08-19 20:36:38

2520

发布于 2022-08-19 20:36:38

文章被收录于专栏：Oracle数据库技术

编者按：

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

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

本来关于 CBO 的 SQL 自动转换的介绍计划在上一次就想收口了，但突然间又想到了几个机能，看来还是要继续弄吧。

子查询关联集展开机能（unnest correlation set subquery）

这个机能，我在 Google 上查了一下，分享的文章特别少，可能是很少被关注到吧。

话不多说，还是马上上例子，在没用到子查询关联集展开机能之前，下面的 Case 会是什么样子。

drop table t1 purge;
drop table t2 purge;
drop table drv purge;


create table t1(key, pad) as
select to_char(rownum*2-1), lpad(' ',50) from dual connect by level <= 100;


create table t2(key, pad) as
select to_char(rownum*2), lpad(' ',50) from dual connect by level <= 100;


create table drv(key, pad) as
select rownum, lpad(' ',500) from dual connect by level <= 5
union all
select -rownum, lpad(' ',500) from dual connect by level <= 1000;


exec dbms_stats.gather_table_stats(user, 'T1');
exec dbms_stats.gather_table_stats(user, 'T2');
exec dbms_stats.gather_table_stats(user, 'DRV');

SQL> alter session set "_optimizer_unnest_corr_set_subq" = FALSE;
SQL> select key from drv
where exists (
    select null
    from t1
    where drv.key = to_number(t1.key)
    union all
    select null
    from t2
    where drv.key = to_number(t2.key)
  );   2    3    4    5    6    7    8    9   10


       KEY
----------
         1
         2
         3
         4
         5

Execution Plan
----------------------------------------------------------
Plan hash value: 1881039188


----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |     5 |  3043   (1)| 00:00:01 |
|*  1 |  FILTER             |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL | DRV  |  1005 |  5025 |    23   (0)| 00:00:01 |
|   3 |   UNION-ALL         |      |       |       |            |          |
|*  4 |    TABLE ACCESS FULL| T1   |     1 |     4 |     3   (0)| 00:00:01 |
|*  5 |    TABLE ACCESS FULL| T2   |     1 |     4 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------


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


   1 - filter( EXISTS ( (SELECT NULL FROM "T1" "T1" WHERE
              TO_NUMBER("T1"."KEY")=:B1) UNION ALL  (SELECT NULL FROM "T2" "T2"
WHERE


              TO_NUMBER("T2"."KEY")=:B2)))
   4 - filter(TO_NUMBER("T1"."KEY")=:B1)
   5 - filter(TO_NUMBER("T2"."KEY")=:B1)

t1 是奇数表，t2 偶数表，drv 是1到5的正数和-1到-1000的负数。

没用到子查询关联集展开机能之前，执行计划和【子查询展开机能（Subquery Unnesting）】一节中讲到的没用子查询展开是的效果一样。即，用子查询的 filter 的结果来对主查询进行 filter，只不过这次，子查询中 t1，t2 表作了 UNION ALL。

这个 Case 有个特点是，drv 同 t1 和 t2 用了相同字段来做结合。

这种情况下，如何展开子查询来做结合处理呢，这就用到了子查询关联集展开机能。

SQL> alter session set "_optimizer_unnest_corr_set_subq" = TRUE;
SQL> select key from drv
where exists (
    select null
    from t1
    where drv.key = to_number(t1.key)
    union all
    select null
    from t2
    where drv.key = to_number(t2.key)
  );  2    3    4    5    6    7    8    9   10


       KEY
----------
         1
         2
         3
         4
         5


Execution Plan
----------------------------------------------------------
Plan hash value: 1657361037


--------------------------------------------------------------------------------
| Id  | Operation            | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |         |     1 |    18 |    29   (0)| 00:00:01 |
|*  1 |  HASH JOIN RIGHT SEMI|         |     1 |    18 |    29   (0)| 00:00:01 |
|   2 |   VIEW               | VW_SQ_1 |   200 |  2600 |     6   (0)| 00:00:01 |
|   3 |    UNION-ALL         |         |       |       |            |          |
|   4 |     TABLE ACCESS FULL| T1      |   100 |   400 |     3   (0)| 00:00:01 |
|   5 |     TABLE ACCESS FULL| T2      |   100 |   400 |     3   (0)| 00:00:01 |
|   6 |   TABLE ACCESS FULL  | DRV     |  1005 |  5025 |    23   (0)| 00:00:01 |
--------------------------------------------------------------------------------


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


   1 - access("DRV"."KEY"="VW_COL_1")

CBO 变换后的执行计划可以看出，t1，t2 表的 UNION ALL 的结果做成 View 之后和 drv 做 SEMI 结合。

那么到底是不是 CBO 内部自动转化了用户的 SQL 呢。还是看看 CBO 的 Trace 吧。

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "DRV"."KEY" "KEY" FROM  ( (SELECT TO_NUMBER("T1"."KEY") "ITEM_2" FROM "U1"."T1" "T1") UNION ALL  (SELECT TO_NUMBER("T2"."KEY") "ITEM_1" FROM "U1"."T2" "T2")) "VW_SQ_1","U1"."DRV" "DRV" WHERE "DRV"."KEY"="VW_SQ_1"."VW_COL_1"

没错，确实是自动转换了，t1 和 t2 做 UNION ALL 后以 View 的形式同 drv 结合。

上面的例子也可以看出来，这个机能是用隐藏参数 “_optimizer_unnest_corr_set_subq” 来控制的。

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2021-04-15，如有侵权请联系 cloudcommunity@tencent.com 删除

自动驾驶