查询转换(Query Transformation)，又称为逻辑优化(Logical Optimization)，也称为软优化，即查询转换器在逻辑上对语句做一些语义等价转换，从而能使优化器生成效率更高的执行计划。
语句在被提交到Oracle后，解析器(Parser)会对SQL语句的语法、语义进行分析，并将查询中的视图展开、划分为小的查询块(Query Block)。它们是嵌套或者相互关联的，而查询形式则决定了它们之间是如何关联的。这些查询块被传送给了查询转换器后，查询转换器会在不影响逻辑结果的前提下，决定如果改变查询形式(从而改变查询块之间的关系)，是否能生成更好的执行计划。
查询转换器依据特定的方式决定是否对查询块进行转换。按照其所依赖的方式，转换技术可以分为两类：启发式查询转换(Heuristic Query Transformation)，又称为基于规则的查询转换(Rule Based Query Transformation)；基于代价的查询转换(Cost Based Query Transformation，CBQT)。
启发式查询转换是基于一套规则对查询进行转换，一旦满足规则所定义的条件，则对语句进行相应的转换，部分启发式转换技术在RBO时代就已经被引入了。
基于代价的查询转换是否对语句进行转换则取决于语义等价语句之间的代价对比，即采用代价最小的一种。
提示：大多数基于代价的查询转换都可以从执行计划的概要数据中找到线索；而启发式查询转换则需要从优化器跟踪(10053)信息中查找线索，并且许多跟踪记录仅能从11g的跟踪信息中发现。
不过，并不是每一项转换技术都被严格划分在某一类下，某些技术，例如子查询反嵌套，既包含了启发式查询转换，又存在基于代价的查询转换。
Oracle主要采用了以上两种转换技术，我们分别介绍两种转换技术，对于同时采用了两种转换规则的情况，我们会再做说明。
在本章中，我们将会了解到以下内容：
Oracle的逻辑优化技术中，存在哪些启发式查询转换技术，以及它们的具体含义和示例；
Oracle的逻辑优化技术中，存在哪些基于代价的查询转换技术，以及它们的具体含义和示例。
一.1 分类
1、子查询展开
SINGLE-ROW(=，,<=,>=,<>)，EXISTS，NOT EXISTS，IN，NOT IN，ANY，ALL
不能做子查询展开的通常会在SQL执行计划最后一步才执行，一般是FILTER类型计划，效率很差
IN ，EXISTS，=ANY可转换为半连接(SEMI JOIN)
NOT IN，NOT EXISTS，<>ALL可转换为反连接 (ANTI JOIN)
展开2条件：展开后语义完全等价，内嵌视图的子查询必须COST低于原SQL才展开
2、视图合并
简单视图合并(始终合并)
外连接视图合并(作为外连接的驱动表 非驱动表但视图定义中只包含一个表)
复杂视图合并(COST低于原SQL才合并)
3、星型转换 针对各纬度表的限制条件，等价改写子查询附加到事实表 通过位图索引间的操作提高效率
4、连接谓词推入 把条件加入原视图定义SQL内部COST低于原SQL才推入
5、连接因式分解 提取公共部分，避免同一个表扫描2次(11.2新特性)
6、表扩展 分区表分区索引不可用时 单独处理此分区 相当于可用分区UNION ALL不可用分区(11.2新特性)COST低于原SQL
7、表移除 外键关联 外连接 可以去除不必要的表
8、IN OR等价OR改写时UNION ALL LNNVL()
IN-LIST ITERATOR IN所在列上必须有索引
IN-LIST EXPANSION/OR EXPANSION 可改写为UNION ALL COST低于原SQL才改写 (可以用到不同列各自索引、分区修剪等特性)
IN-LIST FILTER IN后是自查询不是常量集合并不做子查询展开
子查询展开/视图合并
一.2 启发式查询转换
所有的启发式查询转换都是基于一套优化器内建的规则。在查询转换阶段，转换器会逐个针对这些规则对查询进行检查，确定其是否满足转换规则，一旦满足，转换器就对其进行转换。
一.2.1 简单视图合并
我们知道，视图(View)的实质就是一条查询语句。在解析阶段，语句中的每个视图都会被展开至一个查询块中。如果未做视图合并，优化器则会单独分析每个视图，并为定义视图的查询语句生成一个视图子计划。然后再分析整个查询的其他部分，并生成执行计划。在这种情况下，由于视图的执行计划和整体执行计划不是同时统一做评估的，因此其最终计划可能不是最优的执行计划。
使用视图合并技术后，优化器不再单独为每个视图生成子计划，而是将视图的查询合并到整体查询中去，最终为合并和整体查询寻找到一个最优的执行计划。要将视图查询合并到主查询中去，优化器会用视图所依赖的表的名字替换视图名字，并将视图查询中的WHERE条件合并到主查询的WHERE条件中去。
根据子查询在主查询中的位置以及其与主查询中所引用的表的关系的不同，子查询分为以下几种：
l 标量子查询(Scalar Subquery)：出现在SELECT列表中的子查询称为标量子查询；
l 内联视图(Inline View)：出现在FROM子句中的视图称为内联视图；
l 嵌套子查询(Nested Subquery)：出现在WHERE子句中的子查询称为嵌套子查询；
l 互关联子查询(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表达式，则查询中的任何视图都不能被合并。
一.2.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。
一.2.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)与主查询合并，它们的对象可以直接关联。
一.2.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被消除。
一.2.1.4 我的示例
CREATE OR REPLACE VIEW VW_SVM_LHR AS SELECT * FROM SCOTT.EMP WHERE EMPNO<>7369;
SELECT * FROM VW_SVM_LHR V WHERE V.JOB='DBA';
SELECT /*+ NO_MERGE(V)*/ * FROM VW_SVM_LHR V WHERE V.JOB='DBA';
ALTER SESSION SET "_SIMPLE_VIEW_MERGING"=FALSE;
SELECT * FROM VW_SVM_LHR V WHERE V.JOB='DBA';
(一)简单视图合并
CREATE OR REPLACE VIEW VW_SVM_LHR AS SELECT * FROM SCOTT.EMP WHERE EMPNO<>7369;
SELECT * FROM VW_SVM_LHR V WHERE V.JOB='DBA';
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 114 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 3 | 114 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("JOB"='DBA' AND "EMPNO"<>7369)
LHR@orclasm > SELECT /*+ NO_MERGE(V)*/ * FROM VW_SVM_LHR V WHERE V.JOB='DBA';
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 45352968
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 261 | 3 (0)| 00:00:01 |
| 1 | VIEW | VW_SVM_LHR | 3 | 261 | 3 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| EMP | 3 | 114 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("JOB"='DBA' AND "EMPNO"<>7369)
LHR@orclasm > ALTER SESSION SET "_SIMPLE_VIEW_MERGING"=FALSE;
Session altered.
LHR@orclasm > SELECT * FROM VW_SVM_LHR V WHERE V.JOB='DBA';
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 45352968
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 261 | 3 (0)| 00:00:01 |
| 1 | VIEW | VW_SVM_LHR | 3 | 261 | 3 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| EMP | 3 | 114 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("JOB"='DBA' AND "EMPNO"<>7369)
一.2.2 子查询反嵌套、子查询展开、子查询解嵌套
在对存在嵌套子查询的复杂语句进行优化时，查询转换器会尝试将子查询展开，使得其中的表能与主查询中的表进行关联(Join)，从而获得更优的执行计划。部分子查询反嵌套(Subquery Unnesting)属于启发式查询转换，部分属于基于代价的转换，下面会做特别说明。
提示：提示UNNEST/NO_UNNEST可以控制是否进行反嵌套。在11G中，也可以由优化器参数_OPTIMIZER_UNNEST_ALL_SUBQUERIES控制。
一.2.2.1 其它
解嵌套子查询是指在对存在嵌套子查询的复杂语句进行优化时，查询转换器会尝试将子查询展开，使得其中的表能与主查询中的表关联，从而获得更优的执行计划。部分子查询反嵌套属于启发式查询转换，部分属于基于代价的转换。
系统中存在一个参数来控制解嵌套子查询—_unnest_subquery。参数_unnest_subquery在8i中的默认设置是false，从9i开始其默认设置是true。然而9i在非嵌套时不考虑成本。只有在10g中才开始考虑两种不同选择的成本，并选取成本较低的方式。当从8i升级到9i时，可能想阻塞某些查询的非嵌套。利用子查询中的no_unnest提示可以完成这一点。在8i和9i中，如果star_transformation_enabled=true，则非嵌套时被禁用(即使用了提示)。在11g环境下还受优化器参数_optimizer_unnest_all_subqueries控制。此外，提示UNNEST/NO_UNNEST可以控制是否进行解嵌套。
下面我们通过几个示例看看解嵌套子查询。
1)IN/EXISTS转换为SEMI JOIN：
SELECT * FROM SCOTT.EMP A WHERE EXISTS (SELECT 1 FROM SCOTT.DEPT B WHERE B.DEPTNO=A.DEPTNO);
/*示例中的子查询引用表DEPT，最终转换为两个表的哈希半连接。也就是说，exists子句中的子查询被展开，其中的对象与主查询中的对象直接进行半关联操作*/
// IN的情况类似，如下：
2)IN/EXISTS转换为ANTI JOIN：
/*优化器将NOT EXISTS后的子查询做解嵌套，然后选择了哈希的反连接。这种转换属于基于代价的查询转换。*/
//下面看看NOT IN的情况
/*和NOT EXISTS类似，也选择了哈希连接，只不过是HASH JOIN ANTI NA。这里的NA，实际表示Null-Aware的意思，在11g及以后的版本中，Oracle增加了对空值敏感的反关联的支持*/
3)关联子查询的解嵌套
在对于关联子查询的解嵌套过程中，会将子查询构造出一个内联视图，并将内联视图与主查询的表进行关联。这个操作可以通过参数_unnest_subquery来控制。这种转换属于启发式查询转换。
/*在ID=2的步骤中生成了内联视图，然后跟外部表进行的哈希连接。下面尝试修改参数，看优化器如何处理*/
//这里转换成了嵌套循环的一种特列FILTER
子查询解嵌套是指优化器将子查询展开，和外部的查询进行关联、合并，从而得到更优的执行计划。可以通过UNNEST/NO_UNNEST提示控制是否进行解嵌套。采用这种技术通常可以提高执行效率，原因是如果不解嵌套，子查询往往是最后执行的，作为FILTER条件来过滤外部查询；而一旦展开，优化器就可以选择表关联等更高效的执行方式，以提高效率。下面通过几个示例说明各种解嵌套的形式。
先看第一个示例：
SQL> create table t_tables as select * from dba_tables;
Table created.
SQL> 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);
执行计划如下：
在这个示例中，对EXISTS的子查询进行了解嵌套，然后选择了半连接(SEMI JOIN)的关联方式。
再来看一个示例。
SQL> 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);
执行计划如下：
在这个示例中，对NOT EXISTS的子查询进行了解嵌套，然后选择了反连接(ANTI JOIN)的关联方式。
一.2.2.2 IN/EXISTS转换为SEMI JOIN
转换器将IN或EXISTS子句中的子查询展开(反嵌套)，使得优化器能选择半关联(SEMI-JOIN)操作。这种转换属于启发式查询转换。示例如代码清单3-4所示。
代码清单3-4IN/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子句中的子查询被展开，其中的对象与主查询中的对象直接进行半关联操作。
一.2.2.3 NOT IN/NOT EXISTS转换为ANTI-JOIN
转换器将NOT IN或NOT EXISTS子句中的子查询展开(反嵌套)，使得优化器能选择反关联(ANTI-JOIN)操作。这种转换属于基于代价的查询转换。示例如代码清单3-5所示。
代码清单3-5NOT 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子句中的子查询被展开，其中的对象与主查询中的对象直接进行反关联操作。
一.2.2.4 NOT IN/NOT EXISTS转换为Null-Aware ANTI-JOIN(11g)
转换器将NOT IN/NOT EXISTS子句中的子查询展开(反嵌套)，使得优化器能选择对空值敏感的反关联(Null-Aware ANTI-JOIN)操作。这种转换属于启发式查询转换。
示例如代码清单3-6所示，其中字段T_OBJECTS.OBJECT_NAME允许为空(不存在非空约束)。
提示：对空值敏感的反关联操作能在关联数据时关注到空值的存在，从而避免使用代价高昂的操作(例如笛卡儿乘积关联)来获取逻辑结果。
代码清单3-6NOT 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子句中的子查询被展开，其中的对象与主查询中的对象直接进行对空值敏感的反关联操作。
一.2.2.5 互关联子查询的反嵌套
转换器对互关联子查询的反嵌套，会将子查询构造出一个内联视图，并将内联视图与主查询中的表进行关联。互关联子查询的反嵌套可以由参数_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")
在上例中，关联谓词中存在子查询，该子查询被展开，其中的对象与主查询中的对象直接进行关联操作。
一.2.2.6 含有标量子查询的反嵌套
这种转换属于启发式查询转换。示例如代码清单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直接与主查询中的对象进行关联。
一.2.2.7 我的示例
2)IN和EXISTS转换为半连接(SEMI JOIN)：
CREATE TABLE EMP_LHR AS SELECT * FROM SCOTT.EMP;
CREATE TABLE DEPT_LHR AS SELECT * FROM SCOTT.DEPT;
SELECT * FROM EMP_LHR A WHERE EXISTS (SELECT 1 FROM DEPT_LHR B WHERE B.DEPTNO=A.DEPTNO);
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 1400 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN SEMI | | 14 | 1400 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP_LHR | 14 | 1218 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| DEPT_LHR | 4 | 52 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------
子查询引用表DEPT，最终转换为两个表的哈希半连接。也就是说，EXISTS子句中的子查询被展开，其中的对象与主查询中的对象直接进行半关联操作。IN的情况类似，如下：
SELECT * FROM EMP_LHR A WHERE A.DEPTNO IN (SELECT B.DEPTNO FROM DEPT_LHR B);
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 1400 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN SEMI | | 14 | 1400 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP_LHR | 14 | 1218 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| DEPT_LHR | 4 | 52 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------
2)NOT IN和NOT EXISTS转换为反连接(ANTI JOIN)：
SELECT * FROM EMP_LHR A WHERE NOT EXISTS (SELECT 1 FROM DEPT_LHR B WHERE B.DEPTNO=A.DEPTNO);
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 1400 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN ANTI | | 14 | 1400 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP_LHR | 14 | 1218 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| DEPT_LHR | 4 | 52 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------
优化器将NOT EXISTS后的子查询做解嵌套，然后选择了哈希的反连接。这种转换属于基于代价的查询转换。下面看看NOT IN的情况：
SELECT * FROM EMP_LHR A WHERE A.DEPTNO NOT IN (SELECT B.DEPTNO FROM DEPT_LHR B);
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 1400 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN ANTI NA | | 14 | 1400 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP_LHR | 14 | 1218 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| DEPT_LHR | 4 | 52 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------
和NOT EXISTS类似，也选择了哈希连接，只不过是HASH JOIN ANTI NA。这里的NA，实际表示Null-Aware的意思，在11g及以后的版本中，Oracle增加了对空值敏感的反关联的支持。
一.2.3 子查询推进
子查询推进(Push Subquery)是一项对未能合并或者反嵌套的子查询优化的补充优化技术。通常情况下，未能合并或者反嵌套的子查询的子计划会被放置在整个查询计划的最后步骤执行，而子查询推进使得子查询能够提前被评估，使之可以出现在整体执行计划的较早步骤，从而获得更优的执行计划。
提示：子查询推进是9.2被引入的技术，但是它的概要数据在10.2.0.5及以后版本才可见。它可以由提示PUSH_SUBQ/NO_PUSH_SUBQ控制。
代码清单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。
一.2.3.1 示例
子查询推进是一项对未能合并或者反嵌套的子查询优化的补充优化技术。这一技术是在9.2版本引入的。通常情况下，未能合并或者反嵌套的子查询的子计划会被放置在整个查询计划的最后执行，而子查询推进使得子查询能够提前被评估，使之可以出现在整体执行计划较早的步骤中，从而获得更优的执行计划。可以通过PUSH_SUBQ/NO_PUSH_SUBQ来控制。
//默认情况下，就是用子查询推进技术。
对比一下，我们看看强制不使用的情况
/*对比上面，对了一步FILTER。这里使用了嵌套循环，每一个EMP表的记录，都对应一次子查询的查询，获得MAX值*/
子查询推进(又称子查询推入)是指优化器将子查询提前进行评估，使得优化器可以更早地介入优化以获得更优质的执行计划。这个技术可以通过提示PUSH_SUBQ/NO_PUSH_SUBQ控制。下面通过一个示例看看结果。
SQL> create table t_users as select * from dba_users;
//表已创建
SQL> create index idx_user_created on t_users(created);
//索引已创建
SQL> create table t_objects as select * from dba_objects;
//表已创建
执行以下语句：
SQL> select /*+ no_push_subq(@inv)*/ /*hf1*/ *
from t_objects u
where created >
(select /*+ qb_name(inv)*/ max(created)
from t_users );
执行计划如下：
注：在这个语句中，我们通过提示强制不使用子查询推进技术。由执行计划可见，执行是按照T_OBJECTS和T_USRES进行的一个索引的嵌套循环。
使用子查询推进：
SQL> select /*hf2*/ *
from t_objects u
where created >
( select /*+ qb_name(inv)*/ max(created)
from t_users );
执行计划如下：
在这个示例中，Oracle使用了子查询推入技术，且可以在OutLine中看到PUSH_SUBQ字样。从执行计划可见，没有出现两表关联，提前处理了子查询，生成MAX CREATED，然后全表扫描T_OBJECTS进行条件过滤，显然这种方式效率更高。
一.2.3.2 我的示例
SET LINESIZE 9999
COL PLAN_TABLE_OUTPUT FORMAT A166
ALTER SESSION SET STATISTICS_LEVEL=ALL ;
SELECT * FROM SYS.TAB$ A WHERE A.ANALYZETIME > (SELECT MAX(B.ANALYZETIME-1) FROM SYS.IND$ B);
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ADVANCED ALLSTATS LAST PEEKED_BINDS'));
SELECT /*+ no_push_subq(@lhr_ps)*/ * FROM SYS.TAB$ A WHERE A.ANALYZETIME > (SELECT /*+qb_name(lhr_ps)*/ MAX(B.ANALYZETIME-1) FROM SYS.IND$ B);
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ADVANCED ALLSTATS LAST PEEKED_BINDS'));
一.2.4 子查询合并
子查询合并(Subquery Coalesce)是Oracle 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被查询转换器融合了，它们的查询对象被直接关联。
一.2.4.1 示例
子查询合并是指优化器不再单独为子查询生成执行计划，而是将子查询合并到主查询中，最终为合并后的结果生成一个最优的执行计划。可以通过参数_simple_view_merging或者提示MERGE/NO_MERGE来控制是否开启、关闭子查询合并。
根据子查询的复杂程度，子查询可分为简单子查询、复杂子查询。
所谓简单子查询，是指可以简单将子查询字段投影到外部的情况。对于这种情况，优化器采取的是启发式策略，即满足条件下就行合并。
复杂子查询是指存在分组行数的情况。针对这种情况，优化器采取的是基于代价的策略，最终是否转换取决于成本。当然还有一些子查询是无法进行合并的。
下面通过几个示例看一下。
//这种方式下，并没有进行子查询合并。
下面强制看看效果
/*这里可以看到，没有再生成内联视图，子查询被合并了。那为什么默认没有进行子查询合并呢？从成本可见，显然不合并的成本更低*/
在语义等价的前提下，如果多个子查询产生的结果集相同，则优化器可以使用这种技术将多个子查询合并为一个子查询。这样的好处在于减少多次扫描产生的开销。可以通过NO_COALESCE_SQ/COALESCE_SQ提示来控制。下面看个示例：
select /*+ qb_name(mn)*/ t.*
from t_tables t where exists
(select /*+ qb_name(sub1)*/ 1
from t_tablespaces ts
where t.tablespace_name=ts.tablespace_name and ts.block_size=8)
and exists
(select /*+ qb_name(sub2)*/ 1
from t_tablespaces ts
where t.tablespace_name=ts.tablespace_name);
执行计划如下：
在这个查询中，外部对T_TABLES表的查询要同时满足SUB1和SUB2两个子查询，而SUB1在语义上又是SUB2的子集，因此优化器将两个子查询进行了合并(只进行一次对T_TABLESPACES表的扫描)，然后与外部表T_TABLES进行半连接。
那么如果语义不等价又会怎么样呢？
select /*+ qb_name(mn)*/ t.*
from t_tables t
where exists
(select /*+ qb_name(sub1)*/ 1
from t_tablespaces ts
where t.tablespace_name=ts.tablespace_name and ts.block_size=8)
and exists
(select /*+ qb_name(sub2)*/ 1
from t_tablespaces ts
where t.tablespace_name=ts.tablespace_name and ts.block_size=16);
执行计划如下：
在这个查询语句中，外部查询要满足两个子查询—SUB1和SUB2，但两者条件不同，不能简单合并。因此在执行计划中，分别对两者进行了扫描(直观感觉就是对T_TABLESPACES进行了两次扫描)，然后再做关联查询。
一.2.4.2 我的示例
LHR@orclasm > SELECT * FROM EMP_LHR A WHERE EXISTS (SELECT 1 FROM DEPT_LHR B WHERE B.DEPTNO=A.DEPTNO AND B.DEPTNO=10) AND EXISTS (SELECT 1 FROM DEPT_LHR B WHERE B.DEPTNO=A.DEPTNO AND B.DEPTNO=20) ;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 3115025369
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 113 | 0 (0)| |
|* 1 | FILTER | | | | | |
|* 2 | HASH JOIN SEMI | | 1 | 113 | 10 (10)| 00:00:01 |
|* 3 | HASH JOIN SEMI | | 1 | 100 | 7 (15)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| EMP_LHR | 1 | 87 | 3 (0)| 00:00:01 |
|* 5 | TABLE ACCESS FULL| DEPT_LHR | 1 | 13 | 3 (0)| 00:00:01 |
|* 6 | TABLE ACCESS FULL | DEPT_LHR | 1 | 13 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(NULL IS NOT NULL)
2 - access("B"."DEPTNO"="A"."DEPTNO")
3 - access("B"."DEPTNO"="A"."DEPTNO")
4 - filter("A"."DEPTNO"=10 AND "A"."DEPTNO"=20)
5 - filter("B"."DEPTNO"=20 AND "B"."DEPTNO"=10)
6 - filter("B"."DEPTNO"=10 AND "B"."DEPTNO"=20)
在这个查询语句中，外部查询要满足两个子查询—SUB1和SUB2，但两者条件不同，不能简单合并。因此在执行计划中，分别对两者进行了扫描(直观感觉就是对DEPT_LHR进行了两次扫描)，然后再做关联查询。
LHR@orclasm > SELECT * FROM EMP_LHR A WHERE EXISTS (SELECT 1 FROM DEPT_LHR B WHERE B.DEPTNO=A.DEPTNO AND B.DEPTNO=10) AND EXISTS (SELECT 1 FROM DEPT_LHR B WHERE B.DEPTNO=A.DEPTNO) ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
Execution Plan
----------------------------------------------------------
Plan hash value: 3403691855
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 100 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN SEMI | | 1 | 100 | 7 (15)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| EMP_LHR | 3 | 261 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| DEPT_LHR | 1 | 13 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("B"."DEPTNO"="A"."DEPTNO")
2 - filter("A"."DEPTNO"=10)
3 - filter("B"."DEPTNO"=10)
在这个查询中，外部对EMP_LHR表的查询要同时满足SUB1和SUB2两个子查询，而SUB1在语义上又是SUB2的子集，因此优化器将两个子查询进行了合并(只进行一次对DEPT_LHR表的扫描)，然后与外部表EMP_LHR进行半连接。
一.2.5 旧关联谓词推入
一.2.5.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"被推入了视图的查询语句中，从而成为其子计划的过滤条件。
一.2.5.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"被推入了视图中，并成为联合查询视图中子查询的谓词。
一.2.6 简单过滤谓词推入
简单过滤谓词推入(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被推入了视图的查询语句中。
一.2.6.1 我的示例
CREATE OR REPLACE VIEW VW_FVM_LHR AS SELECT * FROM SCOTT.EMP WHERE EMPNO=7369;
SELECT * FROM (SELECT * FROM VW_SVM_LHR V) WV WHERE WV.JOB='DBA';
SELECT /*+NO_MERGE(WV)*/ * FROM (SELECT * FROM VW_SVM_LHR V) WV WHERE WV.JOB='DBA';
(一)过滤谓词推入
LHR@orclasm > SELECT * FROM (SELECT * FROM VW_SVM_LHR V) WV WHERE WV.JOB='DBA';
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 114 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 3 | 114 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("JOB"='DBA' AND "EMPNO"<>7369)
LHR@orclasm > SELECT /*+NO_MERGE(WV)*/ * FROM (SELECT * FROM VW_SVM_LHR V) WV WHERE WV.JOB='DBA';
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 2734967094
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 261 | 3 (0)| 00:00:01 |
| 1 | VIEW | | 3 | 261 | 3 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| EMP | 3 | 114 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("JOB"='DBA' AND "EMPNO"<>7369)
一.2.7 谓词迁移
谓词迁移(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当中。
一.2.8 “或”操作扩张--OR扩展
如果查询语句的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关系表达式的谓词条件被扩展成对表的两次访问，并将它们的结果集进行拼接。
一.2.8.1 我的示例
SELECT * FROM SCOTT.EMP A WHERE A.EMPNO =7369 OR A.ENAME='DBA';
SELECT /*+OR_EXPAND(A EMPNO)*/ * FROM SCOTT.EMP A WHERE A.EMPNO =7369 OR A.ENAME='DBA';
SELECT * FROM SCOTT.EMP A WHERE A.EMPNO =7369 OR A.EMPNO=1;
SELECT /*+OR_EXPAND(A EMPNO)*/ * FROM SCOTT.EMP A WHERE A.EMPNO =7369 OR A.EMPNO=1;
同一字段：
LHR@orclasm > SELECT * FROM SCOTT.EMP A WHERE A.EMPNO =7369 OR A.EMPNO=1;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
Execution Plan
----------------------------------------------------------
Plan hash value: 2355049923
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 87 | 2 (0)| 00:00:01 |
| 1 | INLIST ITERATOR | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 87 | 2 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("A"."EMPNO"=1 OR "A"."EMPNO"=7369)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
23 recursive calls
5 db block gets
3 consistent gets
0 physical reads
1628 redo size
1025 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
LHR@orclasm > SELECT /*+OR_EXPAND(A EMPNO)*/ * FROM SCOTT.EMP A WHERE A.EMPNO =7369 OR A.EMPNO=1;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
Execution Plan
----------------------------------------------------------
Plan hash value: 2259546459
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 174 | 2 (0)| 00:00:01 |
| 1 | CONCATENATION | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 87 | 1 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 1 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 87 | 1 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("A"."EMPNO"=1)
5 - access("A"."EMPNO"=7369)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
23 recursive calls
4 db block gets
3 consistent gets
0 physical reads
1560 redo size
1021 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
LHR@orclasm >
不同字段：
LHR@orclasm > SELECT * FROM SCOTT.EMP A WHERE A.EMPNO =7369 OR A.ENAME='DBA';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 87 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 87 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A"."EMPNO"=7369 OR "A"."ENAME"='DBA')
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
28 recursive calls
4 db block gets
16 consistent gets
0 physical reads
1544 redo size
1021 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
LHR@orclasm > SELECT /*+OR_EXPAND(A EMPNO)*/ * FROM SCOTT.EMP A WHERE A.EMPNO =7369 OR A.ENAME='DBA';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
Execution Plan
----------------------------------------------------------
Plan hash value: 2453891490
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 174 | 3 (0)| 00:00:01 |
| 1 | CONCATENATION | | | | | |
|* 2 | TABLE ACCESS FULL | EMP | 1 | 87 | 2 (0)| 00:00:01 |
|* 3 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 87 | 1 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("A"."ENAME"='DBA')
3 - filter(LNNVL("A"."ENAME"='DBA'))
4 - access("A"."EMPNO"=7369)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
31 recursive calls
4 db block gets
25 consistent gets
0 physical reads
1560 redo size
1021 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
一.2.9 物化视图查询重写
我们知道，视图是一个逻辑对象，其实质就是一条查询语句。而在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;
一.2.10 集合操作关联转变
当查询中存在集合操作(INTERSECT、MINUS)时，查询转换器可以将进行集合操作的子查询转变为关联(Join)查询，这样的转换就称为集合操作关联转变(Set Join Conversion，SJC)。
提示：尽管该项技术在10g时被引入，但目前为止(11.2.0.1)，在所有版本中其默认为关闭。可以通过隐含参数“_CONVERT_SET_TO_JOIN”和提示SET_TO_JOIN/NO_SET_TO_JOIN控制其是否启用。
一.2.10.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操作被消除，取而代之的是哈希关联操作。
一.2.10.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操作被消除，取而代之的是嵌套循环反关联操作。
一、 我的示例
SELECT * FROM SCOTT.EMP A WHERE A.EMPNO>7469
MINUS
SELECT * FROM SCOTT.EMP A WHERE A.EMPNO>7839;
ALTER SESSION SET "_CONVERT_SET_TO_JOIN"=TRUE;
SELECT * FROM SCOTT.EMP A WHERE A.EMPNO>7469
MINUS
SELECT * FROM SCOTT.EMP A WHERE A.EMPNO>7839;
LHR@orclasm > SELECT * FROM SCOTT.EMP A WHERE A.EMPNO>7469
2 MINUS
3 SELECT * FROM SCOTT.EMP A WHERE A.EMPNO>7839;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
8 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3686975449
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 13 | 1566 | 8 (63)| 00:00:01 |
| 1 | MINUS | | | | | |
| 2 | SORT UNIQUE | | 13 | 1131 | 4 (25)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| EMP | 13 | 1131 | 3 (0)| 00:00:01 |
| 4 | SORT UNIQUE | | 5 | 435 | 4 (25)| 00:00:01 |
|* 5 | TABLE ACCESS FULL| EMP | 5 | 435 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("A"."EMPNO">7469)
5 - filter("A"."EMPNO">7839)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
61 recursive calls
5 db block gets
34 consistent gets
0 physical reads
2536 redo size
1357 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
8 rows processed
LHR@orclasm >
LHR@orclasm > ALTER SESSION SET "_CONVERT_SET_TO_JOIN"=TRUE;
Session altered.
LHR@orclasm >
LHR@orclasm > SELECT * FROM SCOTT.EMP A WHERE A.EMPNO>7469
2 MINUS
3 SELECT * FROM SCOTT.EMP A WHERE A.EMPNO>7839;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
8 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3353202012
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 13 | 2262 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN ANTI | | 13 | 2262 | 7 (15)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| EMP | 13 | 1131 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| EMP | 5 | 435 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."EMPNO"="A"."EMPNO" AND
SYS_OP_MAP_NONNULL("A"."ENAME")=SYS_OP_MAP_NONNULL("A"."ENAME") AND
SYS_OP_MAP_NONNULL("A"."JOB")=SYS_OP_MAP_NONNULL("A"."JOB") AND
SYS_OP_MAP_NONNULL("A"."MGR")=SYS_OP_MAP_NONNULL("A"."MGR") AND
SYS_OP_MAP_NONNULL("A"."HIREDATE")=SYS_OP_MAP_NONNULL("A"."HIREDATE")
AND SYS_OP_MAP_NONNULL("A"."SAL")=SYS_OP_MAP_NONNULL("A"."SAL") AND
SYS_OP_MAP_NONNULL("A"."COMM")=SYS_OP_MAP_NONNULL("A"."COMM") AND
SYS_OP_MAP_NONNULL("A"."DEPTNO")=SYS_OP_MAP_NONNULL("A"."DEPTNO"))
2 - filter("A"."EMPNO">7469)
3 - filter("A"."EMPNO">7839)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
61 recursive calls
5 db block gets
34 consistent gets
0 physical reads
2552 redo size
1347 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
8 rows processed
一.2.11 由约束生成过滤谓词
当查询语句中谓词条件或关联条件的字段上存在约束(非空约束、检查约束)时，优化器会将约束内容考虑进来，决定是否由其生成新的谓词过滤条件。
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')
一.2.12 星形转换
所谓星形查询(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获得数据记录，并与维度表之间进行关联操作。
一.2.13 排序消除
排序消除(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
*/
(一)排序消除
LHR@orclasm > SELECT COUNT(1) FROM ( SELECT T.EMPNO FROM SCOTT.EMP T ORDER BY T.EMPNO);
COUNT(1)
----------
14
Execution Plan
----------------------------------------------------------
Plan hash value: 96606410
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FULL SCAN| IDX_FULL_EMP_LHR | 14 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Statistics
----------------------------------------------------------
47 recursive calls
5 db block gets
1 consistent gets
0 physical reads
2616 redo size
526 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
一.2.14 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操作来消除重复值。
一.2.14.1 我的示例
CREATE TABLE T_QC_20170613_LHR AS SELECT * FROM DBA_USERS;
SELECT DISTINCT T.USERNAME FROM T_QC_20170613_LHR T;
ALTER TABLE T_QC_20170613_LHR ADD PRIMARY KEY (USERNAME);
SELECT DISTINCT T.USERNAME FROM T_QC_20170613_LHR T;
(二)去重消除
CREATE TABLE T_QC_20170613_LHR AS SELECT * FROM DBA_USERS;
LHR@orclasm > SELECT DISTINCT T.USERNAME FROM T_QC_20170613_LHR T;
Execution Plan
----------------------------------------------------------
Plan hash value: 1708573004
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 58 | 986 | 4 (25)| 00:00:01 |
| 1 | HASH UNIQUE | | 58 | 986 | 4 (25)| 00:00:01 |
| 2 | TABLE ACCESS FULL| T_QC_20170613_LHR | 58 | 986 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
6 recursive calls
0 db block gets
9 consistent gets
1 physical reads
0 redo size
1710 bytes sent via SQL*Net to client
552 bytes received via SQL*Net from client
5 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
58 rows processed
ALTER TABLE T_QC_20170613_LHR ADD PRIMARY KEY (USERNAME);
LHR@orclasm > SELECT DISTINCT T.USERNAME FROM T_QC_20170613_LHR T;
Execution Plan
----------------------------------------------------------
Plan hash value: 884813832
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 58 | 986 | 1 (0)| 00:00:01 |
| 1 | INDEX FULL SCAN | SYS_C0089569 | 58 | 986 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
79 recursive calls
28 db block gets
83 consistent gets
0 physical reads
0 redo size
1710 bytes sent via SQL*Net to client
552 bytes received via SQL*Net from client
5 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
58 rows processed
一.2.15 表消除(关联消除)
表消除(Table Elimination)是查询转换器依据表之间的主外键关系、位图关联索引等信息，将复杂查询语句中不必要读取的表消除掉的一项优化技术。由于表消除出现在关联查询语句里面，因此也称为关联消除(Join Elimination，JE)。
提示：表消除可以由优化器参数“_OPTIMIZER_JOIN_ELIMINATION_ENABLED”和提示ELIMINATE_JOIN/NO_ELIMINATE_JOIN控制。
一.2.15.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的扫描。
(三)表消除
SELECT A.* FROM SCOTT.EMP A,SCOTT.DEPT B WHERE A.DEPTNO=B.DEPTNO;
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 1218 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 14 | 1218 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A"."DEPTNO" IS NOT NULL)
一.2.15.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的扫描。
一.2.15.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'," 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的再次扫描。
一.2.15.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的扫描。
一.2.16 公共子表达式消除
公共子表达式消除(Common Sub-expression 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 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>:C)
3 - access("U"."USERNAME"="T"."OWNER")
上述例子中，转换器通过将OR连接的两个谓词表达式合并，从而减少了关联操作。
公共子表达式消除无法从概要数据或执行计划中鉴别出来，但是从Oracle 11g的10053事件跟踪中可以发现公共子表达式消除的转换。
*************************
Common Subexpression elimination (CSE)
*************************
CSE: CSE performed on query block SEL$1 (#0).
一.2.16.1 示例
SELECT * FROM SCOTT.EMP A,SCOTT.DEPT B WHERE ( A.DEPTNO=B.DEPTNO AND A.EMPNO<=7521) OR ( A.DEPTNO=B.DEPTNO AND A.EMPNO>=7782 );
ALTER SESSION SET "_ELIMINATE_COMMON_SUBEXPR"=FALSE;
SELECT * FROM SCOTT.EMP A,SCOTT.DEPT B WHERE ( A.DEPTNO=B.DEPTNO AND A.EMPNO<=7521) OR ( A.DEPTNO=B.DEPTNO AND A.EMPNO>=7782 );
(一)公共子表达式消除(Common Sub-expression Elimination，CSE)
LHR@orclasm > SELECT * FROM SCOTT.EMP A,SCOTT.DEPT B WHERE ( A.DEPTNO=B.DEPTNO AND A.EMPNO<=7521) OR ( A.DEPTNO=B.DEPTNO AND A.EMPNO>=7782 );
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 1287 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN | | 11 | 1287 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPT | 4 | 120 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| EMP | 11 | 957 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."DEPTNO"="B"."DEPTNO")
3 - filter("A"."EMPNO"<=7521 OR "A"."EMPNO">=7782)
LHR@orclasm > ALTER SESSION SET "_ELIMINATE_COMMON_SUBEXPR"=FALSE;
Session altered.
LHR@orclasm > SELECT * FROM SCOTT.EMP A,SCOTT.DEPT B WHERE ( A.DEPTNO=B.DEPTNO AND A.EMPNO<=7521) OR ( A.DEPTNO=B.DEPTNO AND A.EMPNO>=7782 );
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 117 | 10 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 117 | 10 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPT | 4 | 120 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| EMP | 1 | 87 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("A"."DEPTNO"="B"."DEPTNO" AND "A"."EMPNO"<=7521 OR
"A"."DEPTNO"="B"."DEPTNO" AND "A"."EMPNO">=7782)
一.2.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.
注意，由于空值不会被计数，因此计数函数转变不会对允许空值的字段进行转换。
一.2.18 表达式和条件评估
表达式和条件评估(Evaluation of Expressions and Conditions)是一项启发式查询转换技术，它将一些表达式转换为更加高效、语义上等价的表达式。
一.2.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所替换。
LHR@orclasm > SELECT * FROM SCOTT.EMP A WHERE a.sal>=100+50;
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 1218 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 14 | 1218 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A"."SAL">=150)
一.2.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及之前版本中，在谓词信息中看不出这一转换，但执行计划相应的访问路径却是按照转换后得出的。
LHR@orclasm > SELECT * FROM SCOTT.EMP A WHERE A.ENAME LIKE 'lhr' ;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 87 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 87 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A"."ENAME"='lhr')
一.2.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)。
一.2.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")
LHR@orclasm > SELECT * FROM SCOTT.EMP A WHERE A.ENAME IN ('lhr','DBA') ;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 87 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 87 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A"."ENAME"='DBA' OR "A"."ENAME"='lhr')
一.2.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")
一.2.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"))
一.2.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子句进一步展开，转换成对空值敏感的反关联操作。
一.2.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))
LHR@orclasm > SELECT * FROM SCOTT.EMP A WHERE A.EMPNO BETWEEN 1 AND 2;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 169057108
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 87 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 87 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | PK_EMP | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."EMPNO">=1 AND "A"."EMPNO"<=2)
一.2.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表达式。
一.2.18.10 逻辑传递(传递闭包)
优化器可以根据谓词之间的逻辑传递关系，将某个谓词条件中的表达式或常量传递给另一个谓词条件。
HELLODBA.COM>exec sql_explain('select * from t_users where lock_date
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"<:a>
上述例子中，根据LOCK_DATE与EXPIRY_DATE之间的关系("LOCK_DATE"
一、 我的示例
LHR@orclasm > SELECT /*+FULL(A) FULL(B)*/ * FROM SCOTT.EMP A,SCOTT.DEPT B WHERE A.DEPTNO=B.DEPTNO AND B.DEPTNO=20;
Execution Plan
----------------------------------------------------------
Plan hash value: 4192419542
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 468 | 5 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 4 | 468 | 5 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| DEPT | 1 | 30 | 2 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| EMP | 5 | 435 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("B"."DEPTNO"=20)
3 - filter("A"."DEPTNO"=20)
一.2.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.
一.2.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"
我们还可以从跟踪信息中看到被转换后的语句。
一.2.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
...
一.2.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可以从语句中消除掉。
一.2.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相关的操作成为关联操作的子操作。
一.2.24 集合分组查询转换
集合分组查询转换(Grouping Set To Union)将集合分组查询转换为视图，使得它能和其他对象做UNION-ALL查询。
提示：这一技术也称为集合分组临时表转换(Grouping Set Temp Table Transform)。可以由提示USE_TTT_FOR_GSETS控制。
一.2.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"
一.2.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"
一.2.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")
一.2.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(: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"
一.2.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"(+))
一.2.30 外连接视图合并
外连接视图合并(Outer Join View Merging)是指针对那些使用了外连接，以及所带视图的视图定义SQL语句中不含distinct、group by等聚合函数的目标SQL的视图合并。这里“使用外连接”的含义是指外部查询的表和视图之间使用了外连接，或者该视图的视图定义SQL语句中使用了外连接。
外连接会给视图合并带来很多限制，很多在内连接情形下可以做的视图合并一旦换成了外连接就不能做了，因为Oracle能做视图合并的前提条件就是视图合并后的等价改写SQL一定要和原SQL在语义上是完全等价，但对于使用了外连接的目标SQL而言，在很多情况下这种语义上的完全等价性并不能得到保证。
关于外连接视图合并有一个很常用的限制，即当目标视图在和外部查询的表做外连接时，该目标视图可以做外连接视图合并的前提条件是，要么该视图被作为外连接的驱动表，要么该视图虽然被作为外连接的被驱动表但它的视图定义SQL语句中只包含一个表。
CREATE OR REPLACE VIEW VW_SVM_LHR AS SELECT * FROM SCOTT.EMP WHERE EMPNO<>7369;
SELECT * FROM VW_SVM_LHR V,SCOTT.DEPT T WHERE V.DEPTNO=T.DEPTNO(+);
SELECT /*+ FULL(T)*/ * FROM VW_SVM_LHR V,SCOTT.DEPT T WHERE V.DEPTNO(+)=T.DEPTNO;
CREATE OR REPLACE VIEW VW_SVM2_LHR AS SELECT /*+ FULL(A) FULL(B)*/ A.*,B.DNAME FROM SCOTT.EMP A,SCOTT.DEPT B WHERE A.DEPTNO=B.DEPTNO AND EMPNO<>7369;
SELECT /*+ FULL(T)*/ * FROM VW_SVM2_LHR V,SCOTT.DEPT T WHERE V.DEPTNO(+)=T.DEPTNO;
(二)外连接视图合并(Outer Join View Merging)
CREATE OR REPLACE VIEW VW_SVM_LHR AS SELECT * FROM SCOTT.EMP WHERE EMPNO<>7369;
SELECT * FROM VW_SVM_LHR V,SCOTT.DEPT T WHERE V.DEPTNO=T.DEPTNO(+);
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 13 | 754 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN OUTER | | 13 | 754 | 7 (15)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| EMP | 13 | 494 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| DEPT | 4 | 80 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("DEPTNO"="T"."DEPTNO"(+))
2 - filter("EMPNO"<>7369)
视图作为被驱动表：
SELECT /*+ FULL(T)*/ * FROM VW_SVM_LHR V,SCOTT.DEPT T WHERE V.DEPTNO(+)=T.DEPTNO;
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 13 | 754 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN OUTER | | 13 | 754 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPT | 4 | 80 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| EMP | 13 | 494 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("DEPTNO"(+)="T"."DEPTNO")
3 - filter("EMPNO"(+)<>7369)
视图含有2个表：
CREATE OR REPLACE VIEW VW_SVM2_LHR AS SELECT /*+ FULL(A) FULL(B)*/ A.*,B.DNAME FROM SCOTT.EMP A,SCOTT.DEPT B WHERE A.DEPTNO=B.DEPTNO AND EMPNO<>7369;
SELECT /*+ FULL(T)*/ * FROM VW_SVM2_LHR V,SCOTT.DEPT T WHERE V.DEPTNO(+)=T.DEPTNO;
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 13 | 1508 | 10 (10)| 00:00:01 |
|* 1 | HASH JOIN OUTER | | 13 | 1508 | 10 (10)| 00:00:01 |
| 2 | TABLE ACCESS FULL | DEPT | 4 | 80 | 3 (0)| 00:00:01 |
| 3 | VIEW | VW_SVM2_LHR | 13 | 1248 | 7 (15)| 00:00:01 |
|* 4 | HASH JOIN | | 13 | 663 | 7 (15)| 00:00:01 |
| 5 | TABLE ACCESS FULL| DEPT | 4 | 52 | 3 (0)| 00:00:01 |
|* 6 | TABLE ACCESS FULL| EMP | 13 | 494 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("V"."DEPTNO"(+)="T"."DEPTNO")
4 - access("A"."DEPTNO"="B"."DEPTNO")
6 - filter("EMPNO"<>7369)
可见，视图被保留了下来，单独执行。
一.3 基于代价的查询转换
在进行基于代价的查询转换时，转换器先确认查询是否满足转换条件。一旦满足，就会对各种可行的转换方式进行枚举，并对它们进行代价估算，找到代价最低的方式。由此可见，相对于启发式查询转换，基于代价的查询转换是一个相当消耗资源(CPU和内存)的过程。
提示：Oracle中有一个优化器参数“_OPTIMIZER_COST_BASED_TRANSFORMATION”，用它来控制是否进行基于代价的查询转换，以及如何进行基于代价的查询转换，从而限制其对资源的消耗。
一.3.1 复杂视图合并
查询转换器对含有DISTINCT、GROUP BY的视图进行的合并称为复杂视图合并(Complex View Merging，CVM)。
提示：要进行复杂视图合并，需要确保视图合并(参数_complex_view_merging控制)功能和复杂视图合并功能(由优化器参数_COMPLEX_VIEW_MERGING控制，默认为TRUE)都打开。
HELLODBA.COM>exec sql_explain('select * from t_users u, v_objects_sum o where u.username=o.owner and u.created>:A', 'TYPICAL OUTLINE');
Plan hash value: 1302554469
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 819 | 3 (34)| 00:00:04 |
| 1 | HASH GROUP BY | | 9 | 819 | 3 (34)| 00:00:04 |
| 2 | NESTED LOOPS | | 4497 | 399K| 2 (0)| 00:00:03 |
| 3 | TABLE ACCESS BY INDEX ROWID| T_USERS | 2 | 172 | 1 (0)| 00:00:02 |
|* 4 | INDEX RANGE SCAN | T_USERS_IDX1 | 2 | | 1 (0)| 00:00:02 |
| 5 | BITMAP CONVERSION TO ROWIDS| | 2163 | 10815 | 2 (0)| 00:00:03 |
|* 6 | BITMAP INDEX SINGLE VALUE | T_OBJECTS_IDX4 | | | | |
------------------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
USE_NL(@"SEL$F5BB74E1" "T_OBJECTS"@"SEL$2")
LEADING(@"SEL$F5BB74E1" "U"@"SEL$1" "T_OBJECTS"@"SEL$2")
BITMAP_TREE(@"SEL$F5BB74E1" "T_OBJECTS"@"SEL$2" AND(("T_OBJECTS"."OWNER")))
INDEX_RS_ASC(@"SEL$F5BB74E1" "U"@"SEL$1" ("T_USERS"."CREATED"))
OUTLINE(@"SEL$2")
OUTLINE(@"SEL$1")
MERGE(@"SEL$2")
OUTLINE_LEAF(@"SEL$F5BB74E1")
OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("U"."CREATED">:A)
6 - access("U"."USERNAME"="OWNER")
在上例中，V_OBJECTS_SUM是含有GROUP BY子句的视图，与主查询合并后，视图中的对象与主查询中的对象直接关联。
一.3.1.1 我的示例
CREATE OR REPLACE VIEW VW_CVM_LHR AS SELECT T.JOB,COUNT(*) COUNTS FROM SCOTT.EMP T GROUP BY T.JOB;
SELECT * FROM VW_CVM_LHR V,SCOTT.DEPT T WHERE V.JOB=T.DNAME AND V.JOB='DBA';
SELECT /*+MERGE(V)*/ * FROM VW_CVM_LHR V,SCOTT.DEPT T WHERE V.JOB=T.DNAME AND V.JOB='DBA';
(三)复杂视图合并(Complex View Merging)
LHR@orclasm > SELECT * FROM VW_CVM_LHR V,SCOTT.DEPT T WHERE V.JOB=T.DNAME AND V.JOB='DBA';
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 2922957592
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 39 | 7 (15)| 00:00:01 |
| 2 | VIEW | VW_CVM_LHR | 1 | 19 | 3 (0)| 00:00:01 |
| 3 | HASH GROUP BY | | 1 | 8 | 3 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| EMP | 3 | 24 | 3 (0)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | DEPT | 1 | 20 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("V"."JOB"="T"."DNAME")
4 - filter("T"."JOB"='DBA')
5 - filter("T"."DNAME"='DBA')
LHR@orclasm > SELECT /*+MERGE(V)*/ * FROM VW_CVM_LHR V,SCOTT.DEPT T WHERE V.JOB=T.DNAME AND V.JOB='DBA';
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 2006461124
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 28 | 8 (25)| 00:00:01 |
| 1 | HASH GROUP BY | | 1 | 28 | 8 (25)| 00:00:01 |
|* 2 | HASH JOIN | | 3 | 84 | 7 (15)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| DEPT | 1 | 20 | 3 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| EMP | 3 | 24 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T"."JOB"="T"."DNAME")
3 - filter("T"."DNAME"='DBA')
4 - filter("T"."JOB"='DBA')
一.3.2 关联谓词推入
连接谓词推入也叫关联谓词推入(Join Predicate Push-Down，JPPD)转换是基于代价的转换，如果没有特别说明，我们所说的关联谓词推入都是指新的关联谓词推入。
当解析器未合并视图(或者视图不能被合并)时，查询转换器还可以采用连接谓词推入对其进行转换，以生成最优的执行计划。所谓谓词推入，是当视图处在关联查询中时，关联谓词被推入视图中，使得优化器能获得更好的执行计划。
在Oracle 10gR2之前，关联谓词推入技术属于启发式查询转换，因此也称为旧关联谓词推入(Old Join Predicate Push-Down，OJPPD)；从Oracle 10gR2开始，关联谓词推入会考虑到代价因素，属于基于代价的查询转换。
提示：是否进行谓词推入，可以由优化器参数“_PUSH_JOIN_PREDICATE”控制，默认为TRUE，也可以由提示OLD_PUSH_PRED控制。要启用旧的谓词推入，需要关闭基于代价的查询转换。
连接谓词推入(Join Predicate Pushdown)是优化器处理带视图的目标SQL的另外一种优化手段，它是指虽然优化器还是会把该SQL中视图的定义SQL语句当作一个独立的处理单元来单独执行，但此时优化器会把原本处于该视图外部查询中和该视图之间的连接条件推入到该视图的定义SQL语句内部，这样做是为了能使用上该视图内部相关基表上的索引，进而能走出基于索引的嵌套循环连接。
和之前介绍的各种查询转换一样，连接谓词推入使优化器在选择目标SQL的执行计划时多出了走基于索引的嵌套循环连接这种选择，这就增加了走出更高效执行计划的可能性。
但是请注意，连接谓词推入所带来的基于索引的嵌套循环连接并不一定能走出更高效的执行计划，因为当做了连接谓词推入后，原目标SQL中的视图就和外部查询产生了关联，同时Oracle又必须将该视图的定义SQL语句当作一个独立的处理单元来单独执行，这也就意味着对于外部查询所在结果集中的每一条记录，上述视图的定义SQL语句都得单独执行一次，这样一旦外部查询所在结果集的Cardinality比较大的话，即便在执行上述视图的定义SQL语句时能用上索引，整个SQL的执行效率也不一定会比不做连接谓词推入时的哈希连接或排序合并连接高。所以，Oracle在做连接谓词推入时会考虑成本，只有当经过连接谓词推入后走嵌套循环连接的等价改写SQL的成本值小于原SQL的成本值时，Oracle才会对目标SQL做连接谓词推入。Oracle是否能做连接谓词推入与目标视图的类型、该视图与外部查询之间的连接类型以及连接方法有关。到目前为止，Oracle仅仅支持对如下类型的视图做连接谓词推入：
·视图定义SQL语句中包含UNION ALL/UNION、DISTINCT、GROUP BY的视图
·和外部查询之间的连接类型是外连接、反连接、半连接的视图
一.3.2.1 外关联的谓词推入
语句存在外关联匹配时，转换器可以将关联谓词条件推入视图的查询语句中，使之成为其子计划的访问谓词条件。
HELLODBA.COM>exec sql_explain('SELECT /*+ NO_MERGE(v) PUSH_PRED(v) */* FROM t_tables t, v_objects_sys v WHERE t.owner =v.owner(+) and t.table_name = v.object_name(+) AND t.tablespace_name =:A', 'TYPICAL OUTLINE');
Plan hash value: 980895126
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2033 | 567K| 558 (0)| 00:09:19 |
| 1 | NESTED LOOPS OUTER | | 2033 | 567K| 558 (0)| 00:09:19 |
|* 2 | TABLE ACCESS FULL | T_TABLES | 184 | 38272 | 6 (0)| 00:00:07 |
| 3 | VIEW PUSHED PREDICATE | V_OBJECTS_SYS | 1 | 78 | 3 (0)| 00:00:04 |
|* 4 | FILTER | | | | | |
| 5 | TABLE ACCESS BY INDEX ROWID| T_OBJECTS | 1 | 71 | 3 (0)| 00:00:04 |
|* 6 | INDEX SKIP SCAN | T_OBJECTS_IDX1 | 1 | | 2 (0)| 00:00:03 |
-------------------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
INDEX_SS(@"SEL$639F1A6F" "T_OBJECTS"@"SEL$2" ("T_OBJECTS"."STATUS" "T_OBJECTS"."OWNER"
"T_OBJECTS"."OBJECT_NAME"))
USE_NL(@"SEL$1" "V"@"SEL$1")
LEADING(@"SEL$1" "T"@"SEL$1" "V"@"SEL$1")
NO_ACCESS(@"SEL$1" "V"@"SEL$1")
FULL(@"SEL$1" "T"@"SEL$1")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$2")
OUTLINE_LEAF(@"SEL$1")
PUSH_PRED(@"SEL$1" "V"@"SEL$1" 3 2)
OUTLINE_LEAF(@"SEL$639F1A6F")
OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("T"."TABLESPACE_NAME"=:A)
4 - filter('SYS'="T"."OWNER")
6 - access("OWNER"="T"."OWNER" AND "OBJECT_NAME"="T"."TABLE_NAME")
filter("OBJECT_NAME"="T"."TABLE_NAME" AND "OWNER"="T"."OWNER" AND "OWNER"='SYS')
上述查询中，关联条件"OWNER"="T"."OWNER" AND "OBJECT_NAME"="T"."TABLE_NAME"被推入了视图的查询语句中，从而成为了其子计划的访问条件。
一.3.2.2 联合查询视图中关联查询的谓词推入
转换器将关联条件推入含有联合操作(UNION或者UNION-ALL)查询的视图中，并使之成为联合查询视图中两个子查询的谓词。
HELLODBA.COM>exec sql_explain('select * from (select /*+index(t2 t_tables_pk)*/t2.owner, t2.table_name
from t_tables t2 union all select /*+index(t1 t_objects_pk)*/t1.owner, t1.object_name
from t_objects t1) v, t_users t4 where v.owner=t4.username and t4.user_id =:a and v.table_name
like :b','TYPICAL OUTLINE');
Plan hash value: 316561174
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 61 | 7686 | 585 (0)| 00:09:46 |
| 1 | NESTED LOOPS | | 61 | 7686 | 585 (0)| 00:09:46 |
| 2 | TABLE ACCESS BY INDEX ROWID | T_USERS | 1 | 86 | 1 (0)| 00:00:02 |
|* 3 | INDEX UNIQUE SCAN | T_USERS_PK | 1 | | 1 (0)| 00:00:02 |
| 4 | VIEW | | 3 | 120 | 584 (0)| 00:09:45 |
| 5 | UNION ALL PUSHED PREDICATE | | | | | |
|* 6 | INDEX RANGE SCAN | T_TABLES_PK | 5 | 110 | 1 (0)| 00:00:02 |
|* 7 | TABLE ACCESS BY INDEX ROWID| T_OBJECTS | 108 | 3024 | 583 (0)| 00:09:44 |
| 8 | INDEX FULL SCAN | T_OBJECTS_PK | 47585 | | 60 (0)| 00:01:01 |
-----------------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
INDEX(@"SEL$639F1A6F" "T2"@"SEL$2" ("T_TABLES"."TABLE_NAME" "T_TABLES"."OWNER"))
INDEX(@"SEL$B01C6807" "T1"@"SEL$3" ("T_OBJECTS"."OBJECT_ID"))
USE_NL(@"SEL$1" "V"@"SEL$1")
LEADING(@"SEL$1" "T4"@"SEL$1" "V"@"SEL$1")
NO_ACCESS(@"SEL$1" "V"@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "T4"@"SEL$1" ("T_USERS"."USER_ID"))
OUTLINE(@"SEL$1")
OUTLINE(@"SET$1")
OUTLINE(@"SEL$3")
OUTLINE(@"SEL$2")
OUTLINE_LEAF(@"SEL$1")
PUSH_PRED(@"SEL$1" "V"@"SEL$1" 2)
OUTLINE_LEAF(@"SET$5715CE2E")
OUTLINE_LEAF(@"SEL$B01C6807")
OUTLINE_LEAF(@"SEL$639F1A6F")
OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T4"."USER_ID"=TO_NUMBER(:A))
6 - access("T2"."TABLE_NAME" LIKE :B AND "T2"."OWNER"="T4"."USERNAME")
filter("T2"."OWNER"="T4"."USERNAME" AND "T2"."TABLE_NAME" LIKE :B)
7 - filter("T1"."OWNER"="T4"."USERNAME" AND "T1"."OBJECT_NAME" LIKE :B)
上述查询中，关联条件V."OWNER"="T4"."USERNAME"被推入了视图中，并成为联合查询视图中子查询的谓词。
一.3.2.3 我的示例
CREATE OR REPLACE VIEW VW_JPPD_LHR AS SELECT T.EMPNO FROM SCOTT.EMP T;
SELECT /*+NO_MERGE(V)*/ * FROM SCOTT.EMP T,VW_JPPD_LHR V WHERE T.EMPNO=V.EMPNO(+) AND T.ENAME='DBA';
SELECT /*+NO_MERGE(V) NO_PUSH_PRED*/ * FROM SCOTT.EMP T,VW_JPPD_LHR V WHERE T.EMPNO=V.EMPNO(+) AND T.ENAME='DBA';
ALTER SESSION SET "_PUSH_JOIN_PREDICATE"=FALSE;
SELECT /*+NO_MERGE(V)*/ * FROM SCOTT.EMP T,VW_JPPD_LHR V WHERE T.EMPNO=V.EMPNO(+) AND T.ENAME='DBA';
(二)连接谓词推入
LHR@orclasm > SELECT /*+NO_MERGE(V)*/ * FROM SCOTT.EMP T,VW_JPPD_LHR V WHERE T.EMPNO=V.EMPNO(+) AND T.ENAME='DBA';
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 51 | 2 (0)| 00:00:01 |
| 1 | NESTED LOOPS OUTER | | 1 | 51 | 2 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 2 (0)| 00:00:01 |
|* 3 | INDEX SKIP SCAN | IDX_FULL_EMP_LHR | 1 | | 1 (0)| 00:00:01 |
| 4 | VIEW PUSHED PREDICATE | VW_JPPD_LHR | 1 | 13 | 0 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | PK_EMP | 1 | 4 | 0 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T"."ENAME"='DBA')
filter("T"."ENAME"='DBA')
5 - access("T"."EMPNO"="T"."EMPNO")
LHR@orclasm > SELECT /*+NO_MERGE(V) NO_PUSH_PRED(V)*/ * FROM SCOTT.EMP T,VW_JPPD_LHR V WHERE T.EMPNO=V.EMPNO(+) AND T.ENAME='DBA';
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 51 | 4 (25)| 00:00:01 |
|* 1 | HASH JOIN OUTER | | 1 | 51 | 4 (25)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 2 (0)| 00:00:01 |
|* 3 | INDEX SKIP SCAN | IDX_FULL_EMP_LHR | 1 | | 1 (0)| 00:00:01 |
| 4 | VIEW | VW_JPPD_LHR | 14 | 182 | 1 (0)| 00:00:01 |
| 5 | INDEX FULL SCAN | IDX_FULL_EMP_LHR | 14 | 56 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T"."EMPNO"="V"."EMPNO"(+))
3 - access("T"."ENAME"='DBA')
filter("T"."ENAME"='DBA')
LHR@orclasm > ALTER SESSION SET "_PUSH_JOIN_PREDICATE"=FALSE;
Session altered.
LHR@orclasm > SELECT /*+NO_MERGE(V)*/ * FROM SCOTT.EMP T,VW_JPPD_LHR V WHERE T.EMPNO=V.EMPNO(+) AND T.ENAME='DBA';
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 51 | 4 (25)| 00:00:01 |
|* 1 | HASH JOIN OUTER | | 1 | 51 | 4 (25)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 2 (0)| 00:00:01 |
|* 3 | INDEX SKIP SCAN | IDX_FULL_EMP_LHR | 1 | | 1 (0)| 00:00:01 |
| 4 | VIEW | VW_JPPD_LHR | 14 | 182 | 1 (0)| 00:00:01 |
| 5 | INDEX FULL SCAN | IDX_FULL_EMP_LHR | 14 | 56 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T"."EMPNO"="V"."EMPNO"(+))
3 - access("T"."ENAME"='DBA')
filter("T"."ENAME"='DBA')
一.3.3 谓词提取
所谓谓词提取(Predicate Pull Up)，是指将视图(子查询)谓词中复杂的、代价高昂的过滤条件提取出来，放到主查询中进行过滤。
提示：谓词提取可以通过优化器参数_optimizer_filter_pred_pullup及提示pull_pred/no_pull_pred控制。
HELLODBA.COM>begin
2 sql_explain('
3 SELECT /*+qb_name(outv) */ owner, table_name, rownum
4 FROM
5 (
6 SELECT /*+qb_name(inv)*/t.owner, t.table_name, t.last_analyzed
7 FROM t_tables t
8 WHERE (t.last_analyzed) < (SELECT /*+qb_name(subq)*/ MAX(created) FROM t_objects o)
9 AND owner like ''A%''
10 ORDER BY 1
11 )v','TYPICAL OUTLINE PREDICATE');
12 end;
13 /
Plan hash value: 2416283887
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 86 | 5 (0)| 00:00:01 |
| 1 | COUNT | | | | | |
|* 2 | VIEW | | 2 | 86 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| T_TABLES | 2 | 64 | 3 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | T_TABLES_IDX1 | 2 | | 2 (0)| 00:00:01 |
| 5 | SORT AGGREGATE | | 1 | 8 | | |
| 6 | INDEX FULL SCAN (MIN/MAX) | T_OBJECTS_IDX5 | 47585 | 371K| 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
INDEX(@"SUBQ" "O"@"SUBQ" ("T_OBJECTS"."CREATED"))
INDEX_RS_ASC(@"SEL$AA570DA2" "T"@"INV" ("T_TABLES"."OWNER"))
NO_ACCESS(@"SEL$9FF7933E" "V"@"OUTV")
OUTLINE(@"OUTV")
OUTLINE(@"INV")
OUTLINE(@"SUBQ")
OUTLINE_LEAF(@"SEL$9FF7933E")
PULL_PRED(@"OUTV" "V" 1)
OUTLINE_LEAF(@"SEL$AA570DA2")
OUTLINE_LEAF(@"SUBQ")
OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("LAST_ANALYZED"< (SELECT /*+ QB_NAME ("SUBQ") */ MAX("CREATED") FROM
"T_OBJECTS" "O"))
4 - access("OWNER" LIKE 'A%')
filter("OWNER" LIKE 'A%')
从上述内容可见，视图中的复杂谓词条件被提取出来。
一.3.4 GROUP BY配置
GROUP BY配置(Placement)是一项用一个GROUP BY视图来替换复杂查询中的一个或多个表的优化技术。要进行GROUP BY配置的查询转换，需要满足以下条件：
1)外部主查询的FROM子句中最少有两张表；
2)外部主查询包含了GROUP BY的操作；
3)外部主查询中包含了一个对某张表的一个字段的聚集函数的查询。
提示：GROUP BY配置可以通过优化器参数“_optimizer_group_by_placement”或提示PLACE_GROUP_BY/NO_PLACE_GROUP_BY控制。
示例如下(以下示例在11.2.0.1中运行)：
HELLODBA.COM>begin
2 sql_explain('
3 SELECT /*+ qb_name(m) place_group_by(@m (t@m)) */
4 owner, max(maxbytes)
5 FROM t_tables t, t_datafiles d
6 WHERE t.tablespace_name = d.tablespace_name
7 GROUP BY t.owner ',
8 'TYPICAL OUTLINE');
9 end;
10 /
Plan hash value: 4181908607
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 18 | 792 | 33 (10)| 00:00:01 |
| 1 | HASH GROUP BY | | 18 | 792 | 33 (10)| 00:00:01 |
|* 2 | HASH JOIN | | 68 | 2992 | 32 (7)| 00:00:01 |
| 3 | TABLE ACCESS FULL | T_DATAFILES | 6 | 180 | 3 (0)| 00:00:01 |
| 4 | VIEW | VW_GBF_1 | 102 | 1428 | 29 (7)| 00:00:01 |
| 5 | HASH GROUP BY | | 102 | 1428 | 29 (7)| 00:00:01 |
| 6 | TABLE ACCESS FULL| T_TABLES | 2696 | 37744 | 27 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
USE_HASH_AGGREGATION(@"SEL$78EA785B")
FULL(@"SEL$78EA785B" "T"@"M")
USE_HASH_AGGREGATION(@"SEL$3D2A8CF5")
USE_HASH(@"SEL$3D2A8CF5" "VW_GBF_1"@"SEL$30379648")
LEADING(@"SEL$3D2A8CF5" "D"@"M" "VW_GBF_1"@"SEL$30379648")
NO_ACCESS(@"SEL$3D2A8CF5" "VW_GBF_1"@"SEL$30379648")
FULL(@"SEL$3D2A8CF5" "D"@"M")
OUTLINE(@"M")
OUTLINE(@"SEL$30379648")
PLACE_GROUP_BY(@"M" ( "T"@"M" ) 1)
OUTLINE_LEAF(@"SEL$3D2A8CF5")
OUTLINE_LEAF(@"SEL$78EA785B")
ALL_ROWS
DB_VERSION('11.2.0.1')
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ITEM_1"="D"."TABLESPACE_NAME")
在上例中，由于进行了GROUP BY配置转换，执行计划先对表T_TABLES进行了GROUP BY操作，然后再与表T_DATAFILES关联。
一.3.5 表扩张
在对分区表进行查询时，可能发生这样的情形：查询条件能够命中分区表上的本地分区索引，但此时本地分区索引的某个分区的索引发生异常，导致该分区上的索引无法被使用。在11gR2之前，这种情况下，该分区索引将彻底无法使用；而在11gR2之后，引入了表扩张(Table Expansion，TE)的查询转换技术，使得优化器可以针对索引状态对不同分区评估是否使用索引。
提示：表扩张可以通过优化器参数“_optimizer_table_expansion”或提示EXPAND_TABLE/NO_EXPAND_TABLE控制。
示例如下(以下示例在11.2.0.1中运行)：
HELLODBA.COM>alter index t_objects_list_idx3 modify partition part4 unusable;
Index altered.
HELLODBA.COM>exec sql_explain('select /*+EXPAND_TABLE(o)*/* from t_objects_list o','TYPICAL OUTLINE');
Plan hash value: 2631494874
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7077 | 1430K| 305 (1)| 00:00:04 | | |
| 1 | VIEW | VW_TE_1 | 7077 | 1430K| 305 (1)| 00:00:04 | | |
| 2 | UNION-ALL | | | | | | | |
| 3 | PARTITION LIST ALL | | 7076 | 691K| 300 (1)| 00:00:04 | 1 | 6 |
|* 4 | TABLE ACCESS FULL | T_OBJECTS_LIST | 7076 | 691K| 300 (1)| 00:00:04 | 1 | 6 |
| 5 | PARTITION LIST SINGLE| | 1 | 99 | 5 (0)| 00:00:01 |KEY(AP)|KEY(AP)|
| 6 | TABLE ACCESS FULL | T_OBJECTS_LIST | 1 | 99 | 5 (0)| 00:00:01 | 4 | 4 |
----------------------------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
FULL(@"SET$57F73675_2" "O"@"SEL$1")
FULL(@"SET$57F73675_1" "O"@"SEL$1")
NO_ACCESS(@"SEL$72AE2D8F" "VW_TE_1"@"SEL$72AE2D8F")
OUTLINE(@"SEL$1")
EXPAND_TABLE(@"SEL$1" "O"@"SEL$1")
OUTLINE(@"SET$57F73675")
OUTLINE_LEAF(@"SEL$72AE2D8F")
EXPAND_TABLE(@"SEL$1" "O"@"SEL$1")
OUTLINE_LEAF(@"SET$57F73675")
OUTLINE_LEAF(@"SET$57F73675_1")
OUTLINE_LEAF(@"SET$57F73675_2")
ALL_ROWS
DB_VERSION('11.2.0.1')
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("O"."OWNER" IS NULL OR "O"."OWNER"<>'SYSTEM')
上述例子中，表T_OBJECTS_LIST是一张列举分区表，其中PART4的列举值是'SYSTEM'。
HELLODBA.COM>insert into tmp_lob select to_lob(high_value) from dba_ind_partitions where index_name =
'T_OBJECTS_LIST_IDX3' and partition_name = 'PART4';
1 row created.
HELLODBA.COM>select to_char(b) from tmp_lob;
TO_CHAR(B)
-------------------------------------------------------------------------------------------------------
'SYSTEM'
查询转换器按照本地索引t_objects_list_idx3在不同分区上的状态将语句重写为一个UNION-ALL查询。其中，第一个子查询是访问所有分区，并过滤掉索引状态为UNUSABLE的分区("O"."OWNER" IS NULL OR "O"."OWNER"<>'SYSTEM')上的数据；第二个子查询是对单个分区(PARTITION LIST SINGLE)的访问，由查询计划中Pstart & Pstop可以知道是访问PART4。
一.3.6 关联因式分解--连接因式分解
关联因式分解(Join factorization，JF)是在Oracle 11gR2中引入的新的查询重写技术，它可以将UNION/UNION ALL查询中的子查询合并为一个内联视图。
提示：关联因式分解可以通过隐含参数“_OPTIMIZER_JOIN_FACTORIZATION”或提示FACTORIZE_JOIN/NO_FACTORIZE_JOIN控制。
它是优化器处理带UNION ALL的目标SQL的一种优化手段，它是指优化器在处理以UNION ALL连接的目标SQL的各个分支时，不再原封不动地分别重复执行每个分支，而是会把各个分支中公共的部分提出来作为一个单独的结果集，然后再和原UNION ALL中剩下的部分做表连接。
连接因式分解在Oracle 11gR2中才被引入，它的好处是显而易见的。如果不把UNION ALL中公共的部分提出来，则意味着这些公共部分中所包含的表会在UNION ALL的各个分支中被重复访问；而连接因式分解则能够在最大程度上避免这种重复访问现象的产生，当UNION ALL的公共部分所包含的表的数据量很大时，即便是只减少一次对大表的重复访问，那也意味着执行效率上的巨大提升。
代码清单3-16关联因式分解
HELLODBA.COM>begin
2 sql_explain('
3 select /*+ qb_name(sb1) */ u.username, u.created, o.object_name from t_objects o, t_users u
4 where o.owner=u.username and u.lock_date=:A
5 union all
6 select /*+ qb_name(sb2) */ u.username, u.created, o.object_name from t_objects o, t_users u
7 where o.owner=u.username and u.lock_date=:B',
8 'TYPICAL OUTLINE');
9 end;
10 /
Plan hash value: 3854854956
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 81521 | 5891K| 295 (1)| 00:00:04 |
|* 1 | HASH JOIN | | 81521 | 5891K| 295 (1)| 00:00:04 |
| 2 | VIEW | VW_JF_SET$A6672D85 | 26 | 1118 | 6 (0)| 00:00:01 |
| 3 | UNION-ALL | | | | | |
|* 4 | TABLE ACCESS FULL| T_USERS | 13 | 325 | 3 (0)| 00:00:01 |
|* 5 | TABLE ACCESS FULL| T_USERS | 13 | 325 | 3 (0)| 00:00:01 |
| 6 | TABLE ACCESS FULL | T_OBJECTS | 72115 | 2183K| 288 (1)| 00:00:04 |
-------------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
FULL(@"SEL$FDE4A245" "U"@"SB2")
FULL(@"SEL$3335C0C6" "U"@"SB1")
USE_HASH(@"SEL$BCE2A4E7" "O"@"SB1")
LEADING(@"SEL$BCE2A4E7" "VW_JF_SET$A6672D85"@"SEL$94FBCE2D" "O"@"SB1")
FULL(@"SEL$BCE2A4E7" "O"@"SB1")
NO_ACCESS(@"SEL$BCE2A4E7" "VW_JF_SET$A6672D85"@"SEL$94FBCE2D")
OUTLINE(@"SET$1")
OUTLINE(@"SB1")
OUTLINE(@"SB2")
OUTLINE(@"SEL$67A59F16")
OUTLINE(@"SEL$E9EF0288")
FACTORIZE_JOIN(@"SET$1"("O"@"SB1" "O"@"SB2"))
OUTLINE(@"SET$27448025")
OUTLINE(@"SEL$94FBCE2D")
OUTLINE(@"SEL$4ECEF7CB")
MERGE(@"SEL$67A59F16")
OUTLINE_LEAF(@"SEL$BCE2A4E7")
OUTLINE_LEAF(@"SET$A6672D85")
OUTLINE_LEAF(@"SEL$3335C0C6")
OUTLINE_LEAF(@"SEL$FDE4A245")
ALL_ROWS
DB_VERSION('11.2.0.1')
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("O"."OWNER"="ITEM_1")
4 - filter("U"."LOCK_DATE"=:A)
5 - filter("U"."LOCK_DATE"=:B)
上述例子中，两个子查询被合并为一个对T_USERS查询的UNION-ALL内联视图，系统自动命名为VW_JF_SET$A6672D85，然后再与表T_OBJECTS做关联。
一.3.6.1 我的示例
SELECT /*+FULL(A) FULL(B)*/ A.*,B.DNAME FROM SCOTT.EMP A,SCOTT.DEPT B WHERE A.DEPTNO=B.DEPTNO AND A.EMPNO=6
UNION ALL
SELECT /*+FULL(A) FULL(B)*/ A.*,B.DNAME FROM SCOTT.EMP A,SCOTT.DEPT B WHERE A.DEPTNO=B.DEPTNO AND A.EMPNO=7;
ALTER SESSION SET "_OPTIMIZER_JOIN_FACTORIZATION"=FALSE;
SELECT /*+FULL(A) FULL(B)*/ A.*,B.DNAME FROM SCOTT.EMP A,SCOTT.DEPT B WHERE A.DEPTNO=B.DEPTNO AND A.EMPNO=6
UNION ALL
SELECT /*+FULL(A) FULL(B)*/ A.*,B.DNAME FROM SCOTT.EMP A,SCOTT.DEPT B WHERE A.DEPTNO=B.DEPTNO AND A.EMPNO=7;
LHR@orclasm > SELECT /*+FULL(A) FULL(B)*/ A.*,B.DNAME FROM SCOTT.EMP A,SCOTT.DEPT B WHERE A.DEPTNO=B.DEPTNO AND A.EMPNO=6
2 UNION ALL
3 SELECT /*+FULL(A) FULL(B)*/ A.*,B.DNAME FROM SCOTT.EMP A,SCOTT.DEPT B WHERE A.DEPTNO=B.DEPTNO AND A.EMPNO=7;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 1245103347
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 244 | 8 (13)| 00:00:01 |
|* 1 | HASH JOIN | | 2 | 244 | 8 (13)| 00:00:01 |
| 2 | VIEW | VW_JF_SET$623BBB07 | 2 | 200 | 4 (0)| 00:00:01 |
| 3 | UNION-ALL | | | | | |
|* 4 | TABLE ACCESS FULL| EMP | 1 | 87 | 2 (0)| 00:00:01 |
|* 5 | TABLE ACCESS FULL| EMP | 1 | 87 | 2 (0)| 00:00:01 |
| 6 | TABLE ACCESS FULL | DEPT | 4 | 88 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("ITEM_1"="B"."DEPTNO")
4 - filter("A"."EMPNO"=6)
5 - filter("A"."EMPNO"=7)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
99 recursive calls
7 db block gets
32 consistent gets
0 physical reads
4536 redo size
866 bytes sent via SQL*Net to client
509 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
LHR@orclasm > ALTER SESSION SET "_OPTIMIZER_JOIN_FACTORIZATION"=FALSE;
Session altered.
LHR@orclasm > SELECT /*+FULL(A) FULL(B)*/ A.*,B.DNAME FROM SCOTT.EMP A,SCOTT.DEPT B WHERE A.DEPTNO=B.DEPTNO AND A.EMPNO=6
2 UNION ALL
3 SELECT /*+FULL(A) FULL(B)*/ A.*,B.DNAME FROM SCOTT.EMP A,SCOTT.DEPT B WHERE A.DEPTNO=B.DEPTNO AND A.EMPNO=7;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 2703228680
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 218 | 8 (50)| 00:00:01 |
| 1 | UNION-ALL | | | | | |
| 2 | NESTED LOOPS | | 1 | 109 | 4 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| EMP | 1 | 87 | 2 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| DEPT | 82 | 1804 | 2 (0)| 00:00:01 |
| 5 | NESTED LOOPS | | 1 | 109 | 4 (0)| 00:00:01 |
|* 6 | TABLE ACCESS FULL| EMP | 1 | 87 | 2 (0)| 00:00:01 |
|* 7 | TABLE ACCESS FULL| DEPT | 82 | 1804 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("A"."EMPNO"=6)
4 - filter("A"."DEPTNO"="B"."DEPTNO")
6 - filter("A"."EMPNO"=7)
7 - filter("A"."DEPTNO"="B"."DEPTNO")
Statistics
----------------------------------------------------------
93 recursive calls
7 db block gets
14 consistent gets
0 physical reads
4536 redo size
866 bytes sent via SQL*Net to client
509 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
一.3.7 DISTINCT配置
对关联(JOIN)查询结果取DISTINCT值时，DISTINCT配置(Distinct Placement，DP)能用一个含有DISTINCT的内联视图对查询进行重写。这项查询重写技术在Oracle 11gR2引入。
提示：DISTINCT配置可以由隐含参数“_OPTIMIZER_DISTINCT_PLACEMENT”或提示PLACE_DISTINCT/NO_PLACE_DISTINCT控制。
示例如下(以下示例在11.2.0.1中运行)：
HELLODBA.COM>exec sql_explain('select /*+full(u) full(t) place_distinct*/distinct t.tablespace_name, u.account_status from t_tables t, t_users u where t.owner=u.username', 'TYPICAL OUTLINE');
Plan hash value: 800024757
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12 | 480 | 36 (9)| 00:00:01 |
| 1 | HASH UNIQUE | | 12 | 480 | 36 (9)| 00:00:01 |
|* 2 | HASH JOIN | | 102 | 4080 | 35 (6)| 00:00:01 |
| 3 | TABLE ACCESS FULL | T_USERS | 31 | 806 | 3 (0)| 00:00:01 |
| 4 | VIEW | VW_DTP_1B35BA0F | 102 | 1428 | 31 (4)| 00:00:01 |
| 5 | HASH UNIQUE | | 102 | 1428 | 31 (4)| 00:00:01 |
| 6 | TABLE ACCESS FULL| T_TABLES | 2696 | 37744 | 30 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
USE_HASH_AGGREGATION(@"SEL$58BE4727")
FULL(@"SEL$58BE4727" "T"@"SEL$1")
USE_HASH_AGGREGATION(@"SEL$6B08CE13")
USE_HASH(@"SEL$6B08CE13" "VW_DTP_1B35BA0F"@"SEL$1B35BA0F")
LEADING(@"SEL$6B08CE13" "U"@"SEL$1" "VW_DTP_1B35BA0F"@"SEL$1B35BA0F")
NO_ACCESS(@"SEL$6B08CE13" "VW_DTP_1B35BA0F"@"SEL$1B35BA0F")
FULL(@"SEL$6B08CE13" "U"@"SEL$1")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$1B35BA0F")
PLACE_DISTINCT(@"SEL$1" "T"@"SEL$1")
OUTLINE_LEAF(@"SEL$6B08CE13")
OUTLINE_LEAF(@"SEL$58BE4727")
ALL_ROWS
DB_VERSION('11.2.0.1')
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ITEM_1"="U"."USERNAME")
上述例子中，语句被转换为以下查询：
SELECT /*+ PLACE_DISTINCT ) FULL ("U") */
DISTINCT "VW_DTP_1B35BA0F"."ITEM_2" "TABLESPACE_NAME",
"U"."ACCOUNT_STATUS" "ACCOUNT_STATUS"
FROM (SELECT DISTINCT "T"."OWNER" "ITEM_1", "T"."TABLESPACE_NAME" "ITEM_2"
FROM "DEMO"."T_TABLES" "T") "VW_DTP_1B35BA0F",
"DEMO"."T_USERS" "U"
WHERE "VW_DTP_1B35BA0F"."ITEM_1" = "U"."USERNAME"
一.3.7.1 我的示例
SELECT /*+FULL(A) FULL(B) */ DISTINCT A.ENAME,B.DNAME FROM SCOTT.EMP A,SCOTT.DEPT B WHERE A.DEPTNO=B.DEPTNO ;
SELECT /*+FULL(A) FULL(B) PLACE_DISTINCT*/ DISTINCT A.ENAME,B.DNAME FROM SCOTT.EMP A,SCOTT.DEPT B WHERE A.DEPTNO=B.DEPTNO ;
LHR@orclasm > SELECT /*+FULL(A) FULL(B)*/ DISTINCT A.ENAME,B.DNAME FROM SCOTT.EMP A,SCOTT.DEPT B WHERE A.DEPTNO=B.DEPTNO ;
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2962452962
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 588 | 8 (25)| 00:00:01 |
| 1 | HASH UNIQUE | | 14 | 588 | 8 (25)| 00:00:01 |
|* 2 | HASH JOIN | | 14 | 588 | 7 (15)| 00:00:01 |
| 3 | TABLE ACCESS FULL| DEPT | 4 | 88 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| EMP | 14 | 280 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."DEPTNO"="B"."DEPTNO")
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
46 recursive calls
6 db block gets
14 consistent gets
0 physical reads
2620 redo size
870 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
14 rows processed
LHR@orclasm > SELECT /*+FULL(A) FULL(B) PLACE_DISTINCT*/ DISTINCT A.ENAME,B.DNAME FROM SCOTT.EMP A,SCOTT.DEPT B WHERE A.DEPTNO=B.DEPTNO ;
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3633957927
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 588 | 9 (34)| 00:00:01 |
| 1 | HASH UNIQUE | | 14 | 588 | 9 (34)| 00:00:01 |
|* 2 | HASH JOIN | | 14 | 588 | 8 (25)| 00:00:01 |
| 3 | TABLE ACCESS FULL | DEPT | 4 | 88 | 3 (0)| 00:00:01 |
| 4 | VIEW | VW_DTP_377C5901 | 14 | 280 | 4 (25)| 00:00:01 |
| 5 | HASH UNIQUE | | 14 | 280 | 4 (25)| 00:00:01 |
| 6 | TABLE ACCESS FULL| EMP | 14 | 280 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ITEM_1"="B"."DEPTNO")
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
59 recursive calls
5 db block gets
48 consistent gets
0 physical reads
2552 redo size
873 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
14 rows processed
一.3.8 WITH子查询转换
该转换包括：创建临时表，将WITH子查询结果写入临时表，主查询中直接获取临时表中的数据。因此这一转换也称为WITH子查询实体化(Materialize)。该转换由参数“_with_subquery”控制。
提示：WITH子查询转换可以由参数“_with_subquery”或提示MATERIALIZE/INLINE控制。
HELLODBA.COM>exec sql_explain('with v as (select /*+ MATERIALIZE qb_name(wv) */* from t_objects o where object_id<:a select count from v outline>
Plan hash value: 2309780835
------------------------------------------------------------------
| Id | Operation | Name |
------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TEMP TABLE TRANSFORMATION | |
| 2 | LOAD AS SELECT | SYS_TEMP_0FD9D6601_F201F06C |
| 3 | TABLE ACCESS FULL | T_OBJECTS |
| 4 | SORT AGGREGATE | |
| 5 | VIEW | |
| 6 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6601_F201F06C |
------------------------------------------------------------------
上例中，WITH子查询的结果被写入了临时表SYS_TEMP_0FD9D6601_F201F06C中，主查询直接获取其数据。
一.4 Oracle CBO几种基本的查询转换详解
在执行计划的开发过程中，转换和选择有这个不同的任务；实际上，在一个查询进行完语法和权限检查后，首先发生通称为“查询转换”的步骤，这里会进行一系列查询块的转换，然后才是“优选”(优化器为了决定最终的执行计划而为不同的计划计算成本从而选择最终的执行计划)。
我们知道查询块是以SELECT关键字区分的，查询的书写方式决定了查询块之间的关系，各个查询块通常都是嵌在另一个查询块中或者以某种方式与其相联结；例如：
复制代码代码如下:
select * from employees where department_id in (select department_id from departments)
就是嵌套的查询块，不过它们的目的都是去探索如果改变查询写法会不会提供更好的查询计划。
这种查询转换的步骤对于执行用户可以说是完全透明的，要知道转换器可能会在不改变查询结果集的情况下完全改写你的SQL语句结构，因此我们有必要重新评估自己的查询语句的心理预期，尽管这种转换通常来说都是好事，为了获得更好更高效的执行计划。
我们现在来讨论一下几种基本的转换：
1.视图合并
2.子查询解嵌套
3.谓语前推
4.物化视图查询重写
一、视图合并
这种方式比较容易理解，它会将内嵌的视图展开成一个独立处理的查询块，或者将其与查询剩余部分合并成一个总的执行计划，转换后的语句基本上不包含视图了。
视图合并通常发生在当外部查询块的谓语包括：
1，能够在另一个查询块的索引中使用的列
2，能够在另一个查询块的分区截断中所使用的列
3，在一个联结视图能够限制返回行数的条件
在这种查询器的转换下，视图并不总会有自己的子查询计划，它会被预先分析并通常情况下与查询的其他部分合并以获得性能的提升，如下例。
复制代码代码如下:
SQL> set autotrace traceonly explain
-- 进行视图合并
SQL> select * from EMPLOYEES a,
2 (select DEPARTMENT_ID from EMPLOYEES) b_view
3 where a.DEPARTMENT_ID = b_view.DEPARTMENT_ID(+)
4 and a.SALARY > 3000;
Execution Plan
----------------------------------------------------------
Plan hash value: 1634680537
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3161 | 222K| 3 (0)| 00:00:01 |
| 1 | NESTED LOOPS OUTER| | 3161 | 222K| 3 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| EMPLOYEES | 103 | 7107 | 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 31 | 93 | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("A"."SALARY">3000)
3 - access("A"."DEPARTMENT_ID"="DEPARTMENT_ID"(+))
-- 使用NO_MERGE防止视图被重写
SQL> select * from EMPLOYEES a,
2 (select /*+ NO_MERGE */DEPARTMENT_ID from EMPLOYEES) b_view
3 where a.DEPARTMENT_ID = b_view.DEPARTMENT_ID(+)
4 and a.SALARY > 3000;
Execution Plan
----------------------------------------------------------
Plan hash value: 1526679670
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3161 | 253K| 7 (15)| 00:00:01 |
|* 1 | HASH JOIN RIGHT OUTER| | 3161 | 253K| 7 (15)| 00:00:01 |
| 2 | VIEW | | 107 | 1391 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | EMPLOYEES | 107 | 321 | 3 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | EMPLOYEES | 103 | 7107 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."DEPARTMENT_ID"="B_VIEW"."DEPARTMENT_ID"(+))
4 - filter("A"."SALARY">3000)
出于某些情况，视图合并会被禁止或限制，如果在一个查询块中使用了分析函数，聚合函数，，集合运算(如union，intersect，minux)，order by子句，以及rownum中的任何一种，这种情况都会发生；尽管如此，我们仍然可以使用/*+ MERGE(v) */提示来强制使用视图合并，不过前提一定要保证返回的结果集是一致的！！！如下例：
复制代码代码如下:
SQL> set autotrace on
-- 使用聚合函数avg导致视图合并失效
SQL> SELECT e1.last_name, e1.salary, v.avg_salary
2 FROM hr.employees e1,
3 (SELECT department_id, avg(salary) avg_salary
4 FROM hr.employees e2
5 GROUP BY department_id) v
6 WHERE e1.department_id = v.department_id AND e1.salary > v.avg_salary;
Execution Plan
----------------------------------------------------------
Plan hash value: 2695105989
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 17 | 697 | 8 (25)| 00:00:01 |
|* 1 | HASH JOIN | | 17 | 697 | 8 (25)| 00:00:01 |
| 2 | VIEW | | 11 | 286 | 4 (25)| 00:00:01 |
| 3 | HASH GROUP BY | | 11 | 77 | 4 (25)| 00:00:01 |
| 4 | TABLE ACCESS FULL| EMPLOYEES | 107 | 749 | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMPLOYEES | 107 | 1605 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("E1"."DEPARTMENT_ID"="V"."DEPARTMENT_ID")
filter("E1"."SALARY">"V"."AVG_SALARY")
--使用/*+ MERGE(v) */强制进行视图合并
SQL> SELECT /*+ MERGE(v) */ e1.last_name, e1.salary, v.avg_salary
2 FROM hr.employees e1,
3 (SELECT department_id, avg(salary) avg_salary
4 FROM hr.employees e2
5 GROUP BY department_id) v
6 WHERE e1.department_id = v.department_id AND e1.salary > v.avg_salary;
Execution Plan
----------------------------------------------------------
Plan hash value: 3553954154
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 165 | 5610 | 8 (25)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | HASH GROUP BY | | 165 | 5610 | 8 (25)| 00:00:01 |
|* 3 | HASH JOIN | | 3296 | 109K| 7 (15)| 00:00:01 |
| 4 | TABLE ACCESS FULL| EMPLOYEES | 107 | 2889 | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL| EMPLOYEES | 107 | 749 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------
二、子查询解嵌套
最典型的就是子查询转变为表连接了，它和视图合并的主要区别就在于它的子查询位于where子句，由转换器进行解嵌套的检测。
下面便是一个子查询==>表连接的例子：
复制代码代码如下:
SQL> select employee_id, last_name, salary, department_id
2 from hr.employees
3 where department_id in
4 (select department_id
5 from hr.departments where location_id > 1700);
Execution Plan
----------------------------------------------------------
Plan hash value: 432925905
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 34 | 884 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 34 | 884 | 4 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 4 | 28 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | DEPT_LOCATION_IX | 4 | | 1 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 10 | | 0 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID | EMPLOYEES | 10 | 190 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("LOCATION_ID">1700)
5 - access("DEPARTMENT_ID"="DEPARTMENT_ID")
-- 使用/*+ NO_UNNEST */强制为子查询单独生成执行计划
SQL> select employee_id, last_name, salary, department_id
2 from hr.employees
3 where department_id in
4 (select /*+ NO_UNNEST */department_id
5 from hr.departments where location_id > 1700);
Execution Plan
----------------------------------------------------------
Plan hash value: 4233807898
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 190 | 14 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL | EMPLOYEES | 107 | 2033 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 1 | 7 | 1 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | DEPT_ID_PK | 1 | | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "HR"."DEPARTMENTS"
"DEPARTMENTS" WHERE "DEPARTMENT_ID"=:B1 AND "LOCATION_ID">1700))
3 - filter("LOCATION_ID">1700)
4 - access("DEPARTMENT_ID"=:B1)
可以看到没有执行子查询解嵌套的查询只使用了FILTER来进行两张表的匹配，谓语信息第一步的查询也没有丝毫的改动，这便意味着对于EMPLOYEES表中返回的107行的每一行，都需要执行一次子查询。虽然在oracle中存在子查询缓存的优化，我们无法判断这两种计划的优劣，不过相比NESTED LOOPS，FILTER运算的劣势是很明显的。
如果包含相关子查询，解嵌套过程一般会将相关子查询转换成一个非嵌套视图，然后与主查询中的表x相联结，如：
复制代码代码如下:
SQL> select outer.employee_id, outer.last_name, outer.salary, outer.department_id
2 from hr.employees outer
3 where outer.salary >
4 (select avg(inner.salary)
5 from hr.employees inner
6 where inner.department_id = outer.department_id);
Execution Plan
----------------------------------------------------------
Plan hash value: 2167610409
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 17 | 765 | 8 (25)| 00:00:01 |
|* 1 | HASH JOIN | | 17 | 765 | 8 (25)| 00:00:01 |
| 2 | VIEW | VW_SQ_1 | 11 | 286 | 4 (25)| 00:00:01 |
| 3 | HASH GROUP BY | | 11 | 77 | 4 (25)| 00:00:01 |
| 4 | TABLE ACCESS FULL| EMPLOYEES | 107 | 749 | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMPLOYEES | 107 | 2033 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("ITEM_1"="OUTER"."DEPARTMENT_ID")
filter("OUTER"."SALARY">"AVG(INNER.SALARY)")
上面的查询是将子查询转换成视图在与主查询进行hash join，转换后的查询其实像这样：
复制代码代码如下:
SQL> select outer.employee_id, outer.last_name, outer.salary, outer.department_id
2 from hr.employees outer,
3 (select department_id,avg(salary) avg_sal from hr.employees group by department_id) inner
4 where inner.department_id = outer.department_id and outer.salary > inner.avg_sal;
其实这两个语句的执行计划也是一致
三、谓语前推
将谓词从内部查询块推进到一个不可合并的查询块中，这样可以使得谓词条件更早的被选择，更早的过滤掉不需要的数据行，提高效率，同样可以使用这种方式允许某些索引的使用。
复制代码代码如下:
-- 谓语前推示例
SQL> set autotrace traceonly explain
SQL> SELECT e1.last_name, e1.salary, v.avg_salary
2 FROM hr.employees e1,
3 (SELECT department_id, avg(salary) avg_salary
4 FROM hr.employees e2
5 GROUP BY department_id) v
6 WHERE e1.department_id = v.department_id
7 AND e1.salary > v.avg_salary
8 AND e1.department_id = 60;
Execution Plan
----------------------------------------------------------
Plan hash value: 3521487559
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 41 | 3 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 1 | 41 | 3 (0)| 00:00:01 |
| 3 | VIEW | | 1 | 26 | 2 (0)| 00:00:01 |
| 4 | HASH GROUP BY | | 1 | 7 | 2 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 5 | 35 | 2 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 5 | | 1 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 5 | | 0 (0)| 00:00:01 |
|* 8 | TABLE ACCESS BY INDEX ROWID | EMPLOYEES | 1 | 15 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("DEPARTMENT_ID"=60)
7 - access("E1"."DEPARTMENT_ID"=60)
8 - filter("E1"."SALARY">"V"."AVG_SALARY")
-- 不进行谓语前推
SQL> SELECT e1.last_name, e1.salary, v.avg_salary
2 FROM hr.employees e1,
3 (SELECT department_id, avg(salary) avg_salary
4 FROM hr.employees e2
5 WHERE rownum > 1 -- rownum等于同时使用了no_merge和no_push_pred提示，这会同时禁用视图合并和谓语前推
6 GROUP BY department_id) v
7 WHERE e1.department_id = v.department_id
8 AND e1.salary > v.avg_salary
9 AND e1.department_id = 60;
Execution Plan
----------------------------------------------------------
Plan hash value: 3834222907
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 123 | 7 (29)| 00:00:01 |
|* 1 | HASH JOIN | | 3 | 123 | 7 (29)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 5 | 75 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 5 | | 1 (0)| 00:00:01 |
|* 4 | VIEW | | 11 | 286 | 4 (25)| 00:00:01 |
| 5 | HASH GROUP BY | | 11 | 77 | 4 (25)| 00:00:01 |
| 6 | COUNT | | | | | |
|* 7 | FILTER | | | | | |
| 8 | TABLE ACCESS FULL | EMPLOYEES | 107 | 749 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("E1"."DEPARTMENT_ID"="V"."DEPARTMENT_ID")
filter("E1"."SALARY">"V"."AVG_SALARY")
3 - access("E1"."DEPARTMENT_ID"=60)
4 - filter("V"."DEPARTMENT_ID"=60)
7 - filter(ROWNUM>1)
比较上面的两个查询可以看到，在第一个查询中，DEPARTMENT_ID=60谓词被推进到视图v中执行了，这样就使得内部视图查询只需要获得部门号为60的平均薪水就可以了；而在第二个查询中则需要计算每个部门的平均薪水，然后在与外部查询联结的时候使用DEPARTMENT_ID=60条件过滤，相对而言这里为了等待应用谓词条件，查询做了更多的工作。
四、使用物化视图进行查询重写
当为物化视图开启查询重写功能时，CBO优化器会评估相应查询对基表与物化视图的访问成本，如果优化器认为该查询结果从物化视图中获得会更高效，那么就会其自动选择为物化视图来执行，否则则对基表生成查询计划。
还是来看栗子：
复制代码代码如下:
SQL> set autotrace traceonly explain
SQL> select DEPARTMENT_ID,count(EMPLOYEE_ID) from EMPLOYEES group by DEPARTMENT_ID;
Execution Plan
----------------------------------------------------------
Plan hash value: 1192169904
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 33 | 4 (25)| 00:00:01 |
| 1 | HASH GROUP BY | | 11 | 33 | 4 (25)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMPLOYEES | 107 | 321 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------
-- 创建物化视图日志
SQL> create materialized view log on EMPLOYEES with sequence,
2 rowid (EMPLOYEE_ID,DEPARTMENT_ID) including new values;
Materialized view log created.
-- 创建物化视图，并指定查询重写功能
SQL> create materialized view mv_t
2 build immediate refresh fast on commit
3 enable query rewrite as
4 select DEPARTMENT_ID,count(EMPLOYEE_ID) from EMPLOYEES group by DEPARTMENT_ID;
Materialized view created.
SQL> select DEPARTMENT_ID,count(EMPLOYEE_ID) from EMPLOYEES group by DEPARTMENT_ID;
Execution Plan
----------------------------------------------------------
Plan hash value: 1712400360
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12 | 312 | 3 (0)| 00:00:01 |
| 1 | MAT_VIEW REWRITE ACCESS FULL| MV_T | 12 | 312 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
可以看到在第二个查询中，虽然是指定的查询EMPLOYEES表，但是优化器自动选择了物化视图的执行路径，因为它判断出物化视图已经记载当前查询需要的结果集数据了，直接访问物化视图会获得更高的效率。
值得注意的是，这里的物化视图查询重写是自动发生的，同样也可以使用/*+ rewrite(mv_t) */提示的方式强制发生查询重写。
总结：
尽管优化器在用户透明的情况下改写了我们的查询结构，不过通常情况下这都是基于CBO优化模式下其判断较为高效的选择，这也是我们所期望的，同时为我们提供了一种学习方法，即在写SQL语句的过程中时刻考虑优化器的作用。
一.4.1 子查询展开(Subquery Unnesting)
子查询展开(Subquery Unnesting)是优化器处理带子查询的目标sql的一种优化手段，它是指优化器不再将目标sql中子查询当作一个独立的处理单元来单独执行，而是将该子查询转换为它自身和外部查询之间等价的表连接。这种等价连接转换要么是将子查询展开(即将该子查询中的表，视图从子查询中拿出来，然后和外部查询中的表，视图做表连接)，要么是不拆开但是会把该子查询转换为一个内嵌视图(Inline View)然后再和外部查询中的表，视图做表连接。Oracle会确保子查询展开所对应的等价连接转换的正确性，即转换后的sql和原sql在语义上一定是等价的。当然不是所有的子查询都能做子查询展开，有些子查询是不能做这种等价表连接转换的，这种情况下oracle就不会对其做子查询展开，也就是说此时oracle还是会将该子查询当作一个独立的处理单元来单独执行。另外，在oracle10g以后版本中，对于那种不拆开子查询但是会把该子查询转换成一个内嵌视图的子查询展开，只有当经过子查询展开后的等价改写sql的成本值小于原sql的成本值时，oracle才会对原sql执行子查询展开
子查询展开通常都会提高原sql的执行效率，因为如果原sql不做子查询展开，那么通常情况下该子查询就会在其执行计划的最后一步才被执行，并且会走filter类型的执行计划，这也就意味着对于外部查询所在结果集的每一条记录，该子查询就会被执行多少次，这种执行方式的执行效率通常情况不会太高，尤其在子查询中包含两个或两个以上表连接时，此时做子查询展开后的执行效率往往会比走filter类型的执行计划高很多。
Oracle 数据库里子查询前where条件如果是如下这些条件之一，那么这种类型的目标sql在满足了一定条件后就可以做子查询展开,
single-row,exists，not exists,in ,not in,any，all。
范例1：
SQL> set lines 200 pagesize 1000
in写法：
SELECT t1.cust_last_name, t1.cust_id
FROM customers t1
WHERE t1.cust_id IN
4 (SELECT t2.cust_id FROM sales t2 WHERE t2.amount_sold > 700)
5 ;
Execution Plan
----------------------------------------------------------
Plan hash value: 2448612695
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7059 | 158K| | 1583 (1)| 00:00:20 | | |
|* 1 | HASH JOIN SEMI | | 7059 | 158K| 1360K| 1583 (1)| 00:00:20 | | |
| 2 | TABLE ACCESS FULL | CUSTOMERS | 55500 | 704K| | 405 (1)| 00:00:05 | | |
| 3 | PARTITION RANGE ALL| | 560K| 5469K| | 526 (2)| 00:00:07 | 1 | 28 |
|* 4 | TABLE ACCESS FULL | SALES | 560K| 5469K| | 526 (2)| 00:00:07 | 1 | 28 |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
any等价写法：
SELECT t1.cust_last_name, t1.cust_id
FROM customers t1
WHERE t1.cust_id = ANY
4 (SELECT t2.cust_id FROM sales t2 WHERE t2.amount_sold > 700)
5 ;
Execution Plan
----------------------------------------------------------
Plan hash value: 2448612695
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7059 | 158K| | 1583 (1)| 00:00:20 | | |
|* 1 | HASH JOIN SEMI | | 7059 | 158K| 1360K| 1583 (1)| 00:00:20 | | |
| 2 | TABLE ACCESS FULL | CUSTOMERS | 55500 | 704K| | 405 (1)| 00:00:05 | | |
| 3 | PARTITION RANGE ALL| | 560K| 5469K| | 526 (2)| 00:00:07 | 1 | 28 |
|* 4 | TABLE ACCESS FULL | SALES | 560K| 5469K| | 526 (2)| 00:00:07 | 1 | 28 |
----------------------------------------------------------------------------------------------------------
exists等价写法：
SELECT t1.cust_last_name, t1.cust_id
FROM customers t1
WHERE t1.cust_id = ANY
4 (SELECT t2.cust_id FROM sales t2 WHERE t2.amount_sold > 700)
5 ;
Execution Plan
----------------------------------------------------------
Plan hash value: 2448612695
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7059 | 158K| | 1583 (1)| 00:00:20 | | |
|* 1 | HASH JOIN SEMI | | 7059 | 158K| 1360K| 1583 (1)| 00:00:20 | | |
| 2 | TABLE ACCESS FULL | CUSTOMERS | 55500 | 704K| | 405 (1)| 00:00:05 | | |
| 3 | PARTITION RANGE ALL| | 560K| 5469K| | 526 (2)| 00:00:07 | 1 | 28 |
|* 4 | TABLE ACCESS FULL | SALES | 560K| 5469K| | 526 (2)| 00:00:07 | 1 | 28 |
----------------------------------------------------------------------------------------------------------
1
2
3
4
5
6
7
不展开，显然不合理，sales表要执行很多次：
SELECT t1.cust_last_name, t1.cust_id
FROM customers t1
WHERE t1.cust_id IN (SELECT /*+ no_unnest */
t2.cust_id
FROM sales t2
WHERE t2.amount_sold > 700)
子查询展开后，变成hash 半连接：
等价写法：(如果cust_id是唯一键值)可以转换为内连接：
1
2
3
4
SELECT t1.cust_last_name, t1.cust_id
FROM customers t1, sales t2
WHERE t1.cust_id= t2.cust_id
AND t2.amount_sold > 700
如果是not in，则会转换为hash反连接：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SQL> set autot trace
SELECT t1.cust_last_name, t1.cust_id
FROM customers t1
WHERE t1.cust_id not in
4 (SELECT t2.cust_id FROM sales t2 WHERE t2.amount_sold > 700);
Execution Plan
----------------------------------------------------------
Plan hash value: 2850422635
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 48441 | 1088K| | 1583 (1)| 00:00:20 | | |
|* 1 | HASH JOIN ANTI | | 48441 | 1088K| 1360K| 1583 (1)| 00:00:20 | | |
| 2 | TABLE ACCESS FULL | CUSTOMERS | 55500 | 704K| | 405 (1)| 00:00:05 | | |
| 3 | PARTITION RANGE ALL| | 560K| 5469K| | 526 (2)| 00:00:07 | 1 | 28 |
|* 4 | TABLE ACCESS FULL | SALES | 560K| 5469K| | 526 (2)| 00:00:07 | 1 | 28 |
----------------------------------------------------------------------------------------------------------
把子查询转换成内联视图：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
SELECT t1.cust_last_name, t1.cust_id
FROM customers t1
WHERE t1.cust_id NOT IN
(SELECT t2.cust_id
FROM sales t2, products t3
WHERE t2.prod_id = t3.prod_id and t2.amount_sold > 700)
Execution Plan
----------------------------------------------------------
Plan hash value: 1272298339
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 48441 |1229K| |1665 (1)| 00:00:20 | | |
|* 1 | HASH JOIN ANTI | | 48441 |1229K|1360K|1665 (1)| 00:00:20 | | |
| 2 | TABLE ACCESS FULL | CUSTOMERS | 55500 | 704K| | 405 (1)| 00:00:05 | | |
| 3 | VIEW | VW_NSO_1 | 560K|7110K| | 529 (2)| 00:00:07 | | |
|* 4 | HASH JOIN | | 560K|9844K| | 529 (2)| 00:00:07 | | |
| 5 | INDEX FULL SCAN | PRODUCTS_PK | 72 | 288 | | 1 (0)| 00:00:01 | | |
| 6 | PARTITION RANGE ALL| | 560K|7657K| | 526 (2)| 00:00:07 | 1 | 28 |
|* 7 | TABLE ACCESS FULL | SALES | 560K|7657K| | 526 (2)| 00:00:07 | 1 | 28 |
--------------------------------------------------------------------------------------------------------------
这里oracle把子查询转换成内联视图VM_NSO_1，然后再和外部查询中的表customers做hash半连接。
等价：
1
2
3
4
5
6
7
SELECT t1.cust_last_name, t1.cust_id
FROM customers t1,
(SELECT t2.cust_id
FROM sales t2, products t3
WHERE t2.prod_id = t3.prod_id
AND t2.amount_sold > 700) vm_nso_1
WHERE t1.cust_id semi = vm_nso_1.cust_id
子查询是否能够做子查询展开取决于如下两个条件：
子查询展开所对应的等价改写sql和原sql在语义上一定要完全等价的，如果改写后的sql和原sql并不一定能保持语义上的完全等价，这种类型的子查询就不能做子查询展开。
对于不能拆开的子查询但是会把它转换为一个内嵌视图的子查询展开，只有经过子查询展开的等价改写sql成本值小于原sql的成本值。oracle才会对目标sql执行子查询展开。
对于子查询展开的第一种情形(即将子查询展开，把该子查询中的表，视图从子查询中拿出来，然后和外部查询中表，视图做表连接)，即使在oracle 10g以后的版本中，oracle也不会考虑子查询展开的成本，即oracle此时会认为这种情形下子查询展开的效率始终比不展开的效率高，这就意味着如果目标sql满足子查询展开的第一种情形。则oracle始终会做子查询展开，而不管经过子查询展开后的等价sql的成本值是否小于原sql的成本值。
一.4.2 将SQL优化做到极致-子查询优化
子查询是SQL中比较重要的一种语法，恰当地应用会很大程度上提高SQL的性能，若用的不得当，也可能会带来很多问题。因此子查询也是SQL比较难优化的部分。今天一起来学习最常见的几种优化子查询到方式。
针对子查询，优化器支持了多种优化策略。Oracle查询转换功能主要有启发式(基于规则)查询转换以及基于Cost的查询转换两种，针对子查询主要有Subquery Unnest、Push Subquery等。查询转换的目的是转化为Join(包括Semi、Anti Join等)，充分利用索引、Join技术等高效访问方式提高效率。如果子查询不能unnest(启发式)，可以选择把子查询转换为Inline View(基于Cost)；如果都不可以，那么子查询就会最后执行，可能会看到类似Filter的操作。
一、 子查询转换
下面先通过一个示例看看。
//上面代码准备了必要的数据环境，并收集相关对象的统计信息
//默认情况下，是将上面的操作转换为表间关联方式执行
/*通过提示no_unnest，禁止了子查询解嵌套。一次采用了原始的方式执行，子查询部分的作用就是"FILTER"*/
二、 子查询合并
三、 解嵌套子查询--子查询展开(解嵌套)
四、 子查询推进
五、 子查询分解
所谓子查询分解，是指由WITH创建的复杂查询语句存储在临时表中，按照与一般表相同的方式使用该临时表的功能。从概念上来看它与嵌套视图比较类似，但各自有其优缺点。优点在于子查询如果被多次引用，使用嵌套视图就需要被执行多次，尤其在海量数据中满足条件的结果非常少得情况下，两者差别很明显。
使用WITH子查询的优点就在于其复杂查询语句只需要执行一次，但结果可以在同一个查询语句中被多次使用。缺点是使用WITH子查询，由于不允许执行查询语句变形，所以无效的情况也比较多。尤其是WITH中的查询语句所创建的临时表无法拥有索引，当其查询结果的数据量比较大的时候，很可能会影响执行效率。
下面通过一个是示例看看。
/*从上面可以看出，在WITH中有两个子查询语句，但只创建了一个临时表，这是因为WITH中的第二个子查询使用的是第一个子查询的执行结果。在这种情况下，逻辑上只允许创建一个临时表，没有必要再次创建。在处理WITH临时表时，如果临时表可以被优先执行而且可以缩减连接之前的数据量，就可以采用嵌套循环连接，否则必须使用哈希连接*/
六、 子查询缓存
针对某些子查询操作，优化器可以将子查询的结果进行缓存，避免重复读取。这一特性在FILTER型的子查询或标量子查询中都能观察到。看一个示例。
/*注意Id=3步骤的Start=3(emp表中的deptno有3个不同的值，这里就重复执行3次)。这体现了Cache技术，标量子查询中也有类似的Cache技术。*/
一.4.2.1 性能优化之查询转换 - 子查询类
子查询，是SQL中常见的一种写法。对于优化器来说，子查询是较难优化的部分。Oracle提供了多种方式，对子查询进行查询转换。
一.4.3 子查询分解
子查询分解是由WITH创建的复杂查询语句并存储在临时表中，可按照与一般表相同的方式使用该临时表的功能。这种方式可以把一个复杂的查询分成很多简单的部分，并让优化器去决定是产生中间数据集还是构建该查询复杂的扩展形式并对其进行优化。这种方式的优点在于，使用WITH子句的子查询在复杂查询语句中只需要执行一次，但结果可以在同一个查询语句中被多次使用。缺点在于，这种方式不允许语句变形，所以无效的情况较多。
下面看一个示例。
SQL> with user_obj as
(select owner,count(*) cnt
from t_objects group by owner)
select u.user_id,u.username,o.cnt
from t_users u,user_obj o
where u.username=o.owner;
子查询定义为user_obj，在执行计划中以一个视图的形式(ID=2的步骤)出现，并与T_USRES进行了哈希关联。
上述过程并没有生成临时表，可通过一个提示materialize强制优化器创建临时表。
SQL> with user_obj as
(select --+ materialize owner,count(*) cnt
from t_objects group by owner )
select u.user_id,u.username,o.cnt
from t_users u,user_obj o
where u.username=o.owner;
执行计划如下：
引入了materialize提示后，由ID=2步骤可见，系统生成了一个临时表SYS_TEMP_XXX，并由这个表在后面与T_USERS进行了关联查询。
一.4.3.1 子查询合并
5
子查询实体化
子查询实体化是指在上面WITH定义的查询中，将查询结果写入一张临时表中，后续的查询直接利用临时表中的数据。可以通过MATERIALIZE提示来控制。下面看个示例。
SQL> with v as
(select /*+ MATERIALIZE */ * from t_users where username='SYS')
select count(*) from v;
执行计划如下：
在ID=2的步骤中生成了一张临时表SYS_TEMP_xxx，并且这个临时表在后面会被直接使用。如果去掉提示会怎样呢？
SQL> with v as
(select * from t_users where username='SYS')
select count(*) from v;
执行计划如下：
此时不再生成临时表，直接解嵌套执行。
一.4.3.2 Oracle性能优化-子查询的特殊问题
编辑手记：前面我们介绍常用的子查询优化方法，但总有一些情况时在规律之外。谨慎处理方能不掉坑。
1、空值问题
首先值得关注的问题是，在NOT IN子查询中，如果子查询列有空值存在，则整个查询都不会有结果。这可能是跟主观逻辑上感觉不同，但数据库就是这样处理的。因此，在开发过程中，需要注意这一点。看个例子吧。
SQL> select * from dual where 2 not in (select 1 from dual);
D
-
X
SQL> select * from dual where 2 not in (select 1 from dual union all select null from dual);
no rows selected
显然，第二条语句在印象中应该会返回记录，但实际情况就是没有。
第二个值得关注的是，在11g之前，如果主表和子表的对应列未同时有NOT NULL约束，或都未加IS NOT NULL限制，则Oracle会走FILTER。11g有新的ANTI NA(NULL AWARE)优化，可以正常对子查询进行UNNEST。
注意此时的关联字段OBJECT_ID，是可为空的。示例模拟了11g以前的情况，此时走了最原始的FILTER
在确定子查询列object_id不会有NULL存在的情况下，又不想通过增加NOT NULL约束来优化，可以通过上面方式进行改写
在11g的默认情况下，走的就是ANTI NA(NA=NULL AWARE)
2、OR问题
对含有OR的Anti Join或Semi Join，注意有FILTER的情况。如果FILTER影响效率，可以通过改写为UNION、UNION ALL、AND等逻辑条件进行优化。优化的关键要看FILTER满足条件的次数。看下面的示例。
//上例中包含有OR条件的Semi Join，执行计划中使用了FILTER过滤，整个逻辑读消耗为69。
//下面通过改写，看看效果如何？
//将上面的OR连接修改为UNION,消除了FILTER。从成本或逻辑读等角度来看，整个逻辑读为30，较前面的69大大降低了
3、[NOT] IN/EXISTS问题
下面看两个关于[NOT] IN/EXISTS的问题。
1. IN/EXISTS
从原理来讲，IN操作是先进行子查询操作，再进行主查询操作。EXISTS操作是先进行主查询操作，再到子查询中进行过滤。
IN操作相当于对inner table执行一个带有distinct的子查询语句，然后得到的查询结果集再与outer table进行连接，当然连接的方式和索引的使用仍然等同于普通的两表连接。
EXISTS操作相当于对outer table进行全表扫描，用从中检索到的每一行与inner table做循环匹配输出相应的符合条件的结果，其主要开销是对outer table的全表扫描(full scan)，而连接方式是nested loop方式。
当子查询表数据量巨大且索引情况不好(大量重复值等)，则不宜使用产生对子查询的distinct检索而导致系统开支巨大的IN操作；反之当外部表数据量巨大(不受索引影响)而子查询表数据较少且索引良好时，不宜使用引起外部表全表扫描的EXISTS操作。如果限制性强的条件在子查询，一般建议使用IN操作。如果限制性强的条件在主查询，则使用EXISTS操作。
2. NOT IN/EXISTS
在子查询中，NOT IN子句将执行一个内部的排序和合并。无论在哪种情况下，NOT IN都是最低效的(因为它对子查询中的表执行了一个全表遍历)。
为了避免使用NOT IN，可以把它改写成外连接(Outer Joins)或NOT EXISTS。
扫一扫
800
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
