Optimizer Operations
How the Optimizer Performs Operations
How the CBO Evaluates IN-List Iterators
当 IN clause 指定了特定的值，并且在该列上有索引，优化器选择 IN-list iterator。如果有多个 OR clauses 使用相同的索引，那么优化器选择更高效的 IN-list iterator，而不使用 CONCATENATION or UNION ALL。
How the CBO Evaluates Concatenation
当不同的条件使用 OR clause 连接起来，并且不同的条件都可以通过不同的索引生成较好的执行计划，那么 Concatenation 是很有用的。
HINT：
USE_CONCAT
NO_EXPAND 会禁止使用 Concatenation，他其实是阻止 QUERY 扩展为多个 QUERY
当一下情况时不要使用 Concatenation：
1.OR conditions 在同一个列上，可以使用 IN-list，后者更高效。
2.每一个 concatenation 都重复昂贵的步骤。
How the CBO Evaluates Remote Operations
影响执行计划的因素：
1.Network round trips 比物理和逻辑 I/Os 昂贵几个数量级
2.如果远程数据库不是 Oracle 数据库，优化器无法获得远程数据库的任何 statistics
一般来说，优化器在访问本地表之前先访问远程表
How the CBO Executes Distributed Statements
1.如果 SQL 语句中的所有表来自同一个远程数据库，Oracle 把语句发送给远程数据库，远程数据库执行完之后把结果发还给本地数据库。
2.如果表来自不同的数据库，Oracle 把语句拆分，每一个部分访问单个数据库上的表，把他们分别发送给各数据库，各数据库执行自己部分的语句，并把结果发还给本地数据库，本地数据库再执行语句的其余处理部分。
如果是 CBO，优化器会考虑远程数据库上的索引，就像本地数据库一样，还会考虑远程的 statistics，此外，在估计访问的 cost 时，还会考虑数据的位置，比如远程的一个全表扫描比本地相同表的全表扫描估计的 cost 要高。
对于 RBO，优化器不会考虑远程数据库上的索引。
How the CBO Executes Sort Operations
SORT UNIQUE
如果使用了 DISTINCT clause 或者 unique values 在下一步中需要，就会发生 SORT UNIQUE
SORT AGGREGATE
SORT AGGREGATE 实际上不发生 sort，他使用于对整个 set of rows 进行聚合计算。
SORT GROUP BY
SORT GROUP BY 用于对不同组上的数据进行聚合计算，这种情况下 sort 是需要的，sort 用于将行拆分成不同的组。
SORT JOIN
在 SORT MERGE JOIN 中，如果数据需要根据 join key 排序，就会发生 SORT JOIN。
SORT ORDER BY
当语句中使用 ORDER BY，并且没有任何索引适合这种排序方式，那么 SORT ORDER BY 就需要。
How the CBO Executes Views
以下情况 CBO 产生 VIEW：
1.语句中有没有被 decomposed 的 View
2.语句中有 temporary or inline view 被使用
How the CBO Evaluates Constants
常量的计算只在语句被优化时执行一次，而不是每次语句被执行的时候。
比如：salary > 24000/12 会被优化器简化为 salary > 2000
优化器不会跨过比较符简化表达式，salary*12 > 24000 不能被简化为 salary > 2000，因此写语句时应尽量用常量和列作比较，而不要将列作计算之后再去比较。
How the CBO Evaluates the UNION and UNION ALL Operators
对于将 OR clauses 组合为一个复合语句，或者将一个复杂语句分解为包含简单 select 语句的复合语句很有用，他们更易于优化和理解。
就和 concatenation 一样，如果 UNION ALL 重复了昂贵的操作，就不应该使用。
How the CBO Evaluates the LIKE Operator
对于没有通配符的 like 条件，优化器会将他简化为等于操作
last_name LIKE 'SMITH' -->> last_name = 'SMITH'
但这种简化只能用于变长的类型，对于固定长度的，比如 CHAR(10) 就不能简化，因为等于操作遵循 blank-padded semantics，而 like 不是（此规则只适合 9i 以上）。
CREATE TABLE ducks (f CHAR(6), v VARCHAR2(6));
INSERT INTO ducks VALUES ('DUCK', 'DUCK');
commit;
select * from ducks where f='DUCK'; <<---'DUCK' 自动填充空格至 6 位长度
F V
------------ ------
DUCK DUCK
select * from ducks where f like 'DUCK'; <<---不填充空格
no rows selected
以上结果在 9i 上有效，8i 下两者都返回行
How the CBO Evaluates the IN Operator
优化器将 IN comparison operator 条件扩展为等价的 equality comparison operators and OR logical operators 条件
How the CBO Evaluates the ANY or SOME Operator
1.列表
优化器将其扩展为等价的 comparison operators and OR logical operators 条件
salary > ANY (:first_sal, :second_sal) -> salary > :first_sal OR salary > :second_sal
2.子查询
优化器将其转化为等价的 EXISTS operator and a correlated subquery 条件
x > ANY (SELECT salary
FROM employees
WHERE job_id = 'IT_PROG')
变为
EXISTS (SELECT salary
FROM employees
WHERE job_id = 'IT_PROG'
AND x > salary)
How the CBO Evaluates the ALL Operator
1.列表
优化器将其扩展为等价的 comparison operators and AND logical operators 条件
2.子查询
优化器将其转化为等价的 ANY comparison operator and a complementary comparison operator 条件
x > ALL (SELECT salary
FROM employees
WHERE department_id = 50)
变为
NOT (x <= ANY (SELECT salary
FROM employees
WHERE department_id = 50) )
然后再进一步根据 ANY Operator 的转换规则再将其转换：
NOT EXISTS (SELECT salary
FROM employees
WHERE department_id = 50
AND x <= salary)
How the CBO Evaluates the BETWEEN Operator
优化器总是将 BETWEEN 条件用 >= and <= 条件来代替
How the CBO Evaluates the NOT Operator
优化器使用除去 NOT logical operator 并使用相反的 comparison operator 代替原来 comparison operator 的方法来简化条件，使 NOT logical operator 消除。
优化器会将 NOT 传递到子条件中，以便尽可能的简化条件，即使子条件中会产生更多的 NOTs：
NOT (salary < 1000 OR commission_pct IS NULL)
=>
NOT salary < 1000 AND commission_pct IS NOT NULL
=>
salary >= 1000 AND commission_pct IS NOT NULL
How the CBO Evaluates Transitivity
如果两个条件涉及到同一个 column，且这个 column 的其中一个条件是和 constant expressions 进行比较，那么有时候优化器会推断出一个条件，这个推断的条件可以使用 index access path，而原始的条件却不能使用：
WHERE column1 comp_oper constant
AND column1 = column2
其中：
comp_oper 为任何比较操作：=, !=, ^=, <, <>, >, <=, or >=
constant 为任何常量表达式（不能为其他 column）：SQL functions, literals, bind variables, and correlation variables
这时，优化器会推断一个条件：
column2 comp_oper constant
如果 column2 上有索引，就能使用该索引
注：Transitivity 只用于 CBO
How the CBO Optimizes Common Subexpressions
公共的子表达式优化是一种启发式的优化，可以鉴别、移出、收集在各 disjunctive (OR) branches 中的公共子表达式，绝大数情况下，可以减少 join 的数量。
在满足一下情况时，可使用公共子表达式优化（从最顶层至最内层的顺序）：
1.顶层条件是一个 disjunction（几个以 or 连接的条件）
2.每个 or 分支中是 simple predicate 或者 a conjunction（几个以 and 连接的条件）
3.每个 and 分支中是 simple predicate 或者 a disjunction of simple predicates
4.表达式在每个 or 分支中都出现，即公共子表达式
simple predicate 只不含有 AND or OR 连接的条件
满足以上条件的公共子表达式，优化器会将其移到最顶层，去除重复，再和被移去公共子表达式的原 disjunction 做 conjunction，这样可以减少 join 操作。
How the CBO Evaluates DETERMINISTIC Functions
某些情况，优化器不需要计算 user-written function 的值，而用以前计算的值来代替他。
这种 function 必须有一定的限制：
1.Function 的返回值不能随着 package variables、database、session parameters 的不同而改变
2.如果 function 被重定义了，那么他的返回值和以前的要保持一致
3.使用预计算结果代替执行 function 必须没有重大副作用
使用 DETERMINISTIC 关键字创建的 function 告诉 Oracle 该 function 满足以上限制，Oracle 不会去检查该 function，即使 function 很明显不满足以上限制，因此，程序员应负责检查以上限制，只有满足了才能加 DETERMINISTIC 关键字。
How the Optimizer Transforms SQL Statements
How the CBO Transforms ORs into Compound Queries
如果一个查询包含多个用 OR 连接的条件，优化器会将其转换为用 UNION ALL 连接的混合查询，如果转换后的语句执行更加高效
1.如果每个条件都可以单独使用 index access path，那么可以转换。优化器将转换后的语句生成执行计划，他们用不同的索引多次访问表，并把结果放到一起
2.如果任何一个条件使用 full table scan，那么不会转换，因为一个 full table scan 和几个 index scan 结合的效率不如直接使用一个 full table scan 效率高
3.对于 CBO，优化器会估计并比较转换前后的语句所需的 COST，并决定是否转换
4.CBO 不会转换 IN-lists 或者条在在同一列上的 OR 操作，而使用 INLIST iterator operator
How the CBO Unnests Subqueries
优化复杂语句，优化器选择下面两种方法的其中一种：
1.如果复杂语句可以转换为等同的 join 语句，那么先转换，再优化 join 语句，这样可以利用 join 语句的优势
2.如果无法转换，那么优化复杂语句本身
子查询包含 aggregate functions，比如 AVG 的复杂语句，无法转换为 join 语句
How the CBO Merges Views
merge view 的方法：
在语句中将 view 的名字用 view 基表的名字代替，将 view 中的条件加到语句的条件中去
Mergeable and Nonmergeable Views
merge 只适合 select-project-join views，即 view 中只包含 selections, projections, and joins
如果 view 中包含以下元素，不能 merge：
1.Set operators (UNION, UNION ALL, INTERSECT, MINUS)
2.A CONNECT BY clause
3.A ROWNUM pseudocolumn
4.Aggregate functions (AVG, COUNT, MAX, MIN, SUM) in the select list
当 view 中包含以下元素时，只有 Complex View Merging is enabled 的时候才能 merge：
1.A GROUP BY clause
2.A DISTINCT operator in the select list
如果 view 中包含多个基表，那么当该 view 在 outer join 语句的右边时，无法 merge，如果只有一个基表，那么可以使用 complex view merging，即使 an expression in the view can return a nonnull value for a NULL.
如果一个查询语句包含 CURSOR expression，那么即使 view 可以被正常 merge 优化器也不会将它 merge
Complex View Merging
如果 complex view merging is enabled 的，那么包含 GROUP BY clause or DISTINCT 的 view 可以被 merge
Complex merging 还可以用于 merge an IN subquery，只要 subquery 是不相关的
Complex merging 不是 cost-based 的，必须用 OPTIMIZER_FEATURES_ENABLE or the MERGE hint 开启才能使用，如果没有 hint or parameter setting，优化器会使用另外一种方法：pushing predicates
How the CBO Pushes Predicates
优化器可以将访问 nonmergeable view 的 query block 通过 pushing the query block's predicates inside the view's query 来转换
How the CBO Applies an Aggregate Function to the View
优化器可以转换包含 aggregate function 的 query，通过将 aggregate function 应用到 view's query
How the CBO Executes Views in Outer Joins
如果 view 在一个 outer join 的右边：
1.如果 view 只有一个基表，优化器使用 view merging
2.如果 view 有多个基表，优化器可以 push the join predicate into the view
How the CBO Accesses the View's Rows with the Original Statement
如果优化器无法将访问 view 的语句转换为等价的访问基表的语句，比如 view 中包含 ROWNUM pseudocolumn，view 不能被 merge，也不能 push predicate，那么 Oracle 执行 view 中的语句，获得结果集，original statement 将其当作一个表来处理。
How the CBO Executes Compound Queries
为了为混合查询选择执行计划，优化器首先为混合查询的每个部分选择执行计划，然后用 set operator 联合各 resulting row sources
转引自： http://www.heysky.net/archives/2006/08/optimizer_operations_notes.html