编者按：

本文作者系杨昱明，现就职于甲骨文公司，从事数据库方面的技术支持。希望能通过发表文章，把一些零散的知识再整理整理。个人主页: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) asselect to_char(rownum*2-1), lpad(' ',50) from dual connect by level <= 100;

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

create table drv(key, pad) asselect rownum, lpad(' ',500) from dual connect by level <= 5union allselect -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 drvwhere 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 drvwhere 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"