Oracle 的优化器（Optimizer）实际上是数据库环境的参数设置。可以在INITsid.ORA 文件内的
OPTIMZER_MODE=RULE 或OPTIMZER_MODE=COST 或OPTIMZER_MODE=CHOOSE 来
设置优化目标。用户也可以在会话和查询方式下更改优化器的默认操作模式。
如果OPTIMZER_MODE=RULE，则激活基于规则的优化器(RBO)。基于规则的优化器按照一
系列的语法规则来推测可能执行路径和比较可替换的执行路径。
如果OPTIMZER_MODE=COST，则激活基于成本的优化器(CBO)。它使用ANALYZE 语句来
生成数据库对象的统计数据。这些统计数据包括表的行数、平均长度及索引中不同的关键字
数等。基于这些统计数据，成本优化器可以计算出可获得的执行路径的成本。并选择具有最
小的成本执行路径。在CBO 模式下，需要经常运行ANALYZE 命令来确保数据的准确性。
如果OPTIMZER_MODE=CHOOSE，则在表被分析的情况下激活基于成本的优化器。但当一
个查询分析的表是未被ANALYZE 分析统计过的时侯，CBO 优化器就决定进行全表扫描操
作。所以为了减少可能的全表扫描，应该尽量避免使用OPTIMZER_MODE=CHOOSE 选项。
下面介绍在编程序中常用到的语句优化。
讨论SQL 处理技术、优化方法以及优化程序如何执行SQL 语句。包括：
SQL 处理体系结构
EXPLAIN PLAN
优化程序的定义
选择优化程序的方法和目标
基于开销的优化程序(CBO=Cost_Base Optimizer)
CBO 参数
可扩展的优化程序
基于规则的优花程序(RBO=Rule_Base Optimizer)
优化程序操作概述
优化连接（Join）
优化使用公共子表达式的语句
表达及条件的评价
转换及优化的语句
SQL 处理体系结构主要有：
解析程序
优化程序
行源产生程序
SQL 执行
解析程序执行下面两种功能：
语法分析：检查SQL 语句的语法正确性
语义分析：例如检查当前数据库对象和相应的对象属性，并判断其准确性。
优化器是SQL 处理引擎的核心，Oracle 服务器支持两种优化方法：基于规则的优化和基
于开销的优化程序
行源程序产生器(Row Source generator)从优化程序接受优化规则，并输出SQL 语句的执
行规则。执行规则由行源的集合所组成，它呈现出树型结构。行源程序是一种迭代的控制结
构，它以迭代的方式对执行集合进行处理，并且每次只处理一次
SQL 执行（Execution）是操作执行规则时的组件，它与SQL 语句紧密相连，并且可以给出查
询的结果。
通过使用EXPLAIN PLAN 语句，用户可以对SQL 语句优化程序所选择的执行规划进行
检查。只要发出EXPLAIN PLAN 语句，就可以对输出表进行查询，下面是输出表的数据描述
先前所检查的语句：
ID OPERATION OPTION OBJECT_NAME
----- ----------------------------------------- ------------------------- -----------------------
0 SELECT STATEMENT
1 FILTER
2 NESTED LOOPS
3 TABLE ACCESS FULL EMP
4 TABLE ACCESS BY ROWID DEPT
5 INDEX UNIQUNE SCAN PK_DEPTNO
6 TABLE ACCESS FULL SALGRADE
默认方式下，基于代价（CBO）是最好的方法。它使用最少的资源就能对所有行进行处
理。Oracle也可以使用时间响应来优化语句。如： SQL语句并行执行。优化器可以选择最小共
用资源消费开支时间。它可以由参数OPTIMIZER_PERCENT_PARALLEL来指定并行执行的优
化器的个数。
执行计划由优化器根据目标来产生。最大吞吐量优化类似于宁愿不要索引的全表扫描的结果
或者相当于不要嵌套循环的分类合并连接。然而最佳响应时间的优化有点类似于索引扫描或
嵌套循环连接。
例如你可以在任意的嵌套循环操作中或分类合并操作中使用连接语句。当嵌套循环操作快速
返回第一行时，这些分类合并语句也可以快速返回整个结果。如果你的目标是改善吞吐量。
那么优化器就有点象选择分类合并连接操作。如果你的目标是选择改善响应时间，则优化器
更象选择一个嵌套连接操作。
综合上面的描述，选择优化器主要是基 于应用的需要：
对于批处理应用，如报表输出应用，需要采用吞吐量优化。因为吞吐量对于批处理来
说更重要。
对于交互式应用，如Oracle Forms 应用或SQL*PLUS查询，需要采用最佳时间响应优
化。因为交互式用户等着看到第一行的数据。
对于用ROWNUM来限制查询结果的查询，优化首先要考虑的响应时间。因为它要求
的是得到最快的结果。
当选择最佳路径和目标时，优化器受到下面因素的影响：
?OPTIMIZER_MODE 初始参数
?数据字典的统计数据
? ALTER SESSION 语句的OPTIMIZER_GOAL 参数
? 在提示中改变目标
OPTIMIZER_MODE 初始参数
可以用OPTIMIZER_MODE来建立优化路径的默认值,它可以取下面的值：
choose 表示优化器在基于代价和基于规则两种之间进行选择。如果数据字典有访问表的至少
一行的统计数据，则优化器使用的基于代价和最佳吞吐量方法。如果访问表没有统计数据，
则优化使用基于规则的路径。默认为基于规则的路径。
All_rows 对整个SQL语句，优化器使用基于代价的路径。使用最小资源返回整个行。
FIRST_ROWS 对整个SQL语句，优化器使用基于代价的路径。使用最小资源返回第一行。
RULE 对整个SQL语句，优化器使用基于规则的路径。
如果优化使用基于代价的路径，而访问的表没有统计数据时，优化器就使用该表的一些近似
值来代替。
数据字典中的统计数据
Oracle 为CBO 存储有列、表、簇 、索引及分区的统计数据。可以使用ANALYZE 语句或
COMPUTE STATISTICS 子句和DBMS_STATS 包来得到详细的统计结果。为了给优化提供最
新的数据，你应该经常使用ANALYZE 语句对表进行统计
ALTER SESSION 语句的OPTIMIZER_GOAL 参数
ALTER SESSION 语句中带OPTIMIZER_GOAL 参数可以越过初始化路径和
OPTIMIZER_MODE 参数所建立的目标。这个参数的结果影响到SQL 语句的优化。但它对会
话中所使用SQL 语句的递归不起作用。OPTIMIZER_GOAL 可以有下面的值：
choose 表示优化器在基于代价和基于规则两种之间进行选择。如果数据字典有访问表的至少
一行的统计数据，则优化器使用的基于代价和最佳吞吐量方法。如果访问表没有统计数据，
则优化使用基于规则的路径。默认为基于规则的路径。
All_rows 对整个SQL语句，优化器使用基于代价的路径。使用最小资源返回整个行。
FIRST_ROWS 对整个SQL语句，优化器使用基于代价的路径。使用最小资源返回第一行。
RULE 对整个SQL语句，优化器使用基于规则的路径。
关于提示的改变目标
可以在单个的SQL 语句中，使用FIRST_ROWS, ALL_ROWS, CHOOSE, 或 RULE 可以替代由
OPTIMIZER_MODE 初始参数和ALTER SESSION 的OPTIMIZER_GOAL 参数所设置的效果。
在默认下，系统使用的是基于最佳吞吐量的代价的优化。但可以用下面方法来改变CBO 的目
标：
在会话中为CBO改变所有的SQL语句目标，使用：
ALTER SESSION SET OPTIMIZER_MODE 语句带 ALL_ROWS 或FIRST_ROWS子句。
为单个SQL语句指定CBO的目标。使用 ALL_ROWS 或 FIRST_ROWS 提示。如：
ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS;
基于代价优化器（CBO）
CBO在考虑访问路径和模式的统计数据（表或索引）来确定哪种执行计划最有效。同时CBO
也考虑提示语句中哪种是最优的建议。CBO 考虑下面步骤：
1. 优化器在它的有效路径和提示中为SQL语句产生一组潜在的计划。
2. 优化器根据数据字典的统计估计每个计划的代价，并存储表、索引和分区的特征。
代价（cost）是预期资源要求的估计值。优化器计算每一种访问方法的代价并基于估计计算机
资源（包括I/O和内存）的次序。从而得到执行该语句的需求。
较大的代价需要较多时间来执行，而较小的计划需要较少的时间来执行。当使用并行时，资
源不直接与消失的时间有关。
3. 优化器比较这些代价然后选择较小的代价。
维护CBO的有效性，必须收集有关的统计并使它们准确才行。可以用下面方法来收集统计数
据：
用ANALYZE 语句；
使用DBMS_STATS 包。
CBO 结构调整
CBO包括下面部件：
查询转换器
评价器
规划产生程序
CBO 需求
要采用基于CBO的优化，要求下面的任何一种特征：
分区的表
索引构造的表
逆键字索引
基于函数的索引
在SELECT语句中的SAMPLE子句
并行执行和并行DML
星形转换
星形连接
可执行的优化器
查询重写(实体化视图)
进展仪表(Progress meter)
HASH 连接
位图索引
使用CBO
要在语句中使用CBO，就要收集表的统计数据，并用下面方法来使CBO有效：
确认OPTIMIZER_MODE 初始参数设置为 CHOOSE.
在会话中启动CBO ，要用 ALTER SESSION SET OPTIMIZER_MODE 语句并带
ALL_ROWS 或FIRST_ROWS 子句。
在一个单独的SQL语句中使用CBO，要用提示而不用RULE。
由CBO根据表的大小来产生规划，如果使用直方图情况下，还要产生的潜在的数据分布。当
通过具有少量数据的CBO来进行应用程序的原型测试时，不要认为实际数据库选择的规划与
原型数据库所选择的规划就一定一致。
CBO 访问路径
明确地描述执行计划如何从数据库返回数据是优化器的最重要选择之一。对于任何表的任意
行，该表的行的定位和返回都有许多的访问路径。优化器可从中选择一个。下面是Oracle访问
数据的基本方法：
全表扫描
全表扫描是从表中返回所有的行。执行全表扫描，Oracle从该表读所有的行。检查每一行，看
它是否满足where 子句的要求。实际上Oracle是读表中相继的数据块。所以它可以采用多块
读。
样本表扫描
Oracle在Oracle8i Release 8.1.6 版本之后，提供了在select 语句后加 SAMPLE BLOCK 来对表进
行部分的扫描查询。这种查询要求是只能对单表进行，不能在连接查询上使用样本扫描。也
不能在远程进行样本扫描。当然，如果希望在远程上进行样本扫描的话，可以先用CREATE
TABLE AS SELECT 语句将远程表复制到本地，然后在使用 SAMPLE BLOCK SAMPLE
BLOCK 语句。
例：扫描emp表的1%的内容，则发出：
select count(*)*100 from emp sample block(1);
或
SELECT * FROM emp SAMPLE BLOCK (1);
用Rowid访问表
用Oracle的rowid 也能访问到表的记录。每个行的rowid 能确定数据文件、行所在数据块及该行
所在的块位置。可以用rowid 来快速定位到一个单行。
簇(Cluster)扫描
对于以索引簇形式存放的表，簇扫描能够从中获得具有相同簇键(cluster key)值的行。在索引
化的簇中，所有具有相同簇键值的行都被存储进相同的数据库块中。为了执行簇扫描，Oracle
首先通过扫描簇的索引，并从 中得到所选择的rowid 值，然后再基于该rowid 对所有选择行进
行定位。
基于规则(RBO)的优化程序
尽管Oracle 支持基于RBO(Rule-Based Optimzer)的优化程序，但建议大多数系统还是采用
基于CBO 的优化。因为CBO 支持DSS 的某些新增强的功能，所以对于象数据仓库应用系统
也要采用CBO 方法。
如果满足：
１） 设置 OPTIMIZER_MODE=CHOOSE;
２） 数据字典中没有统计信息；
３） 未给SQL 语句加提示。
则可以采用RBO。
如果是：
１） OPTIMIZER_MODE=FIRST_ROWS 或ALL_ROWS ;
２） 不存在可用的统计信息；
则CBO 使用默认值
RBO 访问路径
如果使用基于RBO，则在执行规则中，访问路径的等级具有启发性，即如果存在多种可执行
的SQL 语句，则RBO 选择等级较低的操作，因为等级低的语句执行速度快。下面是RBO 访
问路径及等级：
路径1：根据rowid 访问行
路径2：通过cluster 联结访问行
路径3：根据唯一性主键或主键的哈希簇访问单行
路径4：根据唯一键或主键访问单行
路径5：cluster 连接
路径6：哈希cluster 键
路径7：索引化cluster 键
路径8：复合索引
路径9：单列索引
路径10：索引化有界搜索
路径11：索引化无界搜索
路径12：合并排序联结
路径13：带有MAX 或MIN 的列
路径14：带ORDER BY 的查询
路径15：全表扫描
优化器操作
Oracle 接收到一条SQL 语句后，首先进行句法分析，检查文本并对该查询产生可执行的方案
（plan）。这个可执行的方案有EXPLAIN PLAN 来处理，方案确定后语句就被存放在SQL 共
享区内。而Oracle 的优化器是用来确当对特定语句的最佳执行路径。Oracle 的优化器可以有
两种类型：即基于规则和基于代价。下面是优化程序能进行优化的SQL 语句。
可优化的SQL 语句
简单的SQL 语句，即只设计单个表的insert,update,select
简单的查询
等式连接
非等式连接
外连接
笛卡尔乘积
复合语句
组合查询
访问视图
分布式语句
优化程序操作
优化程序自动简化ＳＱＬ语句中的某些常用结构，如果结果简化执行的话，这些语句变
得非常简单，如2000/10 简化为 200 。也可能变复杂，如将带 OR 的运算语句转换为两个复
合的子查询。对于前者可以随时进行，但后者则取决于where 子句的列上是否有索引以及选
择哪种优化方法。
此外，还有一些其它的转换，包括：
化简算术表达式
将IN 转化为一系列的OR 条件
将一个BETWEEN … AND 转换为一对比较表达式
将 OR 转换为复合查询
视图的定义合并到条件语句中
将一个复杂语句转换成连接条件语句
基于规则或基于代价优化方法
Oracle 按照若干准则对每个语句进行优化，包括：
引用的对象有一个并行度
语句中的提示
OPTIMZER_GOAL 的会话设置
初始化参数OPTIMZER_MODE 的值
被引用的对象的统计结果
通过优化程序的分析，决定执行是采用基于规则或基于代价方法。考察优化的首要因素是看
语句是否存在一次全表扫描和是否有并行（是否包括PARALLEL 选项）。如果都有的话，就
使用基于代价的优化来创建一个包含并行的执行计划。
基于规则的方法不支持并行处理语句的执行计划。如果没有rule 语句的提示，Oracle 都采用基
于代价方法进行优化
优化连接
Oracle 提供了4 种连接操作：
嵌套循环连接（nested loop）
合并排序连接（merge join）
哈西连接(Hash join)
簇连接（cluster join）
嵌套连接
嵌套循环连接（NESTED LOOPS）操作是将两个数据源连接起来。通常是在连接时系统提供
了可用的索引。实际在处理过程中，Oracle 先处理第1 行，接着下一行（不是等到整个处理完
毕才返回第1 行）。在处理嵌套连接时，Oracle 优化器首先为连接选择一个驱动表 ，可能对驱
动表进行全表扫描。对驱动表的每行进行索引检查，以便了解在表之间是否存在匹配。如果
有一个匹配存在，则通过NESTED LOOPS 操作并将该记录返回给用户。
例如：
SQL> explain plan for
select a.deptno,a.dname,b.empno,b.ename,b.sal
from dept a, emp b where a.deptno=b.deptno and a.deptno=20;
Explained.
SQL> select operation,options,object_name from plan_table;
OPERATION OPTIONS OBJECT_NAME
------------------ ------------------ ------------
SELECT STATEMENT
MERGE JOIN
TABLE ACCESS BY INDEX ROWID DEPT
INDEX UNIQUE SCAN PK_DEPT
FILTER
TABLE ACCESS FULL EMP
6 rows selected.
SQL> explain plan for
select a.deptno,a.dname,b.empno,b.ename,b.sal
from dept a, emp b where a.deptno=b.deptno and b.empno=7369;
Explained.
SQL> select operation,options,object_name from plan_table;
OPERATION OPTIONS OBJECT_NAME
------------------ ------------------ ------------
SELECT STATEMENT
NESTED LOOPS
TABLE ACCESS BY INDEX ROWID EMP
INDEX UNIQUE SCAN PK_EMP
TABLE ACCESS BY INDEX ROWID DEPT
INDEX UNIQUE SCAN PK_DEPT
6 rows selected.
在看为等价连接使用索引的例子：
SQL> select dept_id, id
2 from s_emp, s_dept
3 where s_emp.dept_id = s_dept.id;
这样Oracle 优化器使用嵌套循环连接替代排序合并连接。嵌套循环连接不需要任何的排序操
作，所以操作步骤为：
1 执行S_EMP 表全表扫描
2.每个返回行都使用DEPT_ID值，以在PK_ID索引上进行唯一扫描
3.使用从索引中返回的ROWID来定位S_DEPT表的相应行.
4. 为S_DEPT组合从S_EMP返回的每个行。
合并连接
排序合并连接用于从两个独立的数据源进行连接。HASH 连接可能执行比排序合并连接更好。
但另一方面，排序连接可能在下面情况下执行比HASH 连接更好。
行源数据已经做过排序；
一个排序也没有做。
然而，如果一个排序操作涉及到一个较慢的方法（如一个索引扫描对应全表扫描），则采用排
序合并可能会失策。
当连接条件是在两个表之间使用不等式（如<、<=、>、>=）时，排序合并连接很有用。排序
合并连接执行比对大量数据进行嵌套循环连接更好(不要用HASH 连接，除非有等价条件)。
在合并连接里，没有驱动表概念，该连接主要考虑两步：
１． 排序连接操作:连接键的输入端都是被排序过；
２． 合并连接操作：排序列表要合并在一起。
例子：带有非等价的排序连接
SELECT SUM(l.), l2.
FROM order_items l, order_items l2
WHERE l. < l2.
AND l.order_id <> l2.order_id
GROUP BY l2., l2.line_item_id ；
Plan
--------------------------------------------------
SELECT STATEMENT
SORT GROUP BY
MERGE JOIN
SORT JOIN
TABLE ACCESS FULL ORDER_ITEMS
FILTER
SORT JOIN
TABLE ACCESS FULL ORDER_ITEMS
