《Oracle高性能SQL引擎剖析：SQL优化与调优机制详解》一3.1　启发式查询转换

xiaoxiao2022-05-12 151

3.1　启发式查询转换

所有的启发式查询转换都是基于一套优化器内建的规则。在查询转换阶段，转换器会逐个针对这些规则对查询进行检查，确定其是否满足转换规则，一旦满足，转换器就对其进行转换。

3.1.1　简单视图合并

我们知道，视图（View）的实质就是一条查询语句。在解析阶段，语句中的每个视图都会被展开至一个查询块中。如果未做视图合并，优化器则会单独分析每个视图，并为定义视图的查询语句生成一个视图子计划。然后再分析整个查询的其他部分，并生成执行计划。在这种情况下，由于视图的执行计划和整体执行计划不是同时统一做评估的，因此其最终计划可能不是最优的执行计划。使用视图合并技术后，优化器不再单独为每个视图生成子计划，而是将视图的查询合并到整体查询中去，最终为合并和整体查询寻找到一个最优的执行计划。要将视图查询合并到主查询中去，优化器会用视图所依赖的表的名字替换视图名字，并将视图查询中的WHERE条件合并到主查询的WHERE条件中去。根据子查询在主查询中的位置以及其与主查询中所引用的表的关系的不同，子查询分为以下几种：标量子查询（Scalar Subquery）：出现在SELECT列表中的子查询称为标量子查询；内联视图（Inline View）：出现在FROM子句中的视图称为内联视图；嵌套子查询（Nested Subquery）：出现在WHERE子句中的子查询称为嵌套子查询；互关联子查询（Correlated Subquery）：如果嵌套子查询是主查询WHERE条件的逻辑表达式的一部分（非IN、EXISTS子查询），并且嵌套子查询的查询条件中还包含主查询中表的字段，那么这样的子查询又称为互关联子查询。提示：是否进行视图合并，可以由优化器参数_simple_view_merging或者提示MERGE/NO_MERGE控制，默认为TRUE。视图合并又可以分为简单视图合并与复杂视图合并：对于“选择—投影—关联”（Select-Project-Join）的视图的合并称为简单视图合并（Simple View Merge），通常这种合并属于启发式查询转换，即只要视图合并特性被开启（_simple_view_merging=TRUE）都能被合并；对于含有DISTINCT、GROUP BY的视图的合并称为复杂视图合并（Complex View Merge），复杂视图合并通常属于基于代价的查询转换，最终是否合并取决于代价大小，因此我们将复杂视图合并放在下一节介绍。当存在以下情况时，不能进行视图合并：含有集合操作(UNION, UNION ALL, INTERSECT, MINUS)、聚集函数(AVG, COUNT, MAX, MIN, SUM等)、ROWNUM和CONNECT BY的视图则不能被合并；如果主查询语句中含有CURSOR表达式，则查询中的任何视图都不能被合并。3.1.1.1　内联视图合并我们以下面两个执行计划为例，简要说明视图合并技术对执行计划优化的影响，见代码清单3-1。代码清单3-1　内联视图合并

HELLODBA.COM>exec sql_explain('select /*+no_merge(o)*/* from t_tables t, v_objects_sys o where t.owner=o.owner and t.table_name = object_name and t.tablespace_name = :A and t.table_name like :B and o.status=:C', 'TYPICAL'); Plan hash value: 3284354748 ---------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 3 | 840 | 87 (3)| 00:00:01 | |* 1 | HASH JOIN | | 3 | 840 | 87 (3)| 00:00:01 | | 2 | TABLE ACCESS BY INDEX ROWID | T_TABLES | 9 | 1836 | 13 (8)| 00:00:01 | | 3 | BITMAP CONVERSION TO ROWIDS | | | | | | | 4 | BITMAP AND | | | | | | | 5 | BITMAP CONVERSION FROM ROWIDS| | | | | | |* 6 | INDEX RANGE SCAN | T_TABLES_IDX3 | 184 | | 1 (0)| 00:00:01 | | 7 | BITMAP CONVERSION FROM ROWIDS| | | | | | | 8 | SORT ORDER BY | | | | | | |* 9 | INDEX RANGE SCAN | T_TABLES_PK | 184 | | 2 (0)| 00:00:01 | | 10 | VIEW | V_OBJECTS_SYS | 571 | 43396 | 73 (0)| 00:00:01 | | 11 | TABLE ACCESS BY INDEX ROWID | T_OBJECTS | 571 | 47393 | 73 (0)| 00:00:01 | |* 12 | INDEX RANGE SCAN | T_OBJECTS_IDX1 | 103 | | 3 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("T"."OWNER"="O"."OWNER" AND "T"."TABLE_NAME"="OBJECT_NAME") 6 - access("T"."TABLESPACE_NAME"=:A) 9 - access("T"."TABLE_NAME" LIKE :B) filter("T"."TABLE_NAME" LIKE :B AND "T"."TABLE_NAME" LIKE :B) 12 - access("STATUS"=:C AND "OWNER"='SYS' AND "OBJECT_NAME" LIKE :B) filter("OBJECT_NAME" LIKE :B) PL/SQL procedure successfully completed. HELLODBA.COM>exec sql_explain('select * from t_tables t, v_objects_sys o where t.owner=o.owner and t.table_name = object_name and t.tablespace_name = :A and t.table_name like :B and o.status=:C', 'TYPICAL OUTLINE'); Plan hash value: 2603737735 ----------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 3 | 843 | 16 (7)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID | T_OBJECTS | 1 | 77 | 3 (0)| 00:00:01 | | 2 | NESTED LOOPS | | 3 | 843 | 16 (7)| 00:00:01 | | 3 | TABLE ACCESS BY INDEX ROWID | T_TABLES | 3 | 612 | 7 (15)| 00:00:01 | | 4 | BITMAP CONVERSION TO ROWIDS | | | | | | | 5 | BITMAP AND | | | | | | | 6 | BITMAP CONVERSION FROM ROWIDS| | | | | | |* 7 | INDEX RANGE SCAN | T_TABLES_IDX3 | 184 | | 1 (0)| 00:00:01 | | 8 | BITMAP CONVERSION FROM ROWIDS| | | | | | | 9 | SORT ORDER BY | | | | | | |* 10 | INDEX RANGE SCAN | T_TABLES_PK | 184 | | 2 (0)| 00:00:01 | |* 11 | INDEX RANGE SCAN | T_OBJECTS_IDX1 | 1 | | 2 (0)| 00:00:01 | ----------------------------------------------------------------------------------------------------- Outline Data ------------- /*+ BEGIN_OUTLINE_DATA USE_NL(@"SEL$F5BB74E1" "T_OBJECTS"@"SEL$2") LEADING(@"SEL$F5BB74E1" "T"@"SEL$1" "T_OBJECTS"@"SEL$2") INDEX(@"SEL$F5BB74E1" "T_OBJECTS"@"SEL$2" ("T_OBJECTS"."STATUS" "T_OBJECTS"."OWNER" "T_OBJECTS"."OBJECT_NAME")) BITMAP_TREE(@"SEL$F5BB74E1" "T"@"SEL$1" AND(("T_TABLES"."TABLESPACE_NAME") ("T_TABLES"."TABLE_NAME" "T_TABLES"."OWNER"))) 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): --------------------------------------------------- 7 - access("T"."TABLESPACE_NAME"=:A) 10 - access("T"."TABLE_NAME" LIKE :B AND "T"."OWNER"='SYS') filter("T"."OWNER"='SYS' AND "T"."TABLE_NAME" LIKE :B AND "T"."TABLE_NAME" LIKE :B) 11 - access("STATUS"=:C AND "OWNER"='SYS' AND "T"."TABLE_NAME"="OBJECT_NAME") filter("OBJECT_NAME" LIKE :B) PL/SQL procedure successfully completed. 示例分析：上面第一个执行计划是未使用视图合并（通过提示强制）所生成的执行计划。它为视图V_OBJECTS生成了一个子计划（即操作10～12）。该计划从视图查询语句及主查询中获得的谓词条件为（"STATUS"=:C AND "OWNER"='SYS' AND "OBJECT_NAME" LIKE :B），并且优化器估算出它会返回571条数据记录。在这种情况下，优化器选择其与另外一个表T_TABLES做哈希关联，估算代价为49。第二个执行计划使用了视图合并技术，在选择执行计划之前，用视图的依赖表T_OBJECTS替换了视图V_OBJECTS，并且将视图查询中WHERE条件"OWNER"='SYS'与主查询WHERE条件"STATUS"=:C和关联条件"T"."TABLE_NAME"="OBJECT_NAME"合并，最终决定由这些条件访问索引T_OBJECTS_IDX1后与表T_TABLES做嵌套关联，估算代价仅为9。 3.1.1.2　基于关联的含有标量子查询的视图合并对含有标量子查询的视图与主查询中的对象进行关联操作时发生的视图合并，参见代码清单3-2。代码清单3-2　基于关联的含有标量子查询的视图合并 HELLODBA.COM>begin 2 sql_explain('select /*+ qb_name(m) */v.*, o.subobject_name, o.status 3 from (select /*+ qb_name(inv) */table_name, owner, 4 (select/*+ qb_name(sca) */ u.default_tablespace from t_users u 5 where u.username=t.owner) def_ts 6 from t_tables t) v, 7 t_objects o 8 where v.owner = o.owner 9 and v.table_name = o.object_name', 10 'TYPICAL OUTLINE'); 11 end; 12 / Plan hash value: 954562591 ------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 47585 | 3671K| 55 (0)| 00:00:56 | | 1 | TABLE ACCESS BY INDEX ROWID| T_USERS | 1 | 14 | 1 (0)| 00:00:02 | |* 2 | INDEX UNIQUE SCAN | T_USERS_UK | 1 | | 1 (0)| 00:00:02 | | 3 | NESTED LOOPS | | 47585 | 3671K| 55 (0)| 00:00:56 | | 4 | TABLE ACCESS FULL | T_OBJECTS | 47585 | 2648K| 54 (0)| 00:00:55 | |* 5 | INDEX UNIQUE SCAN | T_TABLES_PK | 1 | 22 | 1 (0)| 00:00:02 | ------------------------------------------------------------------------------------------- Outline Data ------------- /*+ BEGIN_OUTLINE_DATA INDEX_RS_ASC(@"SCA" "U"@"SCA" ("T_USERS"."USERNAME")) USE_NL(@"SEL$F88DB697" "T"@"INV") LEADING(@"SEL$F88DB697" "O"@"M" "T"@"INV") INDEX(@"SEL$F88DB697" "T"@"INV" ("T_TABLES"."TABLE_NAME" "T_TABLES"."OWNER")) FULL(@"SEL$F88DB697" "O"@"M") OUTLINE(@"INV") OUTLINE(@"M") OUTLINE(@"SCA") MERGE(@"INV") OUTLINE_LEAF(@"SEL$F88DB697") OUTLINE_LEAF(@"SCA") OPT_PARAM('optimizer_index_cost_adj' 60) OPTIMIZER_FEATURES_ENABLE('10.2.0.4') IGNORE_OPTIM_EMBEDDED_HINTS END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("U"."USERNAME"=:B1) 5 - access("TABLE_NAME"="O"."OBJECT_NAME" AND "OWNER"="O"."OWNER") 在上例中，含有标量子查询（SCA）的子查询（INV）与主查询合并，它们的对象可以直接关联。 3.1.1.3　含有标量子查询的简单查询视图合并这种转换仅在11g及以上版本中有效。它是对含有标量子查询的视图的简单查询进行视图合并转换，见代码清单3-3。代码清单3-3　含有标量子查询的简单查询视图合并（11.2.0.1） HELLODBA.COM>begin 2 sql_explain('select /*+ qb_name(m) */* 3 from (select /*+ qb_name(inv) merge */table_name, 4 (select/*+ qb_name(sca) */ u.default_tablespace from t_users u 5 where u.username=t.owner) def_ts 6 from t_tables t) v 7 where v.def_ts like :A', 8 'TYPICAL OUTLINE'); 9 end; 10 / Plan hash value: 2354222482 -------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2696 | 75488 | 24 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID | T_USERS | 1 | 17 | 1 (0)| 00:00:01 | |* 2 | INDEX UNIQUE SCAN | T_USERS_UK | 1 | | 0 (0)| 00:00:01 | |* 3 | FILTER | | | | | | | 4 | INDEX FAST FULL SCAN | T_TABLES_PK | 2696 | 75488 | 6 (0)| 00:00:01 | | 5 | TABLE ACCESS BY INDEX ROWID| T_USERS | 1 | 17 | 1 (0)| 00:00:01 | |* 6 | INDEX UNIQUE SCAN | T_USERS_UK | 1 | | 0 (0)| 00:00:01 | -------------------------------------------------------------------------------------------- Outline Data ------------- /*+ BEGIN_OUTLINE_DATA INDEX_RS_ASC(@"SCA" "U"@"SCA" ("T_USERS"."USERNAME")) INDEX_RS_ASC(@"SCA" "U"@"SCA" ("T_USERS"."USERNAME")) INDEX_FFS(@"SEL$F88DB697" "T"@"INV" ("T_TABLES"."TABLE_NAME" "T_TABLES"."OWNER")) OUTLINE(@"INV") OUTLINE(@"M") OUTLINE(@"SCA") MERGE(@"INV") OUTLINE_LEAF(@"SEL$F88DB697") OUTLINE_LEAF(@"SCA") OUTLINE_LEAF(@"SCA") 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("U"."USERNAME"=:B1) 3 - filter( (SELECT /*+ QB_NAME ("SCA") */ "U"."DEFAULT_TABLESPACE" FROM "T_USERS" "U" WHERE "U"."USERNAME"=:B1) LIKE :A) 6 - access("U"."USERNAME"=:B1) 在上例中，含有标量子查询（SCA）的子查询（INV）与主查询合并，视图V被消除。 3.1.2　子查询反嵌套在对存在嵌套子查询的复杂语句进行优化时，查询转换器会尝试将子查询展开，使得其中的表能与主查询中的表进行关联（Join），从而获得更优的执行计划。部分子查询反嵌套（Subquery Unnesting）属于启发式查询转换，部分属于基于代价的转换，下面会做特别说明。提示：提示UNNEST/NO_UNNEST可以控制是否进行反嵌套。在11g中，也可以由优化器参数_optimizer_unnest_all_subqueries控制。 3.1.2.1　IN/EXISTS转换为SEMI JOIN 转换器将IN或EXISTS子句中的子查询展开（反嵌套），使得优化器能选择半关联（SEMI-JOIN）操作。这种转换属于启发式查询转换。示例如代码清单3-4所示。代码清单3-4　IN/EXISTS转换为SEMI JOIN HELLODBA.COM>exec sql_explain('select * from t_objects o where exists (select /*+qb_name(inv)*/1 from t_tables t where t.owner=o.owner and t.table_name=o.object_name)', 'TYPICAL OUTLINE'); Plan hash value: 1088835623 ---------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 23 | 2990 | 55 (0)| 00:00:56 | | 1 | NESTED LOOPS SEMI | | 23 | 2990 | 55 (0)| 00:00:56 | | 2 | TABLE ACCESS FULL| T_OBJECTS | 47585 | 5018K| 54 (0)| 00:00:55 | |* 3 | INDEX UNIQUE SCAN| T_TABLES_PK | 1 | 22 | 1 (0)| 00:00:02 | ---------------------------------------------------------------------------------- Outline Data ------------- /*+ BEGIN_OUTLINE_DATA USE_NL(@"SEL$B384BBCF" "T"@"INV") LEADING(@"SEL$B384BBCF" "O"@"SEL$1" "T"@"INV") INDEX(@"SEL$B384BBCF" "T"@"INV" ("T_TABLES"."TABLE_NAME" "T_TABLES"."OWNER")) FULL(@"SEL$B384BBCF" "O"@"SEL$1") OUTLINE(@"INV") OUTLINE(@"SEL$1") UNNEST(@"INV") OUTLINE_LEAF(@"SEL$B384BBCF") OPT_PARAM('optimizer_index_cost_adj' 60) OPT_PARAM('_unnest_subquery' 'false') OPTIMIZER_FEATURES_ENABLE('10.2.0.4') IGNORE_OPTIM_EMBEDDED_HINTS END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("T"."TABLE_NAME"="O"."OBJECT_NAME" AND "T"."OWNER"="O"."OWNER") 在上例中，EXISTS子句中的子查询被展开，其中的对象与主查询中的对象直接进行半关联操作。 3.1.2.2　NOT IN/NOT EXISTS转换为ANTI-JOIN 转换器将NOT IN或NOT EXISTS子句中的子查询展开（反嵌套），使得优化器能选择反关联（ANTI-JOIN）操作。这种转换属于基于代价的查询转换。示例如代码清单3-5所示。代码清单3-5　NOT IN/NOT EXISTS 转换为ANTI-JOIN HELLODBA.COM>exec sql_explain('select * from t_objects o where not exists (select /*+qb_name(inv)*/1 from t_tables t where t.owner=o.owner and t.table_name=o.object_name)', 'TYPICAL OUTLINE'); Plan hash value: 271238895 ---------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 47562 | 6038K| 55 (0)| 00:00:56 | | 1 | NESTED LOOPS ANTI | | 47562 | 6038K| 55 (0)| 00:00:56 | | 2 | TABLE ACCESS FULL| T_OBJECTS | 47585 | 5018K| 54 (0)| 00:00:55 | |* 3 | INDEX UNIQUE SCAN| T_TABLES_PK | 1 | 22 | 1 (0)| 00:00:02 | ---------------------------------------------------------------------------------- Outline Data ------------- /*+ BEGIN_OUTLINE_DATA USE_NL(@"SEL$B384BBCF" "T"@"INV") LEADING(@"SEL$B384BBCF" "O"@"SEL$1" "T"@"INV") INDEX(@"SEL$B384BBCF" "T"@"INV" ("T_TABLES"."TABLE_NAME" "T_TABLES"."OWNER")) FULL(@"SEL$B384BBCF" "O"@"SEL$1") OUTLINE(@"INV") OUTLINE(@"SEL$1") UNNEST(@"INV") OUTLINE_LEAF(@"SEL$B384BBCF") OPT_PARAM('optimizer_index_cost_adj' 60) OPTIMIZER_FEATURES_ENABLE('10.2.0.4') IGNORE_OPTIM_EMBEDDED_HINTS END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("T"."TABLE_NAME"="O"."OBJECT_NAME" AND "T"."OWNER"="O"."OWNER") 在上例中，NOT EXISTS子句中的子查询被展开，其中的对象与主查询中的对象直接进行反关联操作。 3.1.2.3　NOT IN/NOT EXISTS转换为Null-Aware ANTI-JOIN（11g）转换器将NOT IN/NOT EXISTS子句中的子查询展开（反嵌套），使得优化器能选择对空值敏感的反关联（Null-Aware ANTI-JOIN）操作。这种转换属于启发式查询转换。示例如代码清单3-6所示，其中字段T_OBJECTS.OBJECT_NAME允许为空（不存在非空约束）。提示：对空值敏感的反关联操作能在关联数据时关注到空值的存在，从而避免使用代价高昂的操作（例如笛卡儿乘积关联）来获取逻辑结果。代码清单3-6　NOT IN/NOT EXISTS转换为Null-Aware ANTI-JOIN HELLODBA.COM>exec sql_explain('select object_name from t_objects o where object_name not in (select table_name from t_tables t)', 'TYPICAL OUTLINE'); Plan hash value: 2913741112 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 67645 | 3038K| 295 (1)| 00:00:04 | |* 1 | HASH JOIN RIGHT ANTI SNA| | 67645 | 3038K| 295 (1)| 00:00:04 | | 2 | INDEX FAST FULL SCAN | T_TABLES_PK | 2696 | 56616 | 6 (0)| 00:00:01 | | 3 | TABLE ACCESS FULL | T_OBJECTS | 72115 | 1760K| 288 (1)| 00:00:04 | ---------------------------------------------------------------------------------------- Outline Data ------------- /*+ BEGIN_OUTLINE_DATA SWAP_JOIN_INPUTS(@"SEL$5DA710D3" "T"@"SEL$2") USE_HASH(@"SEL$5DA710D3" "T"@"SEL$2") LEADING(@"SEL$5DA710D3" "O"@"SEL$1" "T"@"SEL$2") INDEX_FFS(@"SEL$5DA710D3" "T"@"SEL$2" ("T_TABLES"."TABLE_NAME" "T_TABLES"."OWNER")) FULL(@"SEL$5DA710D3" "O"@"SEL$1") OUTLINE(@"SEL$2") OUTLINE(@"SEL$1") UNNEST(@"SEL$2") OUTLINE_LEAF(@"SEL$5DA710D3") 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("OBJECT_NAME"="TABLE_NAME") 在上例中，NOT IN子句中的子查询被展开，其中的对象与主查询中的对象直接进行对空值敏感的反关联操作。 3.1.2.4　互关联子查询的反嵌套转换器对互关联子查询的反嵌套，会将子查询构造出一个内联视图，并将内联视图与主查询中的表进行关联。互关联子查询的反嵌套可以由参数_unnest_subquery控制。这种转换属于启发式查询转换。示例如代码清单3-7所示。代码清单3-7　互关联子查询的反嵌套 HELLODBA.COM>exec sql_explain('select * from t_users u where created > (select /*+qb_name(inv)*/max(created) from t_objects o where u.username=o.owner)', 'TYPICAL OUTLINE'); Plan hash value: 2385613560 --------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 112 | 57 (4)| 00:00:58 | | 1 | MERGE JOIN | | 1 | 112 | 57 (4)| 00:00:58 | | 2 | TABLE ACCESS BY INDEX ROWID| T_USERS | 41 | 3526 | 1 (0)| 00:00:02 | | 3 | INDEX FULL SCAN | T_USERS_IDX2 | 41 | | 1 (0)| 00:00:02 | |* 4 | FILTER | | | | | | |* 5 | SORT JOIN | | 22 | 572 | 56 (4)| 00:00:57 | | 6 | VIEW | VW_SQ_1 | 22 | 572 | 55 (2)| 00:00:56 | | 7 | SORT GROUP BY | | 22 | 374 | 55 (2)| 00:00:56 | | 8 | TABLE ACCESS FULL | T_OBJECTS | 47585 | 789K| 54 (0)| 00:00:55 | --------------------------------------------------------------------------------------------- Outline Data ------------- /*+ BEGIN_OUTLINE_DATA FULL(@"SEL$2D2FF62D" "O"@"INV") USE_MERGE(@"SEL$7920AA66" "VW_SQ_1"@"SEL$250DBE7D") LEADING(@"SEL$7920AA66" "U"@"SEL$1" "VW_SQ_1"@"SEL$250DBE7D") NO_ACCESS(@"SEL$7920AA66" "VW_SQ_1"@"SEL$250DBE7D") INDEX(@"SEL$7920AA66" "U"@"SEL$1" ("T_USERS"."USERNAME")) OUTLINE(@"SEL$1") OUTLINE(@"SEL$250DBE7D") OUTLINE(@"SEL$2D2FF62D") OUTLINE(@"INV") UNNEST(@"INV") OUTLINE_LEAF(@"SEL$7920AA66") OUTLINE_LEAF(@"SEL$2D2FF62D") OPT_PARAM('optimizer_index_cost_adj' 60) OPTIMIZER_FEATURES_ENABLE('10.2.0.4') IGNORE_OPTIM_EMBEDDED_HINTS END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 4 - filter("CREATED">"VW_COL_1") 5 - access("U"."USERNAME"="OWNER") filter("U"."USERNAME"="OWNER") 在上例中，关联谓词中存在子查询，该子查询被展开，其中的对象与主查询中的对象直接进行关联操作。 3.1.2.5　含有标量子查询的反嵌套这种转换属于启发式查询转换。示例如代码清单3-8所示。代码清单3-8　含有标量子查询的反嵌套 HELLODBA.COM>begin 2 sql_explain('select /*+ qb_name(m) */* 3 from t_tablespaces ts 4 where ts.tablespace_name in 5 (select /*+ qb_name(inv) */ 6 (select/*+ qb_name(sca) */ u.default_tablespace from t_users u 7 where u.username=t.owner) def_ts 8 from t_tables t)', 9 'TYPICAL OUTLINE'); 10 end; 11 / Plan hash value: 607850572 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2696 | 363K| 8 (25)| 00:00:01 | |* 1 | HASH JOIN | | 2696 | 363K| 8 (25)| 00:00:01 | | 2 | TABLE ACCESS FULL | T_TABLESPACES | 13 | 1573 | 3 (0)| 00:00:01 | | 3 | TABLE ACCESS BY INDEX ROWID| T_USERS | 1 | 17 | 1 (0)| 00:00:01 | |* 4 | INDEX UNIQUE SCAN | T_USERS_UK | 1 | | 0 (0)| 00:00:01 | | 5 | VIEW | VW_NSO_1 | 2696 | 45832 | 4 (25)| 00:00:01 | | 6 | HASH UNIQUE | | 2696 | 18872 | 4 (25)| 00:00:01 | | 7 | INDEX FAST FULL SCAN | T_TABLES_IDX1 | 2696 | 18872 | 3 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------- Outline Data ------------- /*+ BEGIN_OUTLINE_DATA INDEX_RS_ASC(@"SCA" "U"@"SCA" ("T_USERS"."USERNAME")) USE_HASH_AGGREGATION(@"SEL$2D2FF62D") INDEX_FFS(@"SEL$2D2FF62D" "T"@"INV" ("T_TABLES"."OWNER")) USE_HASH(@"SEL$1A0389FA" "VW_NSO_1"@"SEL$1A0389FA") LEADING(@"SEL$1A0389FA" "TS"@"M" "VW_NSO_1"@"SEL$1A0389FA") NO_ACCESS(@"SEL$1A0389FA" "VW_NSO_1"@"SEL$1A0389FA") FULL(@"SEL$1A0389FA" "TS"@"M") OUTLINE(@"M") OUTLINE(@"INV") OUTLINE(@"SCA") UNNEST(@"INV") OUTLINE_LEAF(@"SEL$1A0389FA") OUTLINE_LEAF(@"SEL$2D2FF62D") OUTLINE_LEAF(@"SCA") 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("TS"."TABLESPACE_NAME"="DEF_TS") 4 - access("U"."USERNAME"=:B1) 在上例中， IN子句中的子查询（INV）含有标量子查询（SCA），它也被查询转换器展开，标量子查询中的对象T_USERS直接与主查询中的对象进行关联。 3.1.3　子查询推进子查询推进（Push Subquery）是一项对未能合并或者反嵌套的子查询优化的补充优化技术。通常情况下，未能合并或者反嵌套的子查询的子计划会被放置在整个查询计划的最后步骤执行，而子查询推进使得子查询能够提前被评估，使之可以出现在整体执行计划的较早步骤，从而获得更优的执行计划。提示：子查询推进是9.2被引入的技术，但是它的概要数据在10.2.0.5及以后版本才可见。它可以由提示PUSH_SBUQ/NO_PUSH_SUBQ控制。示例如代码清单3-9所示（11.2.0.1）。代码清单3-9　子查询推进（11.2.0.1） HELLODBA.COM>exec sql_explain('select /*+no_push_subq(@inv)*/* from t_objects u where created > (select /*+qb_name(inv)*/max(created) from t_users)', 'TYPICAL OUTLINE'); Plan hash value: 597891120 -------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 72115 | 6972K| 291 (2)| 00:00:04 | |* 1 | FILTER | | | | | | | 2 | TABLE ACCESS FULL | T_OBJECTS | 72115 | 6972K| 290 (2)| 00:00:04 | | 3 | SORT AGGREGATE | | 1 | 8 | | | | 4 | INDEX FULL SCAN (MIN/MAX)| T_USERS_IDX1 | 1 | 8 | 1 (0)| 00:00:01 | -------------------------------------------------------------------------------------------- Outline Data ------------- /*+ BEGIN_OUTLINE_DATA INDEX(@"INV" "T_USERS"@"INV" ("T_USERS"."CREATED")) FULL(@"SEL$1" "U"@"SEL$1") OUTLINE(@"INV") OUTLINE_LEAF(@"SEL$1") OUTLINE_LEAF(@"INV") 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 - filter("CREATED"> (SELECT /*+ NO_PUSH_SUBQ QB_NAME ("INV") */ MAX("CREATED") FROM "T_USERS" "T_USERS")) PL/SQL procedure successfully completed. HELLODBA.COM>exec sql_explain('select * from t_objects u where created > (select /*+qb_name(inv)*/max(created) from t_users)', 'TYPICAL OUTLINE'); Plan hash value: 2825180269 ----------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 3606 | 348K| 14 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID | T_OBJECTS | 3606 | 348K| 13 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | T_OBJECTS_IDX5 | 649 | | 3 (0)| 00:00:01 | | 3 | SORT AGGREGATE | | 1 | 8 | | | | 4 | INDEX FULL SCAN (MIN/MAX)| T_USERS_IDX1 | 1 | 8 | 1 (0)| 00:00:01 | ----------------------------------------------------------------------------------------------- Outline Data ------------- /*+ BEGIN_OUTLINE_DATA INDEX(@"INV" "T_USERS"@"INV" ("T_USERS"."CREATED")) PUSH_SUBQ(@"INV") INDEX_RS_ASC(@"SEL$1" "U"@"SEL$1" ("T_OBJECTS"."CREATED")) OUTLINE(@"INV") OUTLINE_LEAF(@"SEL$1") OUTLINE_LEAF(@"INV") 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("CREATED"> (SELECT /*+ QB_NAME ("INV") */ MAX("CREATED") FROM "T_USERS" "T_USERS")) PL/SQL procedure successfully completed. 从上述例子可见，在未采用子查询推进技术时，子查询操作树是操作1的第二个子树，即整个执行计划的最后一步，估算代价为291；而采用子查询推进技术后，子查询操作树成为操作2的子树，从而使得执行计划可以通过索引T_OBJECTS_IDX5对表T_OBJECTS进行访问，估算代价仅为14。 3.1.4　旧关联谓词推入解析器未合并视图（或者视图不能被合并）时，查询转换器还可以采用另外一项技术对其进行转换，以生成最优的执行计划—谓词推入。所谓谓词推入，是当视图处在关联查询中时，关联谓词被推入视图中，使得优化器能获得更好的执行计划。 10gR2之前，关联谓词推入技术属于启发式查询转换，因此也称为旧关联谓词推入（Old Join Predicate Push-Down，OJPPD）；10gR2之后，关联谓词推入会考虑到代价因素，属于基于代价的查询转换。提示：是否进行谓词推入，可以由优化器参数_push_join_predicate控制，默认为TRUE，也可以由提示OLD_PUSH_PRED控制。要启用旧的谓词推入，需要关闭基于代价的查询转换。 3.1.4.1　外关联的谓词推入语句存在外关联匹配操作时，转换器可以将关联谓词条件推入视图的查询语句中，从而使之成为其子计划的访问谓词条件。示例如代码清单3-10所示。代码清单3-10　外关联的谓词推入 HELLODBA.COM>alter session set "_OPTIMIZER_COST_BASED_TRANSFORMATION"=off; Session altered. HELLODBA.COM>exec sql_explain('SELECT /*+ NO_MERGE(v) OLD_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: 4006197802 ------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 2033 | 557K| 14814 (1)| 00:01:00 | | | | 1 | NESTED LOOPS OUTER | | 2033 | 557K| 14814 (1)| 00:01:00 | | | | 2 | TABLE ACCESS BY INDEX ROWID | T_TABLES | 184 | 37536 | 21 (0)| 00:00:01 | | | |* 3 | INDEX RANGE SCAN | T_TABLES_IDX3 | 184 | | 1 (0)| 00:00:01 | | | | 4 | PARTITION HASH SINGLE | | 11 | 847 | 80 (0)| 00:00:01 | 3 | 3 | |* 5 | VIEW PUSHED PREDICATE | V_OBJECTS_SYS | 11 | 847 | 80 (0)| 00:00:01 | | | | 6 | TABLE ACCESS BY INDEX ROWID| T_OBJECTS | 1039 | 80003 | 115 (2)| 00:00:01 | | | |* 7 | INDEX RANGE SCAN | T_OBJECTS_IDX_PART | 1039 | | 82 (3)| 00:00:01 | 3 | 3 | ------------------------------------------------------------------------------------------------------ Outline Data ------------- /*+ BEGIN_OUTLINE_DATA INDEX_RS_ASC(@"SEL$661FCD0D" "T_OBJECTS"@"SEL$2" ("T_OBJECTS"."OWNER" "T_OBJECTS"."CREATED")) USE_NL(@"SEL$1" "V"@"SEL$1") LEADING(@"SEL$1" "T"@"SEL$1" "V"@"SEL$1") NO_ACCESS(@"SEL$1" "V"@"SEL$1") INDEX_RS_ASC(@"SEL$1" "T"@"SEL$1" ("T_TABLES"."TABLESPACE_NAME")) OUTLINE(@"SEL$1") OUTLINE(@"SEL$2") OUTLINE_LEAF(@"SEL$1") OLD_PUSH_PRED(@"SEL$1" "V"@"SEL$1") OUTLINE_LEAF(@"SEL$661FCD0D") OPT_PARAM('_optimizer_cost_based_transformation' 'off') OPTIMIZER_FEATURES_ENABLE('10.2.0.4') IGNORE_OPTIM_EMBEDDED_HINTS END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("T"."TABLESPACE_NAME"=:A) 5 - filter("T"."TABLE_NAME"="V"."OBJECT_NAME"(+)) 7 - access("OWNER"='SYS') filter("OWNER"="T"."OWNER") 上述查询中，关联条件"OWNER"="T"."OWNER"被推入了视图的查询语句中，从而成为其子计划的过滤条件。 3.1.4.2　联合查询视图关联查询的谓词推入转换器将关联条件推入含有联合操作（UNION或者UNION-ALL）查询的视图中，并使之成为联合查询视图中两个子查询的谓词。示例如代码清单3-11所示。代码清单3-11　联合查询视图关联查询的谓词推入 HELLODBA.COM>alter session set "_OPTIMIZER_COST_BASED_TRANSFORMATION"=off; Session altered. 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: 2951547052 ----------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 113 | 13108 | 85 (0)| 00:00:01 | | 1 | NESTED LOOPS | | 113 | 13108 | 85 (0)| 00:00:01 | | 2 | TABLE ACCESS BY INDEX ROWID | T_USERS | 1 | 86 | 1 (0)| 00:00:01 | |* 3 | INDEX UNIQUE SCAN | T_USERS_PK | 1 | | 0 (0)| 00:00:01 | | 4 | VIEW | | 113 | 3390 | 84 (0)| 00:00:01 | | 5 | UNION-ALL PARTITION | | | | | | |* 6 | INDEX RANGE SCAN | T_TABLES_PK | 5 | 120 | 2 (0)| 00:00:01 | |* 7 | TABLE ACCESS BY INDEX ROWID| T_OBJECTS | 108 | 3240 | 979 (1)| 00:00:04 | | 8 | INDEX FULL SCAN | T_OBJECTS_PK | 47585 | | 103 (3)| 00:00:01 | ----------------------------------------------------------------------------------------------- Outline Data ------------- /*+ BEGIN_OUTLINE_DATA INDEX(@"SEL$661FCD0D" "T2"@"SEL$2" ("T_TABLES"."TABLE_NAME" "T_TABLES"."OWNER")) INDEX(@"SEL$A8E2213E" "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") OLD_PUSH_PRED(@"SEL$1" "V"@"SEL$1") OUTLINE_LEAF(@"SET$AD7CC163") OUTLINE_LEAF(@"SEL$A8E2213E") OUTLINE_LEAF(@"SEL$661FCD0D") OPT_PARAM('_optimizer_cost_based_transformation' 'off') 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.1.5　简单过滤谓词推入简单过滤谓词推入（Filter Push-Down，FPD），即简单地将主查询中作用于视图的过滤谓词推入视图中。简单过滤谓词推入是启发式查询转换技术，只要满足条件就会进行转换。 HELLODBA.COM>exec sql_explain('SELECT /*+ NO_MERGE(v) LEADING(T) USE_NL(T V) */* FROM t_tables t, v_objects_sys v WHERE t.owner =v.owner and t.table_name = v.object_name AND v.status = :A', 'TYPICAL ALIAS'); Plan hash value: 4221611364 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 11429 | 2980K| 111K (1)| 31:06:18 | | 1 | NESTED LOOPS | | 11429 | 2980K| 111K (1)| 31:06:18 | | 2 | TABLE ACCESS FULL | T_TABLES | 2071 | 386K| 6 (0)| 00:00:07 | |* 3 | VIEW | V_OBJECTS_SYS | 6 | 456 | 54 (0)| 00:00:55 | |* 4 | TABLE ACCESS FULL| T_OBJECTS | 11429 | 848K| 54 (0)| 00:00:55 | ------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 2 - SEL$1 / T@SEL$1 3 - SEL$2 / V@SEL$1 4 - SEL$2 / T_OBJECTS@SEL$2 Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter("T"."OWNER"="V"."OWNER" AND "T"."TABLE_NAME"="V"."OBJECT_NAME") 4 - filter("OWNER"='SYS' AND "STATUS"=:A) 上述查询中，主查询中的过滤条件"STATUS"=:A被推入了视图的查询语句中。 3.1.6　谓词迁移谓词迁移（Predicate Move Around）是指在对多个视图的复杂查询中，将其中一个视图的谓词条件提取（Pull Up）出来，并推入（Push Down）另外的视图中，成为其谓词的一部分。提示：谓词迁移可以通过优化器参数_pred_move_around控制。以下例子即发生谓词迁移的语句： HELLODBA.COM>begin 2 sql_explain(' 3 SELECT /*+ NO_MERGE(v1) NO_MERGE(v2) */* FROM 4 (select owner, count(table_name) from t_tables group by owner) v1, 5 (select * from t_objects where owner>''A'') v2 6 WHERE v1.owner =v2.owner', 7 'TYPICAL PREDICATE'); 8 end; 9 / Plan hash value: 1702844990 ----------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 179K| 29M| 58 (4)| 00:00:58 | |* 1 | HASH JOIN | | 179K| 29M| 58 (4)| 00:00:58 | | 2 | VIEW | | 21 | 672 | 3 (34)| 00:00:04 | | 3 | HASH GROUP BY | | 21 | 210 | 3 (34)| 00:00:04 | |* 4 | INDEX FAST FULL SCAN| T_TABLES_IDX1 | 2071 | 20710 | 2 (0)| 00:00:03 | | 5 | VIEW | | 47585 | 6645K| 54 (0)| 00:00:55 | |* 6 | TABLE ACCESS FULL | T_OBJECTS | 47585 | 5576K| 54 (0)| 00:00:55 | ----------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("V1"."OWNER"="V2"."OWNER") 4 - filter("OWNER">'A') 6 - filter("OWNER">'A') 上述例子中，视图V2的谓词条件"OWNER">'A'被迁移至了视图V1当中。 3.1.7　“或”操作扩张如果查询语句的WHERE子句由多个条件以OR关联组成，那么查询转换器会尝试将查询转换为多个等价的子查询，并将这些子查询拼接（Concatation）起来，这样的转换技术就称为“或”扩张。提示：如果OR关联的条件都为同一字段，查询转换器不会对其进行“或”扩张。是否对语句进行“或”扩张转换，可以由优化器参数_no_or_expansion（TRUE为禁止“或”扩张）和提示OR_EXPAND控制。 HELLODBA.COM>exec sql_explain('select /*+OR_EXPAND(o created)*/* from t_objects o where created = :A or (owner = :B and object_name=:C)', 'TYPICAL OUTLINE'); Plan hash value: 3372329462 ----------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 32 | 3456 | 4 (0)| 00:00:05 | | 1 | CONCATENATION | | | | | | | 2 | TABLE ACCESS BY INDEX ROWID| T_OBJECTS | 1 | 108 | 3 (0)| 00:00:04 | |* 3 | INDEX SKIP SCAN | T_OBJECTS_IDX1 | 1 | | 2 (0)| 00:00:03 | |* 4 | TABLE ACCESS BY INDEX ROWID| T_OBJECTS | 31 | 3348 | 1 (0)| 00:00:02 | |* 5 | INDEX RANGE SCAN | T_OBJECTS_IDX5 | 31 | | 1 (0)| 00:00:02 | ----------------------------------------------------------------------------------------------- Outline Data ------------- /*+ BEGIN_OUTLINE_DATA INDEX_RS_ASC(@"SEL$1_2" "O"@"SEL$1_2" ("T_OBJECTS"."CREATED")) INDEX_SS(@"SEL$1_1" "O"@"SEL$1" ("T_OBJECTS"."STATUS" "T_OBJECTS"."OWNER" "T_OBJECTS"."OBJECT_NAME")) OUTLINE(@"SEL$1") OUTLINE_LEAF(@"SEL$1_2") USE_CONCAT(@"SEL$1" 8) OUTLINE_LEAF(@"SEL$1_1") OUTLINE_LEAF(@"SEL$1") OPTIMIZER_FEATURES_ENABLE('10.2.0.4') IGNORE_OPTIM_EMBEDDED_HINTS END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("OWNER"=:B AND "OBJECT_NAME"=:C) filter("OBJECT_NAME"=:C AND "OWNER"=:B) 4 - filter(LNNVL("OBJECT_NAME"=:C) OR LNNVL("OWNER"=:B)) 5 - access("CREATED"=:A) 在上述例子中，OR关系表达式的谓词条件被扩展成对表的两次访问，并将它们的结果集进行拼接。 3.1.8　物化视图查询重写我们知道，视图是一个逻辑对象，其实质就是一条查询语句。而在Oracle中，存在一种特殊对象—物化视图（Materialized View，MV），它是一个物理对象，其数据被存储在相应的段（Segment）上，其内容由一条查询语句决定。因此，执行构造物化视图的查询语句或者与该语句兼容的查询语句时，直接读取物化视图上的数据会比读取相关表对象上的数据效率更高。当查询转换器发现一个查询或子查询的逻辑结构与一个物化视图相兼容时，它能将其重写成对物化视图的查询，从而提高语句的整体性能。提示：是否进行查询重写，可以由参数query_rewrite_enabled或者提示REWRITE/NO_REWRITE控制。示例如下： HELLODBA.COM>exec sql_explain('select t.owner, t.table_name from t_tables t, t_objects o where t.owner = o.owner and t.table_name = o.object_name and o.object_type = ''TABLE'' and t.tablespace_ name is not null and created>:A', 'TYPICAL OUTLINE'); Plan hash value: 2394845261 ------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 92 | 2760 | 3 (0)| 00:00:04 | |* 1 | MAT_VIEW REWRITE ACCESS FULL| MV_TABLES | 92 | 2760 | 3 (0)| 00:00:04 | ------------------------------------------------------------------------------------------ Outline Data ------------- /*+ BEGIN_OUTLINE_DATA FULL(@"SEL$25088146" "MV_TABLES"@"SEL$77906312") OUTLINE(@"SEL$1") REWRITE(@"SEL$1" "MV_TABLES") OUTLINE(@"SEL$30D76588") REWRITE(@"SEL$30D76588" "MV_TABLES") OUTLINE_LEAF(@"SEL$25088146") OPTIMIZER_FEATURES_ENABLE('10.2.0.4') IGNORE_OPTIM_EMBEDDED_HINTS END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("MV_TABLES"."CREATED">:A) 上面例子中，物化视图MV_TABLES的定义语句为： create materialized view mv_tables as select t.owner, t.table_name, t.tablespace_name, o.created, o.last_ddl_time from t_tables t, t_objects o where t.owner = o.owner and t.table_name = o.object_name and o.object_type = 'TABLE' and t.tablespace_name is not null; 而上例的查询语句中，以下部分： select t.owner, t.table_name from t_tables t, t_objects o where t.owner = o.owner and t.table_name = o.object_name and o.object_type = ''TABLE'' and t.tablespace_name is not null 与定义物化视图MV_TABLES的查询语句兼容，并且过滤条件created>:A中的字段也为物化视图的所有字段，因而该语句被重写为对物化视图的查询。重写后的语句与下面的语句等价： select owner, table_name from MV_TABLES where created>:A; 3.1.9　集合操作关联转变当查询中存在集合操作（INTERSECT、MINUS）时，查询转换器可以将进行集合操作的子查询转变为关联（Join）查询，这样的转换就称为集合操作关联转变（Set Join Conversion，SJC）。提示：尽管该项技术在10g时被引入，但目前为止（11.2.0.1），在所有版本中其默认为关闭。可以通过优化器参数_convert_set_to_join和提示SET_TO_JOIN/NO_SET_TO_JOIN控制其是否启用。 3.1.9.1　INTERSECT转换为内关联 INTERSECT是属于数据集合操作中的交集操作，转换器能将其转换为内关联操作： HELLODBA.COM>exec sql_explain('select /*+SET_TO_JOIN(@"SET$1")*/ owner from t_tables intersect select owner from t_objects', 'TYPICAL OUTLINE'); Plan hash value: 1300757496 ------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 21 | 210 | 16 (75)| 00:00:17 | | 1 | HASH UNIQUE | | 21 | 210 | 16 (75)| 00:00:17 | |* 2 | HASH JOIN | | 17M| 168M| 6 (34)| 00:00:06 | | 3 | INDEX FAST FULL SCAN | T_TABLES_IDX1 | 2071 | 10355 | 2 (0)| 00:00:03 | | 4 | BITMAP CONVERSION TO ROWIDS| | 47585 | 232K| 2 (0)| 00:00:03 | | 5 | BITMAP INDEX FULL SCAN | T_OBJECTS_IDX4 | | | | | ------------------------------------------------------------------------------------------------ Outline Data ------------- /*+ BEGIN_OUTLINE_DATA USE_HASH(@"SEL$02B15F54" "T_OBJECTS"@"SEL$2") LEADING(@"SEL$02B15F54" "T_TABLES"@"SEL$1" "T_OBJECTS"@"SEL$2") BITMAP_TREE(@"SEL$02B15F54" "T_OBJECTS"@"SEL$2" AND(("T_OBJECTS"."OWNER"))) INDEX_FFS(@"SEL$02B15F54" "T_TABLES"@"SEL$1" ("T_TABLES"."OWNER")) OUTLINE(@"SET$1") OUTLINE(@"SEL$2") OUTLINE(@"SEL$1") SET_TO_JOIN(@"SET$1") OUTLINE(@"SET$09AAA538") MERGE(@"SEL$2") MERGE(@"SEL$1") OUTLINE_LEAF(@"SEL$02B15F54") OPT_PARAM('optimizer_index_cost_adj' 60) OPTIMIZER_FEATURES_ENABLE('10.2.0.4') IGNORE_OPTIM_EMBEDDED_HINTS END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OWNER"="OWNER") 上述例子中，INTERSECT操作被消除，取而代之的是哈希关联操作。 3.1.9.2　MINUS转换为反关联 MINUS是属于数据集合操作中的集合相减操作，转换器能将其转换为反关联操作： HELLODBA.COM>exec sql_explain('select /*+ SET_TO_JOIN(@"SET$1")*/t.owner from t_tables t minus select username from t_users u', 'TYPICAL OUTLINE'); Plan hash value: 3342847916 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 24 | 4 (25)| 00:00:05 | | 1 | HASH UNIQUE | | 1 | 24 | 4 (25)| 00:00:05 | | 2 | NESTED LOOPS ANTI | | 1 | 24 | 3 (0)| 00:00:04 | | 3 | INDEX FAST FULL SCAN| T_TABLES_IDX1 | 2071 | 20710 | 2 (0)| 00:00:03 | |* 4 | INDEX UNIQUE SCAN | T_USERS_UK | 41 | 574 | 1 (0)| 00:00:02 | ---------------------------------------------------------------------------------------- Outline Data ------------- /*+ BEGIN_OUTLINE_DATA USE_NL(@"SEL$09AAA538" "U"@"SEL$2") LEADING(@"SEL$09AAA538" "T"@"SEL$1" "U"@"SEL$2") INDEX(@"SEL$09AAA538" "U"@"SEL$2" ("T_USERS"."USERNAME")) INDEX_FFS(@"SEL$09AAA538" "T"@"SEL$1" ("T_TABLES"."OWNER")) OUTLINE(@"SET$1") SET_TO_JOIN(@"SET$1") OUTLINE_LEAF(@"SEL$09AAA538") OPT_PARAM('optimizer_index_cost_adj' 60) OPTIMIZER_FEATURES_ENABLE('10.2.0.4') IGNORE_OPTIM_EMBEDDED_HINTS END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("T"."OWNER"="USERNAME") 上述例子中，MINUS操作被消除，取而代之的是嵌套循环反关联操作。 3.1.10　由约束生成过滤谓词当查询语句中谓词条件或关联条件的字段上存在约束（非空约束、检查约束）时，优化器会将约束内容考虑进来，决定是否由其生成新的谓词过滤条件。 HELLODBA.COM>alter table T_OBJECTS add constraint T_OBJECTS_STATUS_CK check (status in ('VALID','INVALID')); Table altered. HELLODBA.COM>exec sql_explain('select owner from t_objects where status=''NOTEXIST''', 'TYPICAL OUTLINE'); Plan hash value: 3869221256 ------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 11 | 0 (0)| | |* 1 | FILTER | | | | | | |* 2 | INDEX RANGE SCAN| T_OBJECTS_IDX1 | 167 | 1837 | 2 (0)| 00:00:03 | ------------------------------------------------------------------------------------ Outline Data ------------- /*+ BEGIN_OUTLINE_DATA INDEX(@"SEL$1" "T_OBJECTS"@"SEL$1" ("T_OBJECTS"."STATUS" "T_OBJECTS"."OWNER" "T_OBJECTS"."OBJECT_NAME")) OUTLINE_LEAF(@"SEL$1") OPT_PARAM('optimizer_index_cost_adj' 60) OPTIMIZER_FEATURES_ENABLE('10.2.0.4') IGNORE_OPTIM_EMBEDDED_HINTS END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(NULL IS NOT NULL) 2 - access("STATUS"='NOTEXIST') 在上例中，字段T_OBJECTS.STATUS上存在约束T_OBJECTS_STATUS_CK，限制其内容为'VALID'或'INVALID'。查询转换器结合该约束内容与语句中的过滤条件status='NOTEXIST'，为该执行计划生成了一个FILTER操作（NULL IS NOT NULL），从而避免了不必要的读取操作。从执行计划的估算代价来看，尽管操作2的估算代价为2，但是因为FILTER的存在，使得该操作不可能被实际执行到，因而最终估算代价为0。从10053事件的跟踪内容也可以看到这一过滤条件的产生过程： kkogcp: try to generate transitive predicate from check constraints for SEL$1 (#0) constraint: "T_OBJECTS"."STATUS"='VALID' OR "T_OBJECTS"."STATUS"='INVALID' predicates with check contraints: "T_OBJECTS"."STATUS"='NOTEXIST' AND ("T_OBJECTS"."STATUS"='VALID' OR "T_OBJECTS"."STATUS"='INVALID') after transitive predicate generation: "T_OBJECTS"."STATUS"='NOTEXIST' AND ("T_OBJECTS"."STATUS"='VALID' OR "T_OBJECTS"."STATUS"='INVALID') AND ('VALID'='NOTEXIST' OR 'INVALID'='NOTEXIST') finally: "T_OBJECTS"."STATUS"='NOTEXIST' AND ('VALID'='NOTEXIST' OR 'INVALID'='NOTEXIST') 3.1.11　星形转换所谓星形查询（Star Query）是指一个事实表（Fact Table）与多个维度表（Dimension Table）的关联查询，并且维度表仅与事实表之间关联，维度表之间不存在关联关系。星形查询分为两个阶段：第一阶段是由事实表利用位图索引的位图信息（或者由B*树索引的ROWID转换得来的位图信息）进行位图操作，进而获得相应数据集；第二阶段则将第一阶段获得的数据集与维度表进行关联，获取最终查询结果。星形转换是将事实表与多个维度表的普通关联查询转换为星形查询的一项优化技术。示例如代码清单3-12所示。提示：星形转换可以由参数star_transformation_enabled或者提示STAR_TRANSFORMATION/ NO_STAR_TRANSFORMATION控制。代码清单3-12　星形查询 HELLODBA.COM>alter session set star_transformation_enabled=true; Session altered. HELLODBA.COM>exec sql_explain('select /*+ QB_NAME(Q) STAR_TRANSFORMATION(@"Q" "T"@"Q" SUBQUERIES(("TS"@"Q") ("U"@"Q")))*/* from t_tables t, t_tablespaces ts, t_users u where t.tablespace_name=ts.tablespace_name and t.owner=u.username','TYPICAL OUTLINE'); Plan hash value: 2958936575 ------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1842 | 660K| 51 (6)| 00:00:51 | |* 1 | HASH JOIN | | 1842 | 660K| 51 (6)| 00:00:51 | | 2 | TABLE ACCESS FULL | T_USERS | 41 | 3526 | 2 (0)| 00:00:03 | |* 3 | HASH JOIN | | 1842 | 505K| 48 (5)| 00:00:49 | | 4 | TABLE ACCESS FULL | T_TABLESPACES | 15 | 1425 | 2 (0)| 00:00:03 | | 5 | TABLE ACCESS BY INDEX ROWID | T_TABLES | 1842 | 334K| 46 (5)| 00:00:46 | | 6 | BITMAP CONVERSION TO ROWIDS | | | | | | | 7 | BITMAP AND | | | | | | | 8 | BITMAP MERGE | | | | | | | 9 | BITMAP KEY ITERATION | | | | | | | 10 | TABLE ACCESS FULL | T_USERS | 41 | 3526 | 2 (0)| 00:00:03 | | 11 | BITMAP CONVERSION FROM ROWIDS| | | | | | |* 12 | INDEX RANGE SCAN | T_TABLES_IDX1 | | | 1 (0)| 00:00:02 | | 13 | BITMAP MERGE | | | | | | | 14 | BITMAP KEY ITERATION | | | | | | | 15 | TABLE ACCESS FULL | T_TABLESPACES | 15 | 1425 | 2 (0)| 00:00:03 | | 16 | BITMAP CONVERSION FROM ROWIDS| | | | | | |* 17 | INDEX RANGE SCAN | T_TABLES_IDX3 | | | 1 (0)| 00:00:02 | ------------------------------------------------------------------------------------------------------ Outline Data ------------- /*+ BEGIN_OUTLINE_DATA SEMIJOIN_DRIVER(@"SEL$72E3C400") FULL(@"SEL$72E3C400" "U"@"SEL$72E3C400") SEMIJOIN_DRIVER(@"SEL$802A46FE") FULL(@"SEL$802A46FE" "TS"@"SEL$802A46FE") SWAP_JOIN_INPUTS(@"SEL$DC6C7441" "U"@"Q") USE_HASH(@"SEL$DC6C7441" "U"@"Q") USE_HASH(@"SEL$DC6C7441" "T"@"Q") LEADING(@"Q" "TS"@"Q" "T"@"Q" "U"@"Q") FULL(@"SEL$DC6C7441" "U"@"Q") BITMAP_TREE(@"Q" "T"@"Q" AND(("T_TABLES"."OWNER") ("T_TABLES"."TABLESPACE_NAME"))) FULL(@"SEL$DC6C7441" "TS"@"Q") OUTLINE(@"Q") STAR_TRANSFORMATION(@"Q" "T"@"Q" SUBQUERIES(("TS"@"Q") ("U"@"Q"))) OUTLINE_LEAF(@"SEL$DC6C7441") OUTLINE_LEAF(@"SEL$802A46FE") OUTLINE_LEAF(@"SEL$72E3C400") OPT_PARAM('optimizer_index_cost_adj' 60) OPT_PARAM('star_transformation_enabled' 'true') OPTIMIZER_FEATURES_ENABLE('10.2.0.4') IGNORE_OPTIM_EMBEDDED_HINTS END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("T"."OWNER"="U"."USERNAME") 3 - access("T"."TABLESPACE_NAME"="TS"."TABLESPACE_NAME") 12 - access("T"."OWNER"="U"."USERNAME") 17 - access("T"."TABLESPACE_NAME"="TS"."TABLESPACE_NAME") filter("T"."TABLESPACE_NAME" IS NOT NULL) Note ----- - star transformation used for this statement 在上例中，查询转换器进行了星形转换，事实表是T_TABLES，维度表为T_USERS和T_TABLESPACES。第一阶段中，通过事实表与维度表之间进行位图操作，获得了访问事实表的ROWID集合；第二阶段中，由ROWID集合访问事实表T_TABLES获得数据记录，并与维度表之间进行关联操作。 3.1.12　排序消除排序消除（Order By Elimination，OBYE）是指优化器在生成执行计划之前，将语句中没有必要的排序操作消除掉，从而避免在执行计划中出现排序操作或由排序导致的操作（如Index Full Scan）。提示：排序消除可以由优化器参数_optimizer_order_by_elimination_enabled或者提示ELIMINATE_OBY/NO_ELIMINATE_OBY控制。 HELLODBA.COM>exec sql_explain('select count(password) from (select * from t_users order by user_id)', 'TYPICAL OUTLINE'); Plan hash value: 995398926 ------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 17 | 2 (0)| 00:00:03 | | 1 | SORT AGGREGATE | | 1 | 17 | | | | 2 | TABLE ACCESS FULL| T_USERS | 41 | 697 | 2 (0)| 00:00:03 | ------------------------------------------------------------------------------ Outline Data ------------- /*+ BEGIN_OUTLINE_DATA FULL(@"SEL$51F12574" "T_USERS"@"SEL$2") OUTLINE(@"SEL$2") ELIMINATE_OBY(@"SEL$2") OUTLINE(@"SEL$73523A42") OUTLINE(@"SEL$1") MERGE(@"SEL$73523A42") OUTLINE_LEAF(@"SEL$51F12574") OPT_PARAM('optimizer_index_cost_adj' 60) OPTIMIZER_FEATURES_ENABLE('10.2.0.4') IGNORE_OPTIM_EMBEDDED_HINTS END_OUTLINE_DATA */ 3.1.13　DISTINCT消除根据表中主键、唯一约束信息，消除查询语句中的DISTINCT。这是一项启发式查询转换。 HELLODBA.COM>exec sql_explain('select /*+full(u)*/distinct username from t_users u', 'TYPICAL OUTLINE'); Plan hash value: 616708042 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 43 | 301 | 19 (0)| 00:00:01 | | 1 | TABLE ACCESS FULL| T_USERS | 43 | 301 | 19 (0)| 00:00:01 | ----------------------------------------------------------------------------- Outline Data ------------- /*+ BEGIN_OUTLINE_DATA FULL(@"SEL$1" "U"@"SEL$1") OUTLINE_LEAF(@"SEL$1") OPTIMIZER_FEATURES_ENABLE('10.2.0.4') IGNORE_OPTIM_EMBEDDED_HINTS END_OUTLINE_DATA */ 上例中，尽管在SELECT子句中存在DISTINCT关键字，但由于表上存在唯一性约束T_USERS_UK(USERNAME)，它并没有增加SORT/HASH UNIQUE操作来消除重复值。 3.1.14　表消除表消除是查询转换器依据表之间的主外键关系、位图关联索引等信息，将复杂查询语句中不必要读取的表消除掉的一项优化技术。由于表消除出现在关联查询语句里面，因此也称为关联消除（Join Elimination，JE）。提示：表消除可以由优化器参数“_optimizer_join_elimination_enabled”和提示ELIMINATE_ JOIN/NO_ELIMINATE_JOIN控制。 3.1.14.1　主外键关系消除关联操作查询转换器能够依据两表之间的主外键关系消除关联操作，从而消除对关联表的扫描。 HELLODBA.COM>alter table t_tables add constraint t_tables_ts_fk foreign key (tablespace_name) references t_tablespaces(tablespace_name); Table altered. HELLODBA.COM>exec sql_explain('SELECT t.* FROM t_tables t, t_tablespaces ts where t.tablespace_name=ts.tablespace_name','TYPICAL OUTLINE'); Plan hash value: 505459244 ------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1842 | 334K| 6 (0)| 00:00:07 | |* 1 | TABLE ACCESS FULL| T_TABLES | 1842 | 334K| 6 (0)| 00:00:07 | ------------------------------------------------------------------------------ Outline Data ------------- /*+ BEGIN_OUTLINE_DATA FULL(@"SEL$5604CA5E" "T"@"SEL$1") OUTLINE(@"SEL$1") ELIMINATE_JOIN(@"SEL$1" "TS"@"SEL$1") OUTLINE_LEAF(@"SEL$5604CA5E") OPT_PARAM('optimizer_index_cost_adj' 60) OPTIMIZER_FEATURES_ENABLE('10.2.0.4') IGNORE_OPTIM_EMBEDDED_HINTS END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("T"."TABLESPACE_NAME" IS NOT NULL) 在上例中，由于消除了关联操作，在最终的执行计划中没有出现对表T_TABLESPACES的扫描。 3.1.14.2　位图关联索引消除关联操作查询转换器能够依据建立在主表上指向引用表的位图关联索引（Bitmap Join Index）消除关联操作，从而消除对关联表的扫描。示例如代码清单3-13所示（在11.2.0.1中运行）。代码清单3-13　位图关联索引消除关联操作 HELLODBA.COM>CREATE BITMAP INDEX t_objects_idx3 ON t_objects(t_objects.status) FROM t_objects, t_users WHERE t_objects.owner = t_users.username tablespace DEMOTSIDX; Index created. HELLODBA.COM>exec sql_explain('SELECT count(1) FROM t_objects o, t_users u where o.owner=u.username and o.status=:A','TYPICAL OUTLINE'); Plan hash value: 2356450313 ------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 13 | 1 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 13 | | | |* 2 | TABLE ACCESS BY INDEX ROWID | T_OBJECTS | 36058 | 457K| 1 (0)| 00:00:01 | | 3 | BITMAP CONVERSION TO ROWIDS| | | | | | |* 4 | BITMAP INDEX SINGLE VALUE | T_OBJECTS_IDX3 | | | | | ------------------------------------------------------------------------------------------------ Outline Data ------------- /*+ BEGIN_OUTLINE_DATA BITMAP_TREE(@"SEL$1" "O"@"SEL$1" AND(("T_OBJECTS"."STATUS"))) OUTLINE(@"SEL$1") ELIMINATE_JOIN(@"SEL$1" "U"@"SEL$1") OUTLINE_LEAF(@"SEL$FC4DCD93") 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 - filter("O"."STATUS"=:A) 4 - access("O"."SYS_NC00017$"=:A) 在上例中，由于消除了关联操作，在最终的执行计划中没有出现对表T_USERS的扫描。 3.1.14.3　自关联消除关联操作当表进行自关联（Self Join），且关联字段为主键或唯一索引字段时，查询转换器可以消除关联操作，从而消除对表的重复扫描。示例如下（以下示例在11.2.0.1中运行）： HELLODBA.COM>exec sql_explain('select u1.* from t_users u1, t_users u2 where u1.user_id=u2.user_id and u1.created<:A and u2.profile=:B', 'TYPICAL OUTLINE'); Plan hash value: 2143948855 -------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2 | 224 | 2 (0)| 00:00:01 | |* 1 | TABLE ACCESS BY INDEX ROWID| T_USERS | 2 | 224 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | T_USERS_IDX1 | 2 | | 1 (0)| 00:00:01 | -------------------------------------------------------------------------------------------- Outline Data ------------- /*+ BEGIN_OUTLINE_DATA INDEX_RS_ASC(@"SEL$568528C2" "U1"@"SEL$1" ("T_USERS"."CREATED")) OUTLINE(@"SEL$1") ELIMINATE_JOIN(@"SEL$1" "U2"@"SEL$1") OUTLINE_LEAF(@"SEL$568528C2") 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 - filter("PROFILE"=:B) 2 - access("U1"."CREATED"<:A) 在上例中，由于消除了关联操作，在最终的执行计划中没有出现对表T_USERS的再次扫描。 3.1.14.4　外关联消除关联操作当进行外关联操作时，如果选择的字段都来自于左表，查询转换器可以消除该关联操作，从而消除对右表的扫描。示例如下（以下示例在11.2.0.1中运行）： HELLODBA.COM>exec sql_explain('SELECT t.* FROM t_tables t, t_users u where t.owner=u.username(+)','TYPICAL OUTLINE'); Plan hash value: 505459244 ------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 2696 | 634K| 30 (0)| 00:00:01 | | 1 | TABLE ACCESS FULL| T_TABLES | 2696 | 634K| 30 (0)| 00:00:01 | ------------------------------------------------------------------------------ Outline Data ------------- /*+ BEGIN_OUTLINE_DATA FULL(@"SEL$639A81C9" "T"@"SEL$1") OUTLINE(@"SEL$1") ELIMINATE_JOIN(@"SEL$1" "U"@"SEL$1") OUTLINE_LEAF(@"SEL$639A81C9") ALL_ROWS DB_VERSION('11.2.0.1') OPTIMIZER_FEATURES_ENABLE('11.2.0.1') IGNORE_OPTIM_EMBEDDED_HINTS END_OUTLINE_DATA */ 在上例中，由于消除了关联操作，在最终的执行计划中没有出现对表T_USERS的扫描。 3.1.15　子查询合并子查询合并（Subquery Coalesce）是11g中引入的新的查询重写技术。它是转换器在未对子查询做反嵌套的情况下，将两个兼容的子查询合并为一个子查询。示例如代码清单3-14所示（在11.2.0.1中运行）。提示：子查询合并可以通过优化器参数“_optimizer_coalesce_subqueries”或提示NO_COALESCE_SQ/COALESCE_SQ控制。代码清单3-14　子查询合并 HELLODBA.COM>begin 2 sql_explain(' 3 SELECT /*+qb_name(mn)*/t.* FROM t_tables t 4 where exists 5 (select /*+qb_name(sub1)*/1 from t_tablespaces ts where t.tablespace_name=ts.tablespace_name and ts.block_size=:A) 6 and exists 7 (select /*+qb_name(sub2)*/1 from t_tablespaces ts where t.tablespace_name=ts.tablespace_name)', 8 'TYPICAL OUTLINE'); 9 end; 10 / Plan hash value: 1517422251 -------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 299 | 78039 | 34 (3)| 00:00:01 | |* 1 | HASH JOIN RIGHT SEMI| | 299 | 78039 | 34 (3)| 00:00:01 | |* 2 | TABLE ACCESS FULL | T_TABLESPACES | 1 | 20 | 3 (0)| 00:00:01 | |* 3 | TABLE ACCESS FULL | T_TABLES | 2388 | 562K| 30 (0)| 00:00:01 | -------------------------------------------------------------------------------------- Outline Data ------------- /*+ BEGIN_OUTLINE_DATA SWAP_JOIN_INPUTS(@"SEL$B5887B32" "TS"@"SUB1") USE_HASH(@"SEL$B5887B32" "TS"@"SUB1") LEADING(@"SEL$B5887B32" "T"@"MN" "TS"@"SUB1") FULL(@"SEL$B5887B32" "TS"@"SUB1") FULL(@"SEL$B5887B32" "T"@"MN") OUTLINE(@"SUB1") OUTLINE(@"SUB2") COALESCE_SQ(@"SUB1") OUTLINE(@"SEL$4B4BED4E") COALESCE_SQ(@"SUB2") OUTLINE(@"SEL$5DD85A06") OUTLINE(@"MN") UNNEST(@"SEL$5DD85A06") OUTLINE_LEAF(@"SEL$B5887B32") 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("T"."TABLESPACE_NAME"="TS"."TABLESPACE_NAME") 2 - filter("TS"."BLOCK_SIZE"=TO_NUMBER(:A)) 3 - filter("T"."TABLESPACE_NAME" IS NOT NULL) 在上例中，子查询SUB1和SBU2被查询转换器融合了，它们的查询对象被直接关联。 3.1.16　公共子表达式消除公共子表达式消除（Common Subexpression Elimination，CSE）是一项启发式查询转换技术，它将反意连接词（如OR）连接的谓词进行合并，消除不必要的子表达式。提示：公共子表达式消除可以由优化器参数“_eliminate_common_subexpr”控制。 HELLODBA.COM>exec sql_explain('select u.user_id from t_users u, t_tables t where (u.username = t.owner and u.created<:A) or (u.username = t.owner and u.created>:C)','TYPICAL OUTLINE'); Plan hash value: 1798698299 ------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 400 | 8400 | 4 (0)| 00:00:05 | | 1 | NESTED LOOPS | | 400 | 8400 | 4 (0)| 00:00:05 | |* 2 | TABLE ACCESS FULL| T_USERS | 4 | 64 | 2 (0)| 00:00:03 | |* 3 | INDEX RANGE SCAN | T_TABLES_IDX1 | 99 | 495 | 1 (0)| 00:00:02 | ------------------------------------------------------------------------------------ Outline Data ------------- /*+ BEGIN_OUTLINE_DATA USE_NL(@"SEL$1" "T"@"SEL$1") LEADING(@"SEL$1" "U"@"SEL$1" "T"@"SEL$1") INDEX(@"SEL$1" "T"@"SEL$1" ("T_TABLES"."OWNER")) FULL(@"SEL$1" "U"@"SEL$1") OUTLINE_LEAF(@"SEL$1") OPT_PARAM('optimizer_index_cost_adj' 60) OPTIMIZER_FEATURES_ENABLE('10.2.0.4') IGNORE_OPTIM_EMBEDDED_HINTS END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("U"."CREATED"<:A OR "U"."CREATED">:C) 3 - access("U"."USERNAME"="T"."OWNER") 上述例子中，转换器通过将OR连接的两个谓词表达式合并，从而减少了关联操作。公共子表达式消除无法从概要数据或执行计划中鉴别出来，但是从11g的10053事件跟踪中可以发现公共子表达式消除的转换。 ************************* Common Subexpression elimination (CSE) ************************* CSE: CSE performed on query block SEL$1 (#0). 3.1.17　计数函数转变计数函数转变（Count Convertion）是一项启发式的查询转换技术，它将表达式COUNT (Column)转变为COUNT(*)。 HELLODBA.COM>exec sql_explain('select owner, count(table_name) from t_tables t group by owner having count(table_name) > 10','TYPICAL OUTLINE'); Plan hash value: 3903624675 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2 | 10 | 3 (34)| 00:00:04 | |* 1 | FILTER | | | | | | | 2 | HASH GROUP BY | | 2 | 10 | 3 (34)| 00:00:04 | | 3 | INDEX FAST FULL SCAN| T_TABLES_IDX1 | 2071 | 10355 | 2 (0)| 00:00:03 | ---------------------------------------------------------------------------------------- Outline Data ------------- /*+ BEGIN_OUTLINE_DATA INDEX_FFS(@"SEL$1" "T"@"SEL$1" ("T_TABLES"."OWNER")) OUTLINE_LEAF(@"SEL$1") OPT_PARAM('optimizer_index_cost_adj' 60) OPT_PARAM('_eliminate_common_subexpr' 'false') OPTIMIZER_FEATURES_ENABLE('10.2.0.4') IGNORE_OPTIM_EMBEDDED_HINTS END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(COUNT(*)>10) 上面例子中，count(table_name)被转变为COUNT(*)。计数函数转变无法从概要数据或执行计划中鉴别出来，但是从11g的10053事件跟踪中可以发现公共子表达式消除的转换。 ************************* Count(col) to Count(*) (CNT) ************************* CNT: Converting COUNT(TABLE_NAME) to COUNT(*). CNT: Converting COUNT(TABLE_NAME) to COUNT(*). CNT: COUNT() to COUNT(*) done. 注意，由于空值不会被计数，因此计数函数转变不会对允许空值的字段进行转换。 3.1.18　表达式和条件评估表达式和条件评估（Evaluation of Expressions and Conditions）是一项启发式查询转换技术，它将一些表达式转换为更加高效、语义上等价的表达式。 3.1.18.1　常量转换将表达式计算出结果，用该结果作为常量替换原表达式。 HELLODBA.COM>exec sql_explain('select * from t_tables t where avg_row_len > 10*10','TYPICAL'); Plan hash value: 505459244 ------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1941 | 352K| 6 (0)| 00:00:07 | |* 1 | TABLE ACCESS FULL| T_TABLES | 1941 | 352K| 6 (0)| 00:00:07 | ------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("AVG_ROW_LEN">100) PL/SQL procedure successfully completed. 上述例子中，表达式10*10被常量100所替换。 3.1.18.2　LIKE转换 LIKE本身是一个范围匹配操作，但是如果LIKE匹配的表达式中没有通配符（如%、_），则与“=”等价。 HELLODBA.COM>exec sql_explain('select * from t_users t where username like ''ABC''','TYPICAL'); Plan hash value: 4216803708 ------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 112 | 1 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T_USERS | 1 | 112 | 1 (0)| 00:00:01 | |* 2 | INDEX UNIQUE SCAN | T_USERS_UK | 1 | | 0 (0)| 00:00:01 | ------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("USERNAME"='ABC') 提示：上述执行计划输出是在11g当中。而在10g及之前版本中，在谓词信息中看不出这一转换，但执行计划相应的访问路径却是按照转换后得出的。 3.1.18.3　IN转换将IN操作符转换为OR连接表达式。 HELLODBA.COM>exec sql_explain('select /*+full(T)*/* from t_users t where username in (:A,:B)','TYPICAL'); Plan hash value: 616708042 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 90 | 2 (0)| 00:00:03 | |* 1 | TABLE ACCESS FULL| T_USERS | 1 | 90 | 2 (0)| 00:00:03 | ----------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("USERNAME"=:A OR "USERNAME"=:B) 在上例中，谓词条件USERNAME IN (:A, :B)被转换成("USERNAME"=:A OR "USERNAME"=:B)。 3.1.18.4　IN子句转换将IN子句转换为=ANY子句。 HELLODBA.COM>alter session set tracefile_identifier = 'IN_TO_ANY'; Session altered. HELLODBA.COM>alter session set events 'TRACE[RDBMS.SQL_Transform.*]'; Session altered. HELLODBA.COM>explain plan for select /*+ NO_UNNEST(@INV) */distinct object_name from t_objects o where object_name in (select /*+qb_name(inv)*/table_name from t_tables t); Explained. 在上例的跟踪信息中，谓词条件IN (…) 被转换成=ANY(…)。 FPD: Current where clause predicates "O"."OBJECT_NAME"=ANY (SELECT /*+ NO_UNNEST QB_NAME ("INV") */ "T"."TABLE_NAME" FROM "T_TABLES" "T") 3.1.18.5　NOT IN子句转换将IN子句转换为<>ALL子句。 HELLODBA.COM>alter session set tracefile_identifier = 'NOT_IN_TO_ALL'; Session altered. HELLODBA.COM>alter session set events 'TRACE[RDBMS.SQL_Transform.*]'; Session altered. HELLODBA.COM>explain plan for select /*+ NO_UNNEST(@INV) */distinct object_name from t_objects o where object_name not in (select /*+qb_name(inv)*/table_name from t_tables t); Explained. 在上例的跟踪信息中，谓词条件IN (…) 被转换成<>ALL(…)。 FPD: Current where clause predicates "O"."OBJECT_NAME"<>ALL (SELECT /*+ NO_UNNEST QB_NAME ("INV") */ "T"."TABLE_NAME" FROM "T_TABLES" "T") 3.1.18.6　ANY/SOME转换将ANY/SOME转换为OR连接表达式或者EXISTS子查询。示例1： HELLODBA.COM>exec sql_explain('select * from t_tables t where avg_row_len > any (:A,:B)', 'TYPICAL'); Plan hash value: 505459244 ------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 202 | 37572 | 6 (0)| 00:00:07 | |* 1 | TABLE ACCESS FULL| T_TABLES | 202 | 37572 | 6 (0)| 00:00:07 | ------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("AVG_ROW_LEN">TO_NUMBER(:A) OR "AVG_ROW_LEN">TO_NUMBER(:B)) 在上例中，谓词条件avg_row_len > any (:A,:B)被转换为谓词表达式("AVG_ROW_LEN">TO_NUMBER(:A) OR "AVG_ROW_LEN">TO_NUMBER(:B))。示例2： HELLODBA.COM>exec sql_explain('select * from t_tables t where last_analyzed > some (select created from t_users u)', 'TYPICAL'); Plan hash value: 2799022803 -------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | -------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 104 | 20072 | | 80 (3)| 00:01:21 | | 1 | MERGE JOIN SEMI | | 104 | 20072 | | 80 (3)| 00:01:21 | | 2 | SORT JOIN | | 2071 | 376K| 1192K| 78 (2)| 00:01:19 | | 3 | TABLE ACCESS FULL| T_TABLES | 2071 | 376K| | 6 (0)| 00:00:07 | |* 4 | SORT UNIQUE | | 41 | 287 | | 2 (50)| 00:00:03 | | 5 | INDEX FULL SCAN | T_USERS_IDX1 | 41 | 287 | | 1 (0)| 00:00:02 | -------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - access(INTERNAL_FUNCTION("LAST_ANALYZED")>INTERNAL_FUNCTION("CREATED")) filter(INTERNAL_FUNCTION("LAST_ANALYZED")>INTERNAL_FUNCTION("CREATED")) 上面示例将SOME转换为了EXISTS子查询，即与以下查询等价： HELLODBA.COM>exec sql_explain('select * from t_tables t where exists (select created from t_users u where t.last_analyzed>created)', 'TYPICAL'); Plan hash value: 2799022803 -------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | -------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 104 | 20072 | | 80 (3)| 00:01:21 | | 1 | MERGE JOIN SEMI | | 104 | 20072 | | 80 (3)| 00:01:21 | | 2 | SORT JOIN | | 2071 | 376K| 1192K| 78 (2)| 00:01:19 | | 3 | TABLE ACCESS FULL| T_TABLES | 2071 | 376K| | 6 (0)| 00:00:07 | |* 4 | SORT UNIQUE | | 41 | 287 | | 2 (50)| 00:00:03 | | 5 | INDEX FULL SCAN | T_USERS_IDX1 | 41 | 287 | | 1 (0)| 00:00:02 | -------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - access(INTERNAL_FUNCTION("T"."LAST_ANALYZED")>INTERNAL_FUNCTION("CREATED")) filter(INTERNAL_FUNCTION("T"."LAST_ANALYZED")>INTERNAL_FUNCTION("CREATED")) 3.1.18.7　ALL转换将ALL转换为AND连接表达式、GREATEST函数或者NOT EXISTS子查询。示例1： HELLODBA.COM>exec sql_explain('select * from t_tables t where avg_row_len > ALL (:A,:B)', 'TYPICAL'); Plan hash value: 505459244 ------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 5 | 930 | 6 (0)| 00:00:07 | |* 1 | TABLE ACCESS FULL| T_TABLES | 5 | 930 | 6 (0)| 00:00:07 | ------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("AVG_ROW_LEN">TO_NUMBER(:A) AND "AVG_ROW_LEN">TO_NUMBER(:B)) 以上示例中，谓词条件avg_row_len > ALL (:A,:B)被转换成了"AVG_ROW_LEN">TO_NUMBER(:A) AND "AVG_ROW_LEN">TO_NUMBER(:B)。示例2： HELLODBA.COM>exec sql_explain('select * from t_tables t where avg_row_len > ALL (select max_extents/ 100 from t_tablespaces)', 'TYPICAL'); Plan hash value: 1401377001 ------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 2061 | 410K| 1152 (1)| 00:00:05 | |* 1 | FILTER | | | | | | | 2 | TABLE ACCESS FULL| T_TABLES | 2071 | 412K| 142 (1)| 00:00:01 | |* 3 | TABLE ACCESS FULL| T_TABLESPACES | 3 | 18 | 5 (0)| 00:00:01 | ------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM "T_TABLESPACES" "T_TABLESPACES" WHERE LNNVL("MAX_EXTENTS"/100<:B1))) 3 - filter(LNNVL("MAX_EXTENTS"/100<:B1)) 上述例子在10.2.0.4中运行，ALL被转换成了NOT EXISTS子句。而在11.2.0.1中，还能将NOT EXISTS子句进一步展开，转换成对空值敏感的反关联操作。 3.1.18.8　BETWEEN AND转换优化器在任何情况下都会将BETWEEN AND转换为<=、>=。 HELLODBA.COM>exec sql_explain('select * from t_tables t where avg_row_len between :A and :B', 'TYPICAL'); Plan hash value: 2079852122 ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 13 | 3133 | 30 (0)| 00:00:01 | |* 1 | FILTER | | | | | | |* 2 | TABLE ACCESS FULL| T_TABLES | 13 | 3133 | 30 (0)| 00:00:01 | ------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(TO_NUMBER(:A)<=TO_NUMBER(:B)) 2 - filter("AVG_ROW_LEN">=TO_NUMBER(:A) AND "AVG_ROW_LEN"<=TO_NUMBER(:B)) 3.1.18.9　 NOT转换优化器会对含有NOT操作的表达式进行逻辑转换。 HELLODBA.COM>exec sql_explain('select * from t_tables t where not avg_row_len > ALL (:A,:B)', 'TYPICAL'); Plan hash value: 505459244 ------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 202 | 37572 | 6 (0)| 00:00:07 | |* 1 | TABLE ACCESS FULL| T_TABLES | 202 | 37572 | 6 (0)| 00:00:07 | ------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("AVG_ROW_LEN"<=TO_NUMBER(:A) OR "AVG_ROW_LEN"<=TO_NUMBER(:B)) 上述例子中，转换器先将not avg_row_len > ALL (:A,:B)转换为avg_row_len <= ANY (:A,:B)，然后将ANY转换为OR表达式。 3.1.18.10　逻辑传递优化器可以根据谓词之间的逻辑传递关系，将某个谓词条件中的表达式或常量传递给另一个谓词条件。 HELLODBA.COM>exec sql_explain('select * from t_users where lock_date<expiry_date and expiry_date<:A', 'TYPICAL'); Plan hash value: 616708042 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 86 | 2 (0)| 00:00:03 | |* 1 | TABLE ACCESS FULL| T_USERS | 1 | 86 | 2 (0)| 00:00:03 | ----------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("LOCK_DATE"<"EXPIRY_DATE" AND "LOCK_DATE"<:A AND "EXPIRY_DATE"<:A) 上述例子中，根据LOCK_DATE与EXPIRY_DATE之间的关系（"LOCK_DATE"<"EXPIRY_DATE"），将"EXPIRY_DATE"<:A传递给了LOCK_DATE。 3.1.19　聚集子查询消除聚集子查询消除（Aggregate Subquery Elimination）是一项利用窗口函数移除子查询（Remove Subquery using Window functions，RSW）的优化技术。提示：聚集子查询消除可以由优化器参“_remove_aggr_subquery”控制。示例如下： HELLODBA.COM>exec sql_explain('select u1.user_id,t.owner,t.table_name from t_users u1, t_tables t where t.owner=u1.username and u1.created = (select max(u2.created) from t_users u2 where u2.username = t.owner)', 'TYPICAL'); Plan hash value: 1272811367 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2696 | 155K| 9 (12)| 00:00:01 | |* 1 | VIEW | VW_WIF_1 | 2696 | 155K| 9 (12)| 00:00:01 | | 2 | WINDOW BUFFER | | 2696 | 163K| 9 (12)| 00:00:01 | | 3 | MERGE JOIN | | 2696 | 163K| 9 (12)| 00:00:01 | | 4 | TABLE ACCESS BY INDEX ROWID| T_USERS | 31 | 1054 | 2 (0)| 00:00:01 | | 5 | INDEX FULL SCAN | T_USERS_UK | 31 | | 1 (0)| 00:00:01 | |* 6 | SORT JOIN | | 2696 | 75488 | 7 (15)| 00:00:01 | | 7 | INDEX FAST FULL SCAN | T_TABLES_PK | 2696 | 75488 | 6 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("VW_COL_4" IS NOT NULL) 6 - access("T"."OWNER"="U1"."USERNAME") filter("T"."OWNER"="U1"."USERNAME") 上述例子中，从执行计划中可以看到“WINDOW BUFFER”操作，子查询已经被转换为聚集函数。在11g中对上述优化器解析过程做10053事件跟踪，可以在跟踪记录中找到转换后的语句： RSW: query after subquery removal:******* UNPARSED QUERY IS ******* SELECT "VW_WIF_1"."ITEM_1" "USER_ID","VW_WIF_1"."ITEM_2" "OWNER","VW_WIF_1"."ITEM_3" "TABLE_NAME" FROM (SELECT "U1"."USER_ID" "ITEM_1","T"."OWNER" "ITEM_2","T"."TABLE_NAME" "ITEM_3",CASE WHEN "U1"."CREATED"=MAX("U1"."CREATED") OVER ( PARTITION BY "U1"."USERNAME") THEN "U1".ROWID END "VW_COL_4" FROM "DEMO"."T_TABLES" "T","DEMO"."T_USERS" "U1" WHERE "T"."OWNER"="U1"."USERNAME") "VW_WIF_1" WHERE "VW_WIF_1"."VW_COL_4" IS NOT NULL Subquery converted. 3.1.20　DISTINCT聚集函数转换在Oracle 10gR2中，引入了哈希分组的方法来计算聚集函数，在数据量比较大时，这种方法比传统的排序分组方法效率更高。但是，对于那些对唯一数值（含有DISTINCT/UNIQUE）进行计算的聚集函数来说，由于要消除重复值，因此还是要使用排序分组的方法。在11gR2中，引入了一项新的查询转换技术，将含有DISTINCT的聚集函数转换为一个聚集内联视图，使得查询同样可以利用哈希分组的方法来计算聚集函数。提示：DISTINCT聚集函数转换可以由优化器参数“_optimizer_distinct_agg_transform”或提示TRANSFORM_DISTINCT_AGG/NO_TRANSFORM_DISTINCT_AGG控制。示例如下（以下示例在11.2.0.1中运行）： HELLODBA.COM>exec sql_explain('select owner, avg(avg_row_len), count(distinct table_name) from t_ tables group by owner', 'TYPICAL OUTLINE'); Plan hash value: 1728567205 --------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 18 | 900 | 31 (4)| 00:00:01 | | 1 | HASH GROUP BY | | 18 | 900 | 31 (4)| 00:00:01 | | 2 | VIEW | VW_DAG_0 | 2696 | 131K| 31 (4)| 00:00:01 | | 3 | HASH GROUP BY | | 2696 | 83576 | 31 (4)| 00:00:01 | | 4 | TABLE ACCESS FULL| T_TABLES | 2696 | 83576 | 30 (0)| 00:00:01 | --------------------------------------------------------------------------------- Outline Data ------------- /*+ BEGIN_OUTLINE_DATA USE_HASH_AGGREGATION(@"SEL$5771D262") FULL(@"SEL$5771D262" "T_TABLES"@"SEL$1") USE_HASH_AGGREGATION(@"SEL$C33C846D") NO_ACCESS(@"SEL$C33C846D" "VW_DAG_0"@"SEL$C33C846D") TRANSFORM_DISTINCT_AGG(@"SEL$1") OUTLINE(@"SEL$5771D262") OUTLINE(@"SEL$1") OUTLINE_LEAF(@"SEL$C33C846D") TRANSFORM_DISTINCT_AGG(@"SEL$1") OUTLINE_LEAF(@"SEL$5771D262") ALL_ROWS DB_VERSION('11.2.0.1') OPTIMIZER_FEATURES_ENABLE('11.2.0.1') IGNORE_OPTIM_EMBEDDED_HINTS END_OUTLINE_DATA */ 在上例中，“distinct table_name”被转换成一个聚集内联视图（VW_DAG_0）。对上述语句做10053事件跟踪，可以看到以下转换信息： DAGG_TRANSFORM: transforming query block SEL$1 (#0) qbcp (before transform):******* UNPARSED QUERY IS ******* SELECT "T_TABLES"."OWNER" "OWNER",AVG("T_TABLES"."AVG_ROW_LEN") "AVG(AVG_ROW_LEN)",COUNT(DISTINCT "T_TABLES"."TABLE_NAME") "COUNT(DISTINCTTABLE_NAME)" FROM "DEMO"."T_TABLES" "T_TABLES" GROUP BY "T_TABLES"."OWNER" pgactx->ctxqbc (before transform):******* UNPARSED QUERY IS ******* SELECT "T_TABLES"."OWNER" "OWNER",AVG("T_TABLES"."AVG_ROW_LEN") "AVG(AVG_ROW_LEN)",COUNT(DISTINCT "T_TABLES"."TABLE_NAME") "COUNT(DISTINCTTABLE_NAME)" FROM "DEMO"."T_TABLES" "T_TABLES" GROUP BY "T_TABLES"."OWNER" Registered qb: SEL$5771D262 0x1e94b604 (SPLIT QUERY BLOCK FOR DISTINCT AGG OPTIM SEL$1; SEL$1) --------------------- QUERY BLOCK SIGNATURE --------------------- signature (): qb_name=SEL$5771D262 nbfros=1 flg=0 fro(0): flg=0 objn=73126 hint_alias="T_TABLES"@"SEL$1" Registered qb: SEL$C33C846D 0x21f4b74c (MAP QUERY BLOCK SEL$5771D262) --------------------- QUERY BLOCK SIGNATURE --------------------- signature (): qb_name=SEL$C33C846D nbfros=1 flg=0 fro(0): flg=5 objn=0 hint_alias="VW_DAG_0"@"SEL$C33C846D" qbcp (after transform):******* UNPARSED QUERY IS ******* SELECT "VW_DAG_0"."ITEM_2" "OWNER",DECODE(NVL(SUM("VW_DAG_0"."ITEM_4"),0),0,TO_NUMBER(NULL),SUM("VW_ DAG_0"."ITEM_3")/NVL(SUM("VW_DAG_0"."ITEM_4"),0)) "AVG(AVG_ROW_LEN)",COUNT("VW_DAG_0"."ITEM_1") "COUNT(DISTINCTTABLE_NAME)" FROM (SELECT "T_TABLES"."TABLE_NAME" "ITEM_1","T_TABLES"."OWNER" "ITEM_2",SUM("T_TABLES"."AVG_ROW_LEN") "ITEM_3",COUNT("T_TABLES"."AVG_ROW_LEN") "ITEM_4" FROM "DEMO"."T_TABLES" "T_TABLES" GROUP BY "T_TABLES"."TABLE_NAME","T_TABLES"."OWNER") "VW_DAG_0" GROUP BY "VW_DAG_0"."ITEM_2" pgactx->ctxqbc (after transform):******* UNPARSED QUERY IS ******* SELECT "VW_DAG_0"."ITEM_2" "OWNER",DECODE(NVL(SUM("VW_DAG_0"."ITEM_4"),0),0,TO_NUMBER(NULL),SUM("VW_ DAG_0"."ITEM_3")/NVL(SUM("VW_DAG_0"."ITEM_4"),0)) "AVG(AVG_ROW_LEN)",COUNT("VW_DAG_0"."ITEM_1") "COUNT(DISTINCTTABLE_NAME)" FROM (SELECT "T_TABLES"."TABLE_NAME" "ITEM_1","T_TABLES"."OWNER" "ITEM_2",SUM("T_TABLES"."AVG_ROW_LEN") "ITEM_3",COUNT("T_TABLES"."AVG_ROW_LEN") "ITEM_4" FROM "DEMO"."T_TABLES" "T_TABLES" GROUP BY "T_TABLES"."TABLE_NAME","T_TABLES"."OWNER") "VW_DAG_0" GROUP BY "VW_DAG_0"."ITEM_2" 我们还可以从跟踪信息中看到被转换后的语句。 3.1.21　选择字段裁剪选择字段裁剪（Select List Pruning，SLP）可以将视图中不必要的字段去除掉，从而避免查询采用效率低下的访问路径。这是一项启发式查询转换技术。 HELLODBA.COM>exec sql_explain('select /*+no_merge(v)*/owner, table_name from (select * from t_tables where tablespace_name=:A) v', 'TYPICAL PROJECTION'); Plan hash value: 2149336122 ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 299 | 10166 | 30 (0)| 00:00:01 | | 1 | VIEW | | 299 | 10166 | 30 (0)| 00:00:01 | |* 2 | TABLE ACCESS FULL| T_TABLES | 299 | 10465 | 30 (0)| 00:00:01 | ------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("TABLESPACE_NAME"=:A) Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - "OWNER"[VARCHAR2,30], "TABLE_NAME"[VARCHAR2,30] 2 - "T_TABLES"."OWNER"[VARCHAR2,30], "T_TABLES"."TABLE_NAME"[VARCHAR2,30] 上述例子中，优化器将内联视图中除owner和table_name以外的所有字段都移除了。对上述语句做10053事件跟踪（11g中），可以看到以下的转换信息： SLP: Removed select list item TABLESPACE_NAME from query block SEL$2 SLP: Removed select list item CLUSTER_NAME from query block SEL$2 SLP: Removed select list item IOT_NAME from query block SEL$2 ... 3.1.22　DISTINCT消除 DISTINCT消除（ Elimination）技术可以将语句中不必要的DISTINCT去除掉，从而生成更加高效的执行计划。该技术在11g中引入。提示：DISTINCT消除可以由优化器参数“_optimizer_distinct_elimination”控制。示例如下：（以下示例在11.2.0.1中运行）： HELLODBA.COM>exec sql_explain('select distinct username from t_users', 'TYPICAL OUTLINE'); Plan hash value: 752775360 ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 31 | 310 | 1 (0)| 00:00:01 | | 1 | INDEX FULL SCAN | T_USERS_UK | 31 | 310 | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------- Outline Data ------------- /*+ BEGIN_OUTLINE_DATA INDEX(@"SEL$1" "T_USERS"@"SEL$1" ("T_USERS"."USERNAME")) OUTLINE_LEAF(@"SEL$1") ALL_ROWS DB_VERSION('11.2.0.1') OPTIMIZER_FEATURES_ENABLE('11.2.0.1') IGNORE_OPTIM_EMBEDDED_HINTS END_OUTLINE_DATA */ 上例中，由于索引T_USERS_UK是一个唯一索引，由其读到的数据必定是唯一的，因而DISTINCT可以从语句中消除掉。 3.1.23　DISTINCT推入 DISTINCT推入（Push Down）使得优化器在生成含DISTINCT的反关联（ANTI-JOIN）查询的执行计划时，优先执行DISTINCT的相关操作。提示：DISTINCT推入可以由优化器参数“_optimizer_push_down_distinct”控制，0为禁止进行DISTINCT推入转换，100为始终进行DISTINCT推入转换，默认为0。 HELLODBA.COM>exec sql_explain('select distinct object_name from t_objects o where object_name not in (select /*+qb_name(inv)*/table_name from t_tables t)', 'TYPICAL OUTLINE'); Plan hash value: 1892615514 --------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2030 | 85260 | 1015 (2)| 00:00:05 | |* 1 | HASH JOIN RIGHT ANTI| | 27420 | 1124K| 1015 (2)| 00:00:05 | | 2 | INDEX FULL SCAN | T_TABLES_PK | 2071 | 37278 | 11 (0)| 00:00:01 | | 3 | SORT UNIQUE | | 47585 | 1115K| 322 (1)| 00:00:02 | | 4 | INDEX FULL SCAN | T_OBJECTS_IDX1 | 47585 | 1115K| 322 (1)| 00:00:02 | --------------------------------------------------------------------------------------- Outline Data ------------- /*+ BEGIN_OUTLINE_DATA SWAP_JOIN_INPUTS(@"SEL$B384BBCF" "T"@"INV") USE_HASH(@"SEL$B384BBCF" "T"@"INV") LEADING(@"SEL$B384BBCF" "O"@"SEL$1" "T"@"INV") INDEX(@"SEL$B384BBCF" "T"@"INV" ("T_TABLES"."TABLE_NAME" "T_TABLES"."OWNER")) INDEX(@"SEL$B384BBCF" "O"@"SEL$1" ("T_OBJECTS"."STATUS" "T_OBJECTS"."OWNER" "T_OBJECTS"."OBJECT_NAME")) OUTLINE(@"INV") OUTLINE(@"SEL$1") UNNEST(@"INV") OUTLINE_LEAF(@"SEL$B384BBCF") OPT_PARAM('_optimizer_push_down_distinct' 100) OPTIMIZER_FEATURES_ENABLE('10.2.0.4') IGNORE_OPTIM_EMBEDDED_HINTS END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("OBJECT_NAME"="TABLE_NAME") 在上例中，DISTINCT推入转换使得DISTINCT相关的操作成为关联操作的子操作。 3.1.24　集合分组查询转换集合分组查询转换（Grouping Set To Union）将集合分组查询转换为视图，使得它能和其他对象做UNION-ALL查询。提示：这一技术也称为集合分组临时表转换（Grouping Set Temp Table Transform）。可以由提示USE_TTT_FOR_GSETS控制。 3.1.25　集合分组查询重写集合分组查询重写（Grouping Set Rewrite）将集合分组查询转换为UNION-ALL视图查询，重写为兼容的物化视图。提示：集合分组查询重写可以由提示EXPAND_GSET_TO_UNION/NO_EXPAND_GSET_TO_UNION控制，也可以由优化器参数“_union_rewrite_for_gs”控制。 HELLODBA.COM>exec sql_explain('select /*+EXPAND_GSET_TO_UNION REWRITE*/owner, status, count(object_name) from t_objects group by owner, rollup(status)', 'TYPICAL OUTLINE'); Plan hash value: 1905288239 ------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 54 | 1890 | 333 (1)| 00:00:02 | | 1 | VIEW | | 54 | 1890 | 333 (1)| 00:00:02 | | 2 | UNION-ALL | | | | | | | 3 | SORT GROUP BY NOSORT | | 32 | 832 | 322 (1)| 00:00:02 | | 4 | INDEX FULL SCAN | T_OBJECTS_IDX1 | 47585 | 1208K| 322 (1)| 00:00:02 | | 5 | MAT_VIEW REWRITE ACCESS FULL| MV_OBJECTS_GP | 22 | 242 | 11 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------- Outline Data ------------- /*+ BEGIN_OUTLINE_DATA FULL(@"SEL$412B92C6" "MV_OBJECTS_GP"@"SEL$D4AFF4CE") INDEX(@"SEL$AE491F43_1" "T_OBJECTS"@"SEL$1" ("T_OBJECTS"."STATUS" "T_OBJECTS"."OWNER" "T_OBJECTS"."OBJECT_NAME")) NO_ACCESS(@"SEL$AE491F43" "$kkqt_split_view"@"SEL$AE491F43") OUTLINE(@"SEL$AE491F43_2") OUTLINE(@"SEL$1") EXPAND_GSET_TO_UNION(@"SEL$1") OUTLINE(@"SEL$AE491F43") REWRITE(@"SEL$AE491F43_2" "MV_OBJECTS_GP") OUTLINE(@"SEL$27AF09BB") EXPAND_GSET_TO_UNION(@"SEL$1") OUTLINE_LEAF(@"SEL$AE491F43") OUTLINE_LEAF(@"SET$D2F1856C") OUTLINE_LEAF(@"SEL$AE491F43_1") REWRITE(@"SEL$27AF09BB" "MV_OBJECTS_GP") OUTLINE_LEAF(@"SEL$412B92C6") OPTIMIZER_FEATURES_ENABLE('10.2.0.4') IGNORE_OPTIM_EMBEDDED_HINTS END_OUTLINE_DATA */ 在上例中，物化视图 MV_OBJECTS_GP的定义语句为： create materialized view mv_objects_gp enable query rewrite as select count(object_name), owner from t_objects group by owner; 查询转换器首先将聚合分组查询（GROUP BY ROLLUP）展开，重写为UNION-ALL，然后将其中一个子查询重写为对物化视图MV_OBJECTS_GP的查询。从10053事件的跟踪中，可以看到上述语句被重写为下面的语句： SELECT /*+ REWRITE EXPAND_GSET_TO_UNION */ "$kkqt_split_view"."OWNER" "OWNER", "$kkqt_split_view"."STATUS" "STATUS", "$kkqt_split_view"."VW_COL_1" "COUNT(OBJECT_NAME)" FROM ((SELECT /*+ REWRITE EXPAND_GSET_TO_UNION */ COUNT(*) "VW_COL_1", "T_OBJECTS"."STATUS" "STATUS", "T_OBJECTS"."OWNER" "OWNER" FROM "DEMO"."T_OBJECTS" "T_OBJECTS" GROUP BY "T_OBJECTS"."OWNER", "T_OBJECTS"."STATUS") UNION ALL (SELECT /*+ REWRITE EXPAND_GSET_TO_UNION */ "MV_OBJECTS_GP"."COUNT(OBJECT_NAME)" "VW_COL_1", NULL "STATUS", "MV_OBJECTS_GP"."OWNER" "OWNER" FROM "DEMO"."MV_OBJECTS_GP" "MV_OBJECTS_GP")) "$kkqt_split_view" 3.1.26　集合分组裁剪集合分组裁剪（Group Pruning）将其他查询块中的谓词条件推入集合分组查询块当中，使得优化器可以不访问被过滤的分组集合。示例如代码清单3-15所示。提示：SQL提示NO_PRUNE_GSETS可以禁止优化器进行此项查询转换。代码清单3-15　集合分组裁剪 HELLODBA.COM>exec sql_explain('select /*+ qb_name(m) */v.owner, v.table_name, v.constraint_type, cns_cnt from (select /* + qb_name(gv) */owner, table_name, constraint_type, count(constraint_name) cns_cnt from t_constraints c group by cube(ow ner, table_name, constraint_type)) v where v.owner = ''DEMO''','TYPICAL OUTLINE'); Plan hash value: 3455729417 ----------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 16 | 1504 | 20 (10)| 00:00:01 | | 1 | VIEW | | 16 | 1504 | 20 (10)| 00:00:01 | | 2 | TEMP TABLE TRANSFORMATION | | | | | | | 3 | MULTI-TABLE INSERT | | | | | | | 4 | DIRECT LOAD INTO | SYS_TEMP_0FD9D661C_F1EAFE3B | | | | | | 5 | DIRECT LOAD INTO | SYS_TEMP_0FD9D661D_F1EAFE3B | | | | | | 6 | SORT GROUP BY ROLLUP | | 16 | 496 | 13 (8)| 00:00:01 | | 7 | TABLE ACCESS BY INDEX ROWID | T_CONSTRAINTS | 56 | 1736 | 12 (0)| 00:00:01 | | 8 | BITMAP CONVERSION TO ROWIDS| | | | | | |* 9 | BITMAP INDEX SINGLE VALUE | T_CONSTRAINTS_IDX1 | | | | | | 10 | LOAD AS SELECT | SYS_TEMP_0FD9D661D_F1EAFE3B | | | | | | 11 | SORT GROUP BY ROLLUP | | 1 | 77 | 3 (34)| 00:00:01 | | 12 | TABLE ACCESS FULL | SYS_TEMP_0FD9D661C_F1EAFE3B | 1 | 77 | 2 (0)| 00:00:01 | | 13 | VIEW | | 2 | 188 | 4 (0)| 00:00:01 | | 14 | VIEW | | 2 | 188 | 4 (0)| 00:00:01 | | 15 | UNION-ALL | | | | | | | 16 | TABLE ACCESS FULL | SYS_TEMP_0FD9D661C_F1EAFE3B | 1 | 94 | 2 (0)| 00:00:01 | | 17 | TABLE ACCESS FULL | SYS_TEMP_0FD9D661D_F1EAFE3B | 1 | 94 | 2 (0)| 00:00:01 | ----------------------------------------------------------------------------------------------------------------- 对上述语句的优化器过程进行跟踪，可以看到以下记录（主查询中的谓词条件被推入了集合分组查询块当中）： PM: Pushed down predicate "C"."OWNER"='DEMO' from query block M (#0) to query block GV (#0) 查询被重写为以下形式： SELECT /*+ QB_NAME ("M") */ "V"."OWNER" "OWNER","V"."TABLE_NAME" "TABLE_NAME","V"."CONSTRAINT_TYPE" "CONSTRAINT_TYPE","V"."CNS_CNT" "CNS_CNT" FROM (SELECT /*+ QB_NAME ("GV") */ "C"."OWNER" "OWNER","C"."TABLE_NAME" "TABLE_NAME","C"."CONSTRAINT_TYPE" "CONSTRAINT_TYPE",COUNT(*) "CNS_CNT" FROM "DEMO"."T_CONSTRAINTS" "C" WHERE "C"."OWNER"='DEMO' GROUP BY GROUPING SETS (("C"."OWNER", "C"."TABLE_ NAME", "C"."CONSTRAINT_TYPE"), ("C"."OWNER", "C"."CONSTRAINT_TYPE"), ("C"."OWNER", "C"."TABLE_NAME"), ("C"."OWNER"))) "V" 3.1.27　外关联消除外关联消除（Outer Join Elimination，OJE）在保证语义等价的情况下，将外关联转换为内关联（Inner Join），从而使得优化器能生成更加高效的执行计划。提示：外关联消除可以由提示ELIMINATE_OUTER_JOIN/NO_ELIMINATE_OUTER_JOIN（11g之前版本）、OUTER_JOIN_TO_INNER/NO_OUTER_JOIN_TO_INNER（11g）控制。 HELLODBA.COM>exec sql_explain('select t.owner, u.user_id from t_tables t, t_users u where t.owner=u.username(+) and u.created < :A', 'TYPICAL OUTLINE'); Plan hash value: 3550265735 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 103 | 2575 | 4 (0)| 00:00:01 | | 1 | NESTED LOOPS | | 103 | 2575 | 4 (0)| 00:00:01 | | 2 | TABLE ACCESS BY INDEX ROWID| T_USERS | 2 | 38 | 2 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | T_USERS_IDX1 | 2 | | 1 (0)| 00:00:01 | |* 4 | INDEX RANGE SCAN | T_TABLES_IDX1 | 48 | 288 | 1 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------- Outline Data ------------- /*+ BEGIN_OUTLINE_DATA USE_NL(@"SEL$6E71C6F6" "T"@"SEL$1") LEADING(@"SEL$6E71C6F6" "U"@"SEL$1" "T"@"SEL$1") INDEX(@"SEL$6E71C6F6" "T"@"SEL$1" ("T_TABLES"."OWNER")) INDEX_RS_ASC(@"SEL$6E71C6F6" "U"@"SEL$1" ("T_USERS"."CREATED")) OUTLINE(@"SEL$1") ELIMINATE_OUTER_JOIN(@"SEL$1") OUTLINE_LEAF(@"SEL$6E71C6F6") OPTIMIZER_FEATURES_ENABLE('10.2.0.4') IGNORE_OPTIM_EMBEDDED_HINTS END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("U"."CREATED"<:A) 4 - access("T"."OWNER"="U"."USERNAME") 3.1.28　真正完全外关联真正完全外关联（Native Full Outer Join）将完全外关联查询转换为一个完全外关联视图，使得执行计划能真正使用完全外关联方法。注意，完全外关联操作是在10.2.0.4被引入的新操作。在这之前，优化器只能通过UNION-ALL和左外关联来模拟实现完全外关联。提示：真正完全外关联可以由优化器参数“_optimizer_native_full_outer_join”或提示NATIVE_FULL_OUTER_JOIN/NO_NATIVE_FULL_OUTER_JOIN来控制。 HELLODBA.COM>exec sql_explain('select /*+NATIVE_FULL_OUTER_JOIN*/ts.tablespace_name, ts.block_size, u.user_id from t_tablespaces ts full outer join t_users u on ts.tablespace_name=u.default_ tablespace and ts.max_extents<:A and u.user_id>:B', 'TYPICAL OUTLINE'); Plan hash value: 4264077763 --------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 56 | 2408 | 31 (4)| 00:00:01 | | 1 | VIEW | VW_FOJ_0 | 56 | 2408 | 31 (4)| 00:00:01 | |* 2 | HASH JOIN FULL OUTER| | 56 | 1512 | 31 (4)| 00:00:01 | | 3 | TABLE ACCESS FULL | T_TABLESPACES | 15 | 240 | 11 (0)| 00:00:01 | | 4 | TABLE ACCESS FULL | T_USERS | 43 | 473 | 19 (0)| 00:00:01 | --------------------------------------------------------------------------------------- Outline Data ------------- /*+ BEGIN_OUTLINE_DATA USE_HASH(@"SEL$1" "U"@"SEL$1") LEADING(@"SEL$1" "TS"@"SEL$1" "U"@"SEL$1") FULL(@"SEL$1" "U"@"SEL$1") FULL(@"SEL$1" "TS"@"SEL$1") NO_ACCESS(@"SEL$2" "from$_subquery$_003"@"SEL$2") OUTLINE_LEAF(@"SEL$2") OUTLINE_LEAF(@"SEL$1") OPTIMIZER_FEATURES_ENABLE('10.2.0.4') IGNORE_OPTIM_EMBEDDED_HINTS END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("TS"."TABLESPACE_NAME"="U"."DEFAULT_TABLESPACE") filter("TS"."MAX_EXTENTS"<TO_NUMBER(:A) AND "U"."USER_ID">TO_NUMBER(:B)) 上例中，查询被转换为了以下形式： SELECT /*+ NATIVE_FULL_OUTER_JOIN */ "VW_FOJ_0"."TABLESPACE_NAME_0" "TABLESPACE_NAME", "VW_FOJ_0"."BLOCK_SIZE_1" "BLOCK_SIZE", "VW_FOJ_0"."USER_ID_2" "USER_ID" FROM (SELECT "TS"."TABLESPACE_NAME" "TABLESPACE_NAME_0", "TS"."BLOCK_SIZE" "BLOCK_SIZE_1", "U"."USER_ID" "USER_ID_2" FROM "DEMO"."T_USERS" "U" FULL OUTER JOIN "DEMO"."T_TABLESPACES" "TS" ON "TS"."TABLESPACE_NAME" = "U"."DEFAULT_TABLESPACE" AND "TS"."MAX_EXTENTS" < TO_NUMBER(:B1) AND "U"."USER_ID" > TO_NUMBER(:B2)) "VW_FOJ_0" 而如果没有使用真正完全外关联技术，上述查询则会被转换为以下形式： SELECT "from$_subquery$_003"."TABLESPACE_NAME" "TABLESPACE_NAME", "from$_subquery$_003"."BLOCK_SIZE" "BLOCK_SIZE", "from$_subquery$_003"."USER_ID" "USER_ID" FROM ((SELECT "TS"."TABLESPACE_NAME" "TABLESPACE_NAME", "TS"."BLOCK_SIZE" "BLOCK_SIZE", "U"."USER_ID" "USER_ID" FROM "DEMO"."T_TABLESPACES" "TS", "DEMO"."T_USERS" "U" WHERE "TS"."TABLESPACE_NAME" = "U"."DEFAULT_TABLESPACE"(+) AND "U"."USER_ID"(+) > TO_NUMBER(:B1) AND "TS"."MAX_EXTENTS" < TO_NUMBER(CASE WHEN "U"."DEFAULT_TABLESPACE"(+) IS NOT NULL THEN :B2 ELSE :B3 END)) UNION ALL (SELECT NULL, NULL, "U"."USER_ID" "USER_ID" FROM (SELECT /*+ UNNEST */ "TS"."TABLESPACE_NAME" "ITEM_1", :B4 "ITEM_2" FROM "DEMO"."T_TABLESPACES" "TS" WHERE "TS"."MAX_EXTENTS" < TO_NUMBER(:B5)) "VW_SQ_1", "DEMO"."T_USERS" "U" WHERE "VW_SQ_1"."ITEM_1" = "U"."DEFAULT_TABLESPACE" AND "U"."USER_ID" > TO_NUMBER("VW_SQ_1"."ITEM_2"))) "from$_subquery$_003" 3.1.29　左（右）外关联转换为侧视图 Oracle编译器在解析左（右）外关联时，都会将外关联转换为侧视图（Lateral View）。这是一项启发式查询转换。侧视图也是属于内联视图的一种，它和普通内联视图的区别在于：侧视图中的表可以与主查询中的表建立关联关系。 HELLODBA.COM>exec sql_explain('select ts.tablespace_name, ts.block_size, u.user_id from t_tablespaces ts left outer join t_users u on ts.tablespace_name=u.default_tablespace and ts.block_size=8192', 'TYPICAL OUTLINE'); Plan hash value: 38288728 -------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 15 | 345 | 296 (0)| 00:00:02 | | 1 | NESTED LOOPS OUTER | | 15 | 345 | 296 (0)| 00:00:02 | | 2 | TABLE ACCESS FULL | T_TABLESPACES | 15 | 150 | 11 (0)| 00:00:01 | | 3 | VIEW | | 1 | 13 | 19 (0)| 00:00:01 | |* 4 | FILTER | | | | | | |* 5 | TABLE ACCESS FULL| T_USERS | 1 | 11 | 19 (0)| 00:00:01 | -------------------------------------------------------------------------------------- Outline Data ------------- /*+ BEGIN_OUTLINE_DATA FULL(@"SEL$1" "U"@"SEL$1") USE_NL(@"SEL$64EAE176" "from$_subquery$_004"@"SEL$2") LEADING(@"SEL$64EAE176" "TS"@"SEL$2" "from$_subquery$_004"@"SEL$2") NO_ACCESS(@"SEL$64EAE176" "from$_subquery$_004"@"SEL$2") FULL(@"SEL$64EAE176" "TS"@"SEL$2") OUTLINE(@"SEL$2") OUTLINE(@"SEL$3") MERGE(@"SEL$2") OUTLINE_LEAF(@"SEL$64EAE176") OUTLINE_LEAF(@"SEL$1") OPTIMIZER_FEATURES_ENABLE('10.2.0.4') IGNORE_OPTIM_EMBEDDED_HINTS END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 4 - filter("TS"."BLOCK_SIZE"=8192) 5 - filter("TS"."TABLESPACE_NAME"="U"."DEFAULT_TABLESPACE") 上例中的语句经过转换后，变成了以下形式： SELECT "TS"."TABLESPACE_NAME" "TABLESPACE_NAME", "TS"."BLOCK_SIZE" "BLOCK_SIZE", "from$_subquery$_004"."USER_ID" "USER_ID" FROM "DEMO"."T_TABLESPACES" "TS", LATERAL((SELECT "U"."DEFAULT_TABLESPACE" "DEFAULT_TABLESPACE", "U"."USER_ID" "USER_ID" FROM "DEMO"."T_USERS" "U" WHERE "TS"."TABLESPACE_NAME" = "U"."DEFAULT_TABLESPACE" AND "TS"."BLOCK_SIZE" = 8192))(+) "from$_subquery$_004"; 提示：如果要在语句中直接使用侧视图，需要使用函数LATERAL()[(+)]，同时要开启22829事件。 HELLODBA.COM>alter session set events '22829 trace name context off'; Session altered. HELLODBA.COM>set autot trace exp HELLODBA.COM>SELECT "TS"."TABLESPACE_NAME" "TABLESPACE_NAME", 2 "TS"."BLOCK_SIZE" "BLOCK_SIZE", 3 "from$_subquery$_004"."USER_ID" "USER_ID" 4 FROM "DEMO"."T_TABLESPACES" "TS", 5 LATERAL((SELECT "U"."DEFAULT_TABLESPACE" "DEFAULT_TABLESPACE", 6 "U"."USER_ID" "USER_ID" 7 FROM "DEMO"."T_USERS" "U" 8 WHERE "TS"."TABLESPACE_NAME" = "U"."DEFAULT_TABLESPACE" 9 AND "TS"."BLOCK_SIZE" = 8192))(+) "from$_subquery$_004"; Execution Plan ---------------------------------------------------------- Plan hash value: 38288728 -------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 15 | 345 | 32 (0)| 00:00:33 | | 1 | NESTED LOOPS OUTER | | 15 | 345 | 32 (0)| 00:00:33 | | 2 | TABLE ACCESS FULL | T_TABLESPACES | 15 | 150 | 2 (0)| 00:00:03 | | 3 | VIEW | | 1 | 13 | 2 (0)| 00:00:03 | |* 4 | FILTER | | | | | | |* 5 | TABLE ACCESS FULL| T_USERS | 1 | 9 | 2 (0)| 00:00:03 | -------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - filter("TS"."BLOCK_SIZE"=8192) 5 - filter("TS"."TABLESPACE_NAME"="U"."DEFAULT_TABLESPACE") 需要注意的是，作为内联视图的一种，侧视图也可以被合并，以下示例就是外关联转换为侧视图后被合并的情况。 HELLODBA.COM>exec sql_explain('select ts.tablespace_name, ts.block_size, u.user_id from t_tablespaces ts left outer join t_users u on ts.tablespace_name=u.default_tablespace where ts.block_ size=8192', 'TYPICAL OUTLINE'); Plan hash value: 443123601 ------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 43 | 903 | 22 (5)| 00:00:01 | | 1 | MERGE JOIN OUTER | | 43 | 903 | 22 (5)| 00:00:01 | |* 2 | TABLE ACCESS BY INDEX ROWID| T_TABLESPACES | 15 | 150 | 2 (0)| 00:00:01 | | 3 | INDEX FULL SCAN | T_TABLESPACE_PK | 15 | | 1 (0)| 00:00:01 | |* 4 | SORT JOIN | | 43 | 473 | 20 (5)| 00:00:01 | | 5 | TABLE ACCESS FULL | T_USERS | 43 | 473 | 19 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------ Outline Data ------------- /*+ BEGIN_OUTLINE_DATA USE_MERGE(@"SEL$9E43CB6E" "U"@"SEL$1") LEADING(@"SEL$9E43CB6E" "TS"@"SEL$2" "U"@"SEL$1") FULL(@"SEL$9E43CB6E" "U"@"SEL$1") INDEX(@"SEL$9E43CB6E" "TS"@"SEL$2" ("T_TABLESPACES"."TABLESPACE_NAME")) OUTLINE(@"SEL$1") OUTLINE(@"SEL$2") MERGE(@"SEL$1") OUTLINE(@"SEL$58A6D7F6") OUTLINE(@"SEL$3") MERGE(@"SEL$58A6D7F6") OUTLINE_LEAF(@"SEL$9E43CB6E") OPTIMIZER_FEATURES_ENABLE('10.2.0.4') IGNORE_OPTIM_EMBEDDED_HINTS END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("TS"."BLOCK_SIZE"=8192) 4 - access("TS"."TABLESPACE_NAME"="U"."DEFAULT_TABLESPACE"(+)) filter("TS"."TABLESPACE_NAME"="U"."DEFAULT_TABLESPACE"(+))

专利

最新回复(0)

《Oracle高性能SQL引擎剖析：SQL优化与调优机制详解》一3.1 启发式查询转换

3.1 启发式查询转换

3.1.1 简单视图合并

专利

《Oracle高性能SQL引擎剖析：SQL优化与调优机制详解》一3.1　启发式查询转换

3.1　启发式查询转换

3.1.1　简单视图合并