Oracle SQL优化法则

################################################################

Oracle 语句优化５３个规则详解

################################################################

Oracle 语句优化５３个规则详解(1)

Oracle sql 性能优化调整 1. 选用适合的 ORACLE 优化器

 ORACLE 的优化器共有 3 种：

a. RULE （基于规则）

b. COST （基于成本）

 如果数据库的优化器模式设置为选择性（CHOOSE），那么实际的优化器模式将和是否

运行过 analyze 命令有关。 如果 table 已经被 analyze 过， 优化器模式将自动成为 CBO ， 反

之，数据库将采用 RULE 形式的优化器。

 在缺省情况下，ORACLE 采用 CHOOSE 优化器，为了避免那些不必要的全表扫描（full

table scan） ， 你必须尽量避免使用 CHOOSE 优化器，而直接采用基于规则或者基于成本

的优化器。

 2. 访问 Table 的方式 ORACLE 采用两种访问表中记录的方式：

 a. 全表扫描

 全 表扫描就是 顺 序地 访 问表 中 每条 记 录。 ORACLE 采用 一 次读 入 多个 数 据块

（database block）的方式优化全表扫描。

 为了不重复解析相同的 SQL 语句，在第一次解析之后， ORACLE 将 SQL 语句存放在内

存中。这块位于系统全局区域 SGA（system global area）的共享池（shared buffer pool）中

的内存可以被所有的数据库用户共享。 因此，当你执行一个 SQL 语句（有时被称为一个游

标）时，如果它和之前的执行过的语句完全相同， ORACLE 就能很快获得已经被解析的语

句以及最好的执行路径。 ORACLE 的这个功能大大地提高了 SQL 的执行性能并节省了内

存的使用。

 可惜的是 ORACLE 只对简单的表提供高速缓冲（cache buffering） ，这个功能并不适用

于多表连接查询。

 数据库管理员必须在 init.ora 中为这个区域设置合适的参数，当这个内存区域越大，就可

须完全相同（包括空格，换行等）。

 A. 字符级的比较：

 当前被执行的语句和共享池中的语句必须完全相同。

 例如：

 SELECT * FROM EMP；

 和下列每一个都不同

 SELECT * from EMP；

 Select * From Emp；

 SELECT * FROM EMP；

 B. 两个语句所指的对象必须完全相同：

 Jack sal_limit private synonym

　　　　Work_city public synonym

　　　　Plant_detail public synonym

　Jill sal_limit private synonym

　考虑一下下列 SQL 语句能否在这两个用户之间共享。

不能

每个用户都有一个 private synonym - sal_limit , 它们是不同的对象

两个用户访问相同的对象 public synonym - work_city

select a.sdesc,b.location from work_city a , plant_detail b where a.city_id = b.city_id

不能

 C. 两个 SQL 语句中必须使用相同的名字的绑定变量（bind variables）

 例如：第一组的两个 SQL 语句是相同的（可以共享），而第二组中的两个语句是不同的

（即使在运行时，赋于不同的绑定变量相同的值）

　 select pin ， name from people where pin = ：blk1.pin；

　 select pin ， name from people where pin = ：blk1.pin；

　b.

　select pin ， name from people where pin = ：blk1.ov_ind；

 ORACLE 的解析器按照从右到左的顺序处理 FROM 子句中的表名，因此 FROM 子句中

写在最后的表（基础表 driving table）将被最先处理。 在 FROM 子句中包含多个表的情况

下，你必须选择记录条数最少的表作为基础表。当 ORACLE 处理多个表时， 会运用排序

及合并的方式连接它们。首先，扫描第一个表（FROM 子句中最后的那个表）并对记录进

行派序，然后扫描第二个表（FROM 子句中最后第二个表），最后将所有从第二个表中检

索出的记录与第一个表中合适记录进行合并。

 例如：

 表 TAB2 1 条记录

 选择 TAB2 作为基础表 （最好的方法）

交叉表是指那个被其他表所引用的表。

 例如： EMP 表描述了 LOCATION 表和 CATEGORY 表的交集。

SELECT *

FROM LOCATION L ,

 CATEGORY C,

 EMP E

WHERE E.EMP_NO BETWEEN 1000 AND 2000

AND E.EMP_NO BETWEEN 1000 AND 2000

Oracle 语句优化５３个规则详解(2)

5. WHERE 子句中的连接顺序。 ORACLE 采用自下而上的顺序解析 WHERE 子句，