本文共 22476 字，大约阅读时间需要 74 分钟。
在进行基于代价的查询转换时，转换器先确认查询是否满足转换条件。一旦满足，就会对各种可行的转换方式进行枚举，并对它们进行代价估算，找到代价最低的方式。由此可见，相对于启发式查询转换，基于代价的查询转换是一个相当消耗资源（CPU和内存）的过程。
提示：Oracle中有一个优化器参数_OPTIMIZER_COST_BASED_TRANSFORMATION，用它来控制是否进行基于代价的查询转换，以及如何进行基于代价的查询转换，从而限制其对资源的消耗。查询转换器对含有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中，主查询直接获取其数据。
转载地址：http://xrupx.baihongyu.com/