3.2 基于代价的查询转换
在进行基于代价的查询转换时，转换器先确认查询是否满足转换条件。一旦满足，就会对各种可行的转换方式进行枚举，并对它们进行代价估算，找到代价最低的方式。由此可见，相对于启发式查询转换，基于代价的查询转换是一个相当消耗资源（CPU和内存）的过程。
提示：Oracle中有一个优化器参数_OPTIMIZER_COST_BASED_TRANSFORMATION，用它来控制是否进行基于代价的查询转换，以及如何进行基于代价的查询转换，从而限制其对资源的消耗。
3.2.1 复杂视图合并
查询转换器对含有DISTINCT、GROUP BY的视图进行的合并称为复杂视图合并（Complex View Merging，CVM）。
提示：要进行复杂视图合并，需要确保视图合并（参数_complex_view_merging控制）功能和复杂视图合并功能（由优化器参数_complex_view_merging控制，默认为TRUE）都打开。
HELLODBA.COM>exec sql_explain('select * from t_users u, v_objects_sum o where u.username=o.owner and
u.created>:A', 'TYPICAL OUTLINE');
Plan hash value: 1302554469
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 819 | 3 (34)| 00:00:04 |
| 1 | HASH GROUP BY | | 9 | 819 | 3 (34)| 00:00:04 |
| 2 | NESTED LOOPS | | 4497 | 399K| 2 (0)| 00:00:03 |
| 3 | TABLE ACCESS BY INDEX ROWID| T_USERS | 2 | 172 | 1 (0)| 00:00:02 |
|* 4 | INDEX RANGE SCAN | T_USERS_IDX1 | 2 | | 1 (0)| 00:00:02 |
| 5 | BITMAP CONVERSION TO ROWIDS| | 2163 | 10815 | 2 (0)| 00:00:03 |
|* 6 | BITMAP INDEX SINGLE VALUE | T_OBJECTS_IDX4 | | | | |
------------------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
USE_NL(@"SEL$F5BB74E1" "T_OBJECTS"@"SEL$2")
LEADING(@"SEL$F5BB74E1" "U"@"SEL$1" "T_OBJECTS"@"SEL$2")
BITMAP_TREE(@"SEL$F5BB74E1" "T_OBJECTS"@"SEL$2" AND(("T_OBJECTS"."OWNER")))
INDEX_RS_ASC(@"SEL$F5BB74E1" "U"@"SEL$1" ("T_USERS"."CREATED"))
OUTLINE(@"SEL$2")
OUTLINE(@"SEL$1")
MERGE(@"SEL$2")
OUTLINE_LEAF(@"SEL$F5BB74E1")
OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("U"."CREATED">:A)
6 - access("U"."USERNAME"="OWNER")
在上例中，V_OBJECTS_SUM是含有GROUP BY子句的视图，与主查询合并后，视图中的对象与主查询中的对象直接关联。
3.2.2 关联谓词推入
关联谓词推入（Join Predicate Push-Down，JPPD）转换是基于代价的转换，如果没有特别说明，我们所说的关联谓词推入都是指新的关联谓词推入。
3.2.2.1 外关联的谓词推入
语句存在外关联匹配时，转换器可以将关联谓词条件推入视图的查询语句中，使之成为其子计划的访问谓词条件。
HELLODBA.COM>exec sql_explain('SELECT /*+ NO_MERGE(v) PUSH_PRED(v) */* FROM t_tables t, v_objects_sys
v WHERE t.owner =v.owner(+) and t.table_name = v.object_name(+) AND t.tablespace_name =
:A', 'TYPICAL OUTLINE');
Plan hash value: 980895126
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2033 | 567K| 558 (0)| 00:09:19 |
| 1 | NESTED LOOPS OUTER | | 2033 | 567K| 558 (0)| 00:09:19 |
|* 2 | TABLE ACCESS FULL | T_TABLES | 184 | 38272 | 6 (0)| 00:00:07 |
| 3 | VIEW PUSHED PREDICATE | V_OBJECTS_SYS | 1 | 78 | 3 (0)| 00:00:04 |
|* 4 | FILTER | | | | | |
| 5 | TABLE ACCESS BY INDEX ROWID| T_OBJECTS | 1 | 71 | 3 (0)| 00:00:04 |
|* 6 | INDEX SKIP SCAN | T_OBJECTS_IDX1 | 1 | | 2 (0)| 00:00:03 |
-------------------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
INDEX_SS(@"SEL$639F1A6F" "T_OBJECTS"@"SEL$2" ("T_OBJECTS"."STATUS" "T_OBJECTS"."OWNER"
"T_OBJECTS"."OBJECT_NAME"))
USE_NL(@"SEL$1" "V"@"SEL$1")
LEADING(@"SEL$1" "T"@"SEL$1" "V"@"SEL$1")
NO_ACCESS(@"SEL$1" "V"@"SEL$1")
FULL(@"SEL$1" "T"@"SEL$1")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$2")
OUTLINE_LEAF(@"SEL$1")
PUSH_PRED(@"SEL$1" "V"@"SEL$1" 3 2)
OUTLINE_LEAF(@"SEL$639F1A6F")
OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("T"."TABLESPACE_NAME"=:A)
4 - filter('SYS'="T"."OWNER")
6 - access("OWNER"="T"."OWNER" AND "OBJECT_NAME"="T"."TABLE_NAME")
filter("OBJECT_NAME"="T"."TABLE_NAME" AND "OWNER"="T"."OWNER" AND "OWNER"='SYS')
上述查询中，关联条件"OWNER"="T"."OWNER" AND "OBJECT_NAME"="T"."TABLE_NAME"被推入了视图的查询语句中，从而成为了其子计划的访问条件。
3.2.2.2 联合查询视图中关联查询的谓词推入
转换器将关联条件推入含有联合操作（UNION或者UNION-ALL）查询的视图中，并使之成为联合查询视图中两个子查询的谓词。
HELLODBA.COM>exec sql_explain('select * from (select /*+index(t2 t_tables_pk)*/t2.owner, t2.table_name
from t_tables t2 union all select /*+index(t1 t_objects_pk)*/t1.owner, t1.object_name
from t_objects t1) v, t_users t4 where v.owner=t4.username and t4.user_id =:a and v.table_name
like :b','TYPICAL OUTLINE');
Plan hash value: 316561174
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 61 | 7686 | 585 (0)| 00:09:46 |
| 1 | NESTED LOOPS | | 61 | 7686 | 585 (0)| 00:09:46 |
| 2 | TABLE ACCESS BY INDEX ROWID | T_USERS | 1 | 86 | 1 (0)| 00:00:02 |
|* 3 | INDEX UNIQUE SCAN | T_USERS_PK | 1 | | 1 (0)| 00:00:02 |
| 4 | VIEW | | 3 | 120 | 584 (0)| 00:09:45 |
| 5 | UNION ALL PUSHED PREDICATE | | | | | |
|* 6 | INDEX RANGE SCAN | T_TABLES_PK | 5 | 110 | 1 (0)| 00:00:02 |
|* 7 | TABLE ACCESS BY INDEX ROWID| T_OBJECTS | 108 | 3024 | 583 (0)| 00:09:44 |
| 8 | INDEX FULL SCAN | T_OBJECTS_PK | 47585 | | 60 (0)| 00:01:01 |
-----------------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
INDEX(@"SEL$639F1A6F" "T2"@"SEL$2" ("T_TABLES"."TABLE_NAME" "T_TABLES"."OWNER"))
INDEX(@"SEL$B01C6807" "T1"@"SEL$3" ("T_OBJECTS"."OBJECT_ID"))
USE_NL(@"SEL$1" "V"@"SEL$1")
LEADING(@"SEL$1" "T4"@"SEL$1" "V"@"SEL$1")
NO_ACCESS(@"SEL$1" "V"@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "T4"@"SEL$1" ("T_USERS"."USER_ID"))
OUTLINE(@"SEL$1")
OUTLINE(@"SET$1")
OUTLINE(@"SEL$3")
OUTLINE(@"SEL$2")
OUTLINE_LEAF(@"SEL$1")
PUSH_PRED(@"SEL$1" "V"@"SEL$1" 2)
OUTLINE_LEAF(@"SET$5715CE2E")
OUTLINE_LEAF(@"SEL$B01C6807")
OUTLINE_LEAF(@"SEL$639F1A6F")
OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T4"."USER_ID"=TO_NUMBER(:A))
6 - access("T2"."TABLE_NAME" LIKE :B AND "T2"."OWNER"="T4"."USERNAME")
filter("T2"."OWNER"="T4"."USERNAME" AND "T2"."TABLE_NAME" LIKE :B)
7 - filter("T1"."OWNER"="T4"."USERNAME" AND "T1"."OBJECT_NAME" LIKE :B)
上述查询中，关联条件V."OWNER"="T4"."USERNAME"被推入了视图中，并成为联合查询视图中子查询的谓词。
3.2.3 谓词提取
所谓谓词提取（Predicate Pull Up），是指将视图（子查询）谓词中复杂的、代价高昂的过滤条件提取出来，放到主查询中进行过滤。
提示：谓词提取可以通过优化器参数_optimizer_filter_pred_pullup及提示pull_pred/no_pull_pred控制。
HELLODBA.COM>begin
2 sql_explain('
3 SELECT /*+qb_name(outv) */ owner, table_name, rownum
4 FROM
5 (
6 SELECT /*+qb_name(inv)*/t.owner, t.table_name, t.last_analyzed
7 FROM t_tables t
8 WHERE (t.last_analyzed) < (SELECT /*+qb_name(subq)*/ MAX(created) FROM t_objects o)
9 AND owner like ''A%''
10 ORDER BY 1
11 )v','TYPICAL OUTLINE PREDICATE');
12 end;
13 /
Plan hash value: 2416283887
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 86 | 5 (0)| 00:00:01 |
| 1 | COUNT | | | | | |
|* 2 | VIEW | | 2 | 86 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| T_TABLES | 2 | 64 | 3 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | T_TABLES_IDX1 | 2 | | 2 (0)| 00:00:01 |
| 5 | SORT AGGREGATE | | 1 | 8 | | |
| 6 | INDEX FULL SCAN (MIN/MAX) | T_OBJECTS_IDX5 | 47585 | 371K| 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
INDEX(@"SUBQ" "O"@"SUBQ" ("T_OBJECTS"."CREATED"))
INDEX_RS_ASC(@"SEL$AA570DA2" "T"@"INV" ("T_TABLES"."OWNER"))
NO_ACCESS(@"SEL$9FF7933E" "V"@"OUTV")
OUTLINE(@"OUTV")
OUTLINE(@"INV")
OUTLINE(@"SUBQ")
OUTLINE_LEAF(@"SEL$9FF7933E")
PULL_PRED(@"OUTV" "V" 1)
OUTLINE_LEAF(@"SEL$AA570DA2")
OUTLINE_LEAF(@"SUBQ")
OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("LAST_ANALYZED"< (SELECT /*+ QB_NAME ("SUBQ") */ MAX("CREATED") FROM
"T_OBJECTS" "O"))
4 - access("OWNER" LIKE 'A%')
filter("OWNER" LIKE 'A%')
从上述内容可见，视图中的复杂谓词条件被提取出来。
3.2.4 GROUP BY配置
GROUP BY配置（Placement）是一项用一个GROUP BY视图来替换复杂查询中的一个或多个表的优化技术。要进行GROUP BY配置的查询转换，需要满足以下条件：
1）外部主查询的FROM子句中最少有两张表；
2）外部主查询包含了GROUP BY的操作；
3）外部主查询中包含了一个对某张表的一个字段的聚集函数的查询。
提示：GROUP BY配置可以通过优化器参数“_optimizer_group_by_placement”或提示PLACE_GROUP_BY/NO_PLACE_GROUP_BY控制。
示例如下（以下示例在11.2.0.1中运行）：
HELLODBA.COM>begin
2 sql_explain('
3 SELECT /*+ qb_name(m) place_group_by(@m (t@m)) */
4 owner, max(maxbytes)
5 FROM t_tables t, t_datafiles d
6 WHERE t.tablespace_name = d.tablespace_name
7 GROUP BY t.owner ',
8 'TYPICAL OUTLINE');
9 end;
10 /
Plan hash value: 4181908607
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 18 | 792 | 33 (10)| 00:00:01 |
| 1 | HASH GROUP BY | | 18 | 792 | 33 (10)| 00:00:01 |
|* 2 | HASH JOIN | | 68 | 2992 | 32 (7)| 00:00:01 |
| 3 | TABLE ACCESS FULL | T_DATAFILES | 6 | 180 | 3 (0)| 00:00:01 |
| 4 | VIEW | VW_GBF_1 | 102 | 1428 | 29 (7)| 00:00:01 |
| 5 | HASH GROUP BY | | 102 | 1428 | 29 (7)| 00:00:01 |
| 6 | TABLE ACCESS FULL| T_TABLES | 2696 | 37744 | 27 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
USE_HASH_AGGREGATION(@"SEL$78EA785B")
FULL(@"SEL$78EA785B" "T"@"M")
USE_HASH_AGGREGATION(@"SEL$3D2A8CF5")
USE_HASH(@"SEL$3D2A8CF5" "VW_GBF_1"@"SEL$30379648")
LEADING(@"SEL$3D2A8CF5" "D"@"M" "VW_GBF_1"@"SEL$30379648")
NO_ACCESS(@"SEL$3D2A8CF5" "VW_GBF_1"@"SEL$30379648")
FULL(@"SEL$3D2A8CF5" "D"@"M")
OUTLINE(@"M")
OUTLINE(@"SEL$30379648")
PLACE_GROUP_BY(@"M" ( "T"@"M" ) 1)
OUTLINE_LEAF(@"SEL$3D2A8CF5")
OUTLINE_LEAF(@"SEL$78EA785B")
ALL_ROWS
DB_VERSION('11.2.0.1')
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ITEM_1"="D"."TABLESPACE_NAME")
在上例中，由于进行了GROUP BY配置转换，执行计划先对表T_TABLES进行了GROUP BY操作，然后再与表T_DATAFILES关联。
3.2.5 表扩张
在对分区表进行查询时，可能发生这样的情形：查询条件能够命中分区表上的本地分区索引，但此时本地分区索引的某个分区的索引发生异常，导致该分区上的索引无法被使用。在11gR2之前，这种情况下，该分区索引将彻底无法使用；而在11gR2之后，引入了表扩张（Table Expansion，TE）的查询转换技术，使得优化器可以针对索引状态对不同分区评估是否使用索引。
提示：表扩张可以通过优化器参数“_optimizer_table_expansion”或提示EXPAND_TABLE/NO_EXPAND_TABLE控制。
示例如下（以下示例在11.2.0.1中运行）：
HELLODBA.COM>alter index t_objects_list_idx3 modify partition part4 unusable;
Index altered.
HELLODBA.COM>exec sql_explain('select /*+EXPAND_TABLE(o)*/* from t_objects_list o','TYPICAL OUTLINE');
Plan hash value: 2631494874
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7077 | 1430K| 305 (1)| 00:00:04 | | |
| 1 | VIEW | VW_TE_1 | 7077 | 1430K| 305 (1)| 00:00:04 | | |
| 2 | UNION-ALL | | | | | | | |
| 3 | PARTITION LIST ALL | | 7076 | 691K| 300 (1)| 00:00:04 | 1 | 6 |
|* 4 | TABLE ACCESS FULL | T_OBJECTS_LIST | 7076 | 691K| 300 (1)| 00:00:04 | 1 | 6 |
| 5 | PARTITION LIST SINGLE| | 1 | 99 | 5 (0)| 00:00:01 |KEY(AP)|KEY(AP)|
| 6 | TABLE ACCESS FULL | T_OBJECTS_LIST | 1 | 99 | 5 (0)| 00:00:01 | 4 | 4 |
----------------------------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
FULL(@"SET$57F73675_2" "O"@"SEL$1")
FULL(@"SET$57F73675_1" "O"@"SEL$1")
NO_ACCESS(@"SEL$72AE2D8F" "VW_TE_1"@"SEL$72AE2D8F")
OUTLINE(@"SEL$1")
EXPAND_TABLE(@"SEL$1" "O"@"SEL$1")
OUTLINE(@"SET$57F73675")
OUTLINE_LEAF(@"SEL$72AE2D8F")
EXPAND_TABLE(@"SEL$1" "O"@"SEL$1")
OUTLINE_LEAF(@"SET$57F73675")
OUTLINE_LEAF(@"SET$57F73675_1")
OUTLINE_LEAF(@"SET$57F73675_2")
ALL_ROWS
DB_VERSION('11.2.0.1')
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("O"."OWNER" IS NULL OR "O"."OWNER"<>'SYSTEM')
上述例子中，表T_OBJECTS_LIST是一张列举分区表，其中PART4的列举值是'SYSTEM'。
HELLODBA.COM>insert into tmp_lob select to_lob(high_value) from dba_ind_partitions where index_name =
'T_OBJECTS_LIST_IDX3' and partition_name = 'PART4';
1 row created.
HELLODBA.COM>select to_char(b) from tmp_lob;
TO_CHAR(B)
-------------------------------------------------------------------------------------------------------
'SYSTEM'
查询转换器按照本地索引t_objects_list_idx3在不同分区上的状态将语句重写为一个UNION-ALL查询。其中，第一个子查询是访问所有分区，并过滤掉索引状态为UNUSABLE的分区（"O"."OWNER" IS NULL OR "O"."OWNER"<>'SYSTEM'）上的数据；第二个子查询是对单个分区（PARTITION LIST SINGLE）的访问，由查询计划中Pstart & Pstop可以知道是访问PART4。
3.2.6 关联因式分解
关联因式分解（Join factorization，JF）是11gR2中引入的新的查询重写技术，它可以将UNION/UNION-ALL查询中的子查询合并为一个内联视图。示例参见代码清单3-16。
提示：关联因式分解可以通过优化器参数“_optimizer_join_factorization”或提示FACTORIZE_JOIN/NO_FACTORIZE_JOIN控制。
代码清单3-16 关联因式分解
HELLODBA.COM>begin
2 sql_explain('
3 select /*+ qb_name(sb1) */ u.username, u.created, o.object_name from t_objects o, t_users u
4 where o.owner=u.username and u.lock_date=:A
5 union all
6 select /*+ qb_name(sb2) */ u.username, u.created, o.object_name from t_objects o, t_users u
7 where o.owner=u.username and u.lock_date=:B',
8 'TYPICAL OUTLINE');
9 end;
10 /
Plan hash value: 3854854956
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 81521 | 5891K| 295 (1)| 00:00:04 |
|* 1 | HASH JOIN | | 81521 | 5891K| 295 (1)| 00:00:04 |
| 2 | VIEW | VW_JF_SET$A6672D85 | 26 | 1118 | 6 (0)| 00:00:01 |
| 3 | UNION-ALL | | | | | |
|* 4 | TABLE ACCESS FULL| T_USERS | 13 | 325 | 3 (0)| 00:00:01 |
|* 5 | TABLE ACCESS FULL| T_USERS | 13 | 325 | 3 (0)| 00:00:01 |
| 6 | TABLE ACCESS FULL | T_OBJECTS | 72115 | 2183K| 288 (1)| 00:00:04 |
-------------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
FULL(@"SEL$FDE4A245" "U"@"SB2")
FULL(@"SEL$3335C0C6" "U"@"SB1")
USE_HASH(@"SEL$BCE2A4E7" "O"@"SB1")
LEADING(@"SEL$BCE2A4E7" "VW_JF_SET$A6672D85"@"SEL$94FBCE2D" "O"@"SB1")
FULL(@"SEL$BCE2A4E7" "O"@"SB1")
NO_ACCESS(@"SEL$BCE2A4E7" "VW_JF_SET$A6672D85"@"SEL$94FBCE2D")
OUTLINE(@"SET$1")
OUTLINE(@"SB1")
OUTLINE(@"SB2")
OUTLINE(@"SEL$67A59F16")
OUTLINE(@"SEL$E9EF0288")
FACTORIZE_JOIN(@"SET$1"("O"@"SB1" "O"@"SB2"))
OUTLINE(@"SET$27448025")
OUTLINE(@"SEL$94FBCE2D")
OUTLINE(@"SEL$4ECEF7CB")
MERGE(@"SEL$67A59F16")
OUTLINE_LEAF(@"SEL$BCE2A4E7")
OUTLINE_LEAF(@"SET$A6672D85")
OUTLINE_LEAF(@"SEL$3335C0C6")
OUTLINE_LEAF(@"SEL$FDE4A245")
ALL_ROWS
DB_VERSION('11.2.0.1')
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("O"."OWNER"="ITEM_1")
4 - filter("U"."LOCK_DATE"=:A)
5 - filter("U"."LOCK_DATE"=:B)
上述例子中，两个子查询被合并为一个对T_USERS查询的UNION-ALL内联视图，系统自动命名为VW_JF_SET$A6672D85，然后再与表T_OBJECTS做关联。
3.2.7 DISTINCT配置
对关联（JOIN）查询结果取DISTINCT值时，DISTINCT配置（Distinct Placement，DP）能用一个含有DISTINCT的内联视图对查询进行重写。这项查询重写技术在11gR2引入。
提示：DISTINCT配置可以由优化器参数“_optimizer_distinct_placement”或提示PLACE_DISTINCT/NO_PLACE_DISTINCT控制。
示例如下（以下示例在11.2.0.1中运行）：
HELLODBA.COM>exec sql_explain('select /*+full(u) full(t) place_distinct*/distinct t.tablespace_name,
u.account_status from t_tables t, t_users u where t.owner=u.username', 'TYPICAL OUTLINE');
Plan hash value: 800024757
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12 | 480 | 36 (9)| 00:00:01 |
| 1 | HASH UNIQUE | | 12 | 480 | 36 (9)| 00:00:01 |
|* 2 | HASH JOIN | | 102 | 4080 | 35 (6)| 00:00:01 |
| 3 | TABLE ACCESS FULL | T_USERS | 31 | 806 | 3 (0)| 00:00:01 |
| 4 | VIEW | VW_DTP_1B35BA0F | 102 | 1428 | 31 (4)| 00:00:01 |
| 5 | HASH UNIQUE | | 102 | 1428 | 31 (4)| 00:00:01 |
| 6 | TABLE ACCESS FULL| T_TABLES | 2696 | 37744 | 30 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
USE_HASH_AGGREGATION(@"SEL$58BE4727")
FULL(@"SEL$58BE4727" "T"@"SEL$1")
USE_HASH_AGGREGATION(@"SEL$6B08CE13")
USE_HASH(@"SEL$6B08CE13" "VW_DTP_1B35BA0F"@"SEL$1B35BA0F")
LEADING(@"SEL$6B08CE13" "U"@"SEL$1" "VW_DTP_1B35BA0F"@"SEL$1B35BA0F")
NO_ACCESS(@"SEL$6B08CE13" "VW_DTP_1B35BA0F"@"SEL$1B35BA0F")
FULL(@"SEL$6B08CE13" "U"@"SEL$1")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$1B35BA0F")
PLACE_DISTINCT(@"SEL$1" "T"@"SEL$1")
OUTLINE_LEAF(@"SEL$6B08CE13")
OUTLINE_LEAF(@"SEL$58BE4727")
ALL_ROWS
DB_VERSION('11.2.0.1')
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ITEM_1"="U"."USERNAME")
上述例子中，语句被转换为以下查询：
SELECT /*+ PLACE_DISTINCT ) FULL ("U") */
DISTINCT "VW_DTP_1B35BA0F"."ITEM_2" "TABLESPACE_NAME",
"U"."ACCOUNT_STATUS" "ACCOUNT_STATUS"
FROM (SELECT DISTINCT "T"."OWNER" "ITEM_1", "T"."TABLESPACE_NAME" "ITEM_2"
FROM "DEMO"."T_TABLES" "T") "VW_DTP_1B35BA0F",
"DEMO"."T_USERS" "U"
WHERE "VW_DTP_1B35BA0F"."ITEM_1" = "U"."USERNAME"
3.2.8 WITH子查询转换
该转换包括：创建临时表，将WITH子查询结果写入临时表，主查询中直接获取临时表中的数据。因此这一转换也称为WITH子查询实体化（Materialize）。该转换由参数“_with_subquery”控制。
提示：WITH子查询转换可以由参数“_with_subquery”或提示MATERIALIZE/INLINE控制。
HELLODBA.COM>exec sql_explain('with v as (select /*+ MATERIALIZE qb_name(wv) */* from t_objects o where
object_id<:A) select count(*) from v', 'BASIC OUTLINE');
Plan hash value: 2309780835
------------------------------------------------------------------
| Id | Operation | Name |
------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TEMP TABLE TRANSFORMATION | |
| 2 | LOAD AS SELECT | SYS_TEMP_0FD9D6601_F201F06C |
| 3 | TABLE ACCESS FULL | T_OBJECTS |
| 4 | SORT AGGREGATE | |
| 5 | VIEW | |
| 6 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6601_F201F06C |
------------------------------------------------------------------上例中，WITH子查询的结果被写入了临时表SYS_TEMP_0FD9D6601_F201F06C中，主查询直接获取其数据。
