编者按：
本文作者系杨昱明，现就职于甲骨文公司，从事数据库方面的技术支持。希望能通过发表文章，把一些零散的知识再整理整理。个人主页:https://blog.csdn.net/weixin_50513167，经其本人授权发布。
【免责声明】本号文章仅代表个人观点，与任何公司无关。
本来关于 CBO 的 SQL 自动转换的介绍计划在上一次就想收口了，但突然间又想到了几个机能，看来还是要继续弄吧。
子查询关联集展开机能（unnest correlation set subquery）
这个机能，我在 Google 上查了一下，分享的文章特别少，可能是很少被关注到吧。
话不多说，还是马上上例子，在没用到子查询关联集展开机能之前，下面的 Case 会是什么样子。
代码语言：javascript复制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 用了相同字段来做结合。
这种情况下，如何展开子查询来做结合处理呢，这就用到了子查询关联集展开机能。
代码语言：javascript复制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 吧。
代码语言：javascript复制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” 来控制的。
