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

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

本来关于 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” 来控制的。