oracle SQL性能优化

（ 1 ）       选择 最有效率的表名 顺 序 ( 只在基于 规则 的 优 化器中有效 ) ：

ORACLE 的解析器按照从右到左的 顺 序 处 理 FROM 子句中的表名， FROM 子句中写在最后的表 ( 基 础 表 driving table) 将被最先 处 理，在 FROM 子句中包含多个表的情况下 , 你必 须选择记录 条数最少的表作 为 基 础 表。如果有 3 个以上的表 连 接 查询 , 那就需要 选择 交叉表 (intersection table) 作 为 基 础 表 , 交叉表是指那个被其他表所引用的表 .

（ 2 ）       WHERE 子句中的 连 接 顺 序．：

ORACLE 采用自下而上的 顺 序解析 WHERE 子句 , 根据 这 个原理 , 表之 间 的 连 接必 须 写在其他 WHERE 条件之前 , 那些可以 过滤 掉最大数量 记录 的条件必 须 写在 WHERE 子句的末尾 .

（ 3 ）       SELECT 子句中避免使用 ‘ * ‘ ：

ORACLE 在解析的 过 程中 , 会将 '*' 依次 转换 成所有的列名 , 这 个工作是通 过查询 数据字典完成的 , 这 意味着将耗 费 更多的 时间

（ 4 ）       减少 访问 数据 库 的次数：

ORACLE 在内部 执 行了 许 多工作 : 解析 SQL 语 句 , 估算索引的利用率 , 绑 定 变 量 , 读 数据 块 等；

（ 5 ）       在 SQL*Plus , SQL*Forms 和 Pro*C 中重新 设 置 ARRAYSIZE 参数 , 可以增加 每 次数据 库访问 的 检 索数据量 , 建 议值为 200

（ 6 ）       使用 DECODE 函数来减少 处 理 时间 ：

使用 DECODE 函数可以避免重 复扫 描相同 记录 或重 复连 接相同的表 .

（ 7 ）       整合 简单 , 无 关联 的数据 库访问 ：

如果你有几个 简单 的数据 库查询语 句 , 你可以把它 们 整合到一个 查询 中 ( 即使它 们 之 间 没有 关 系 )

（ 8 ）       删 除重 复记录 ：

最高效的 删 除重 复记录 方法 ( 因 为 使用了 ROWID) 例子：

DELETE  FROM  EMP E  WHERE  E.ROWID > (SELECT MIN(X.ROWID)
FROM  EMP X  WHERE  X.EMP_NO = E.EMP_NO);

（ 9 ）       用 TRUNCATE 替代 DELETE ：

当 删 除表中的 记录时 , 在通常情况下 , 回 滚 段 (rollback segments ) 用来存放可以被恢 复 的信息 . 如果你没有 COMMIT 事 务 ,ORACLE 会将数据恢 复 到 删 除之前的状 态 ( 准确地 说 是恢 复 到 执 行 删 除命令之前的状况 ) 而当运用 TRUNCATE 时 , 回 滚 段不再存 放任何可被恢 复 的信息 . 当命令运行后 , 数据不能被恢 复 . 因此很少的 资 源被 调 用 , 执 行 时间 也会很短 . ( 译 者按 : TRUNCATE 只在 删 除全表适用 ,TRUNCATE 是 DDL 不是 DML)

（ 10 ）   尽量多使用 COMMIT ：

只要有可能 , 在程序中尽量多使用 COMMIT, 这样 程序的性能得到提高 , 需求也会因 为 COMMIT 所 释 放的 资 源而减少 :
COMMIT 所 释 放的 资 源 :
a. 回 滚 段上用于恢 复 数据的信息 .
b. 被程序 语 句 获 得的 锁
c. redo log buffer 中的空 间
d. ORACLE 为 管理上述 3 种资 源中的内部花 费

（ 11 ）   用 Where 子句替 换 HAVING 子句：

避免使用 HAVING 子句 , HAVING 只会在 检 索出所有 记录 之后才 对结 果集 进 行 过滤 . 这 个 处 理需要排序 , 总计 等操作 . 如果能通 过 WHERE 子句限制 记录 的数目 , 那就能减少 这 方面的 开销 . ( 非 oracle 中 ) on 、 where 、 having 这 三个都可以加条件的子句中， on 是最先 执 行， where 次之， having 最后，因 为 on 是先把不符合条件的 记录过滤 后才 进 行 统计 ，它就可以减少中 间 运算要 处 理的数据，按理 说应该 速度是最快的， where 也 应该 比 having 快点的，因 为 它 过滤 数据后才 进 行 sum ，在两个表 联 接 时 才用 on 的，所以在一个表的 时 候，就剩下 where 跟 having 比 较 了。在 这单 表 查询统计 的情况下，如果要 过滤 的条件没有 涉 及到要 计 算字段，那它 们 的 结 果是一 样 的，只是 where 可以使用 rushmore 技 术 ，而 having 就不能，在速度上后者要慢如果要 涉 及到 计 算的字段，就表示在没 计 算之前， 这 个字段的 值 是不确定的，根据上篇写的工作流程， where 的作用 时间 是在 计 算之前就完成的，而 having 就是在 计 算后才起作用的，所以在 这种 情况下，两者的 结 果会不同。在多表 联 接 查询时 ， on 比 where 更早起作用。系 统 首先根据各个表之 间 的 联 接条件，把多个表合成一个 临时 表后，再由 where 进 行 过滤 ，然后再 计 算， 计 算完后再由 having 进 行 过滤 。由此可 见 ，要想 过滤 条件起到正确的作用，首先要明白 这 个条件 应该 在什 么时 候起作用，然后再决定放在那里

（ 12 ）   减少 对 表的 查询 ：

在含有子 查询 的 SQL 语 句中 , 要特 别 注意减少 对 表的 查询 . 例子：

     SELECT  TAB_NAME FROM TABLES WHERE (TAB_NAME,DB_VER) = ( SELECT

TAB_NAME,DB_VER  FROM  TAB_COLUMNS   WHERE  VERSION = 604)

（ 13 ）   通 过 内部函数提高 SQL 效率 . ：

复杂 的 SQL 往往 牺 牲了 执 行效率 . 能 够 掌握上面的运用函数解决 问题 的方法在 实际 工作中是非常有意 义 的

（ 14 ）   使用表的 别 名 (Alias) ：

当在 SQL 语 句中 连 接多个表 时 , 请 使用表的 别 名并把 别 名前 缀 于 每 个 Column 上 . 这样 一来 , 就可以减少解析的 时间 并减少那些由 Column 歧 义 引起的 语 法 错误 .

（ 15 ）   用 EXISTS 替代 IN 、用 NOT EXISTS 替代 NOT IN ：

在 许 多基于基 础 表的 查询 中 , 为 了 满 足一个条件 , 往往需要 对 另一个表 进 行 联 接 . 在 这种 情况下 , 使用 EXISTS( 或 NOT EXISTS) 通常将提高 查询 的效率 . 在子 查询 中 ,NOT IN 子句将 执 行一个内部的排序和合并 . 无 论 在哪 种 情况下 ,NOT IN 都是最低效的 ( 因 为 它 对 子 查询 中的表 执 行了一个全表遍 历 ). 为 了避免使用 NOT IN , 我 们 可以把它改写成外 连 接 (Outer Joins) 或 NOT EXISTS.

例子：

（ 高效 ） SELECT * FROM  EMP ( 基 础 表 )   WHERE  EMPNO > 0   AND   EXISTS ( SELECT ‘X'   FROM DEPT   WHERE  DEPT.DEPTNO = EMP.DEPTNO   AND  LOC = ‘MELB')

( 低效 ) SELECT  * FROM  EMP ( 基 础 表 )   WHERE  EMPNO > 0   AND  DEPTNO IN (SELECT DEPTNO   FROM  DEPT   WHERE  LOC = ‘MELB' )

（ 16 ）   识别 ' 低效 执 行 ' 的 SQL 语 句：

虽 然目前各 种关 于 SQL 优 化的 图 形化工具 层 出不 穷 , 但是写出自己的 SQL 工具来解决 问题 始 终 是一个最好的方法：

SELECT  EXECUTIONS , DISK_READS, BUFFER_GETS,
ROUND ((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2 ) Hit_radio,
ROUND (DISK_READS/EXECUTIONS,2) Reads_per_run,
SQL_TEXT
FROM  V$SQLAREA
WHERE  EXECUTIONS>0
AND  BUFFER_GETS > 0
AND  (BUFFER_GETS-DISK_READS)/BUFFER_GETS < 0.8
ORDER BY   4 DESC ;

（ 17 ）   用索引提高效率：

索引是表的一个概念部分 , 用来提高 检 索数据的效率， ORACLE 使用了一个 复杂 的自平衡 B-tree 结 构 . 通常 , 通 过 索引 查询 数据比全表 扫 描要快 . 当 ORACLE 找出 执 行 查询 和 Update 语 句的最佳路径 时 , ORACLE 优 化器将使用索引 . 同 样 在 联结 多个表 时 使用索引也可以提高效率 . 另一个使用索引的好 处 是 , 它提供了主 键 (primary key) 的唯一性 验证 . 。那些 LONG 或 LONG RAW 数据 类 型 , 你可以索引几乎所有的列 . 通常 , 在大型表中使用索引特 别 有效 . 当然 , 你也会 发现 , 在 扫 描小表 时 , 使用索引同 样 能提高效率 . 虽 然使用索引能得到 查询 效率的提高 , 但是我 们 也必 须 注意到它的代价 . 索引需要空 间 来存 储 , 也需要定期 维护 , 每 当有 记录 在表中增减或索引列被修改 时 , 索引本身也会被修改 . 这 意味着 每 条 记录 的 INSERT , DELETE , UPDATE 将 为 此多付出 4 , 5 次的磁 盘 I/O . 因 为 索引需要 额 外的存 储 空 间 和 处 理 , 那些不必要的索引反而会使 查询 反 应时间变 慢 . 。 定期的重构索引是有必要的 . ：

ALTER   INDEX <INDEXNAME> REBUILD <TABLESPACENAME>

（ 18 ）   用 EXISTS 替 换 DISTINCT ：

当提交一个包含一 对 多表信息 ( 比如部 门 表和雇 员 表 ) 的 查询时 , 避免在 SELECT 子句中使用 DISTINCT. 一般可以考 虑 用 EXIST 替 换 , EXISTS 使 查询 更 为 迅速 , 因 为 RDBMS 核心模 块 将在子 查询 的条件一旦 满 足后 , 立刻返回 结 果 . 例子：

       ( 低效 ):
SELECT   DISTINCT  DEPT_NO,DEPT_NAME   FROM  DEPT D , EMP E

WHERE  D.DEPT_NO = E.DEPT_NO
( 高效 ):
SELECT  DEPT_NO,DEPT_NAME   FROM  DEPT D   WHERE   EXISTS ( SELECT ‘X'
FROM  EMP E   WHERE  E.DEPT_NO = D.DEPT_NO ) ;

（ 19 ）   sql 语 句用大写的 ；因 为 oracle 总 是先解析 sql 语 句，把小写的字母 转换 成大写的再 执 行

（ 20 ）   在 java 代 码 中尽量 少用 连 接符 “ ＋ ” 连 接字符串 ！

（ 21 ）   避免在索引列上使用 NOT 通常 ，　

我 们 要避免在索引列上使用 NOT, NOT 会 产 生在和在索引列上使用函数相同的影响 . 当 ORACLE” 遇到 ”NOT, 他就会停止使用索引 转 而 执 行全表 扫 描 .

（ 22 ）   避免在索引列上使用 计 算．
WHERE 子句中，如果索引列是函数的一部分． 优 化器将不使用索引而使用全表 扫 描．
举 例 :
低效：
SELECT … FROM  DEPT  WHERE SAL * 12 > 25000;
高效 :
SELECT … FROM DEPT WHERE SAL > 25000/12;

（ 23 ）   用 >= 替代 >

高效 :
SELECT * FROM  EMP  WHERE  DEPTNO >=4
低效 :
SELECT * FROM EMP WHERE DEPTNO >3
两者的区 别 在于 , 前者 DBMS 将直接跳到第一个 DEPT 等于 4 的 记录 而后者将首先定位到 DEPTNO=3 的 记录 并且向前 扫 描到第一个 DEPT 大于 3 的 记录 .

（ 24 ）   用 UNION 替 换 OR ( 适用于索引列 )

通常情况下 , 用 UNION 替 换 WHERE 子句中的 OR 将会起到 较 好的效果 . 对 索引列使用 OR 将造成全表 扫 描 . 注意 , 以上 规则 只 针对 多个索引列有效 . 如果有 column 没有被索引 , 查询 效率可能会因 为 你没有 选择 OR 而降低 . 在下面的例子中 , LOC_ID 和 REGION 上都建有索引 .
高效 :
SELECT LOC_ID , LOC_DESC , REGION
FROM LOCATION
WHERE LOC_ID = 10
UNION
SELECT LOC_ID , LOC_DESC , REGION
FROM LOCATION
WHERE REGION = “MELBOURNE”
低效 :
SELECT LOC_ID , LOC_DESC , REGION
FROM LOCATION
WHERE LOC_ID = 10 OR REGION = “MELBOURNE”
如果你 坚 持要用 OR, 那就需要返回 记录 最少的索引列写在最前面 .

（ 25 ）   用 IN 来替 换 OR  

这 是一条 简单 易 记 的 规则 ，但是 实际 的 执 行效果 还须检验 ，在 ORACLE8i 下，两者的 执 行路径似乎是相同的．　

低效 :
SELECT …. FROM LOCATION WHERE LOC_ID = 10 OR LOC_ID = 20 OR LOC_ID = 30
高效
SELECT … FROM LOCATION WHERE LOC_IN   IN (10,20,30);

（ 26 ）   避免在索引列上使用 IS NULL 和 IS NOT NULL

避免在索引中使用任何可以 为 空的列， ORACLE 将无法使用 该 索引． 对 于 单 列索引，如果列包含空 值 ，索引中将不存在此 记录 . 对 于 复 合索引，如果 每 个列都 为 空，索引中同 样 不存在此 记录 . 　如果至少有一个列不 为 空， 则记录 存在于索引中． 举 例 : 如果唯一性索引建立在表的 A 列和 B 列上 , 并且表中存在一条 记录 的 A,B 值为 (123,null) , ORACLE 将不接受下一条具有相同 A,B 值 （ 123,null ）的 记录 ( 插入 ). 然而如果所有的索引列都 为 空， ORACLE 将 认为 整个 键值为 空而空不等于空 . 因此你可以插入 1000 条具有相同 键值 的 记录 , 当然它 们 都是空 ! 因 为 空 值 不存在于索引列中 , 所以 WHERE 子句中 对 索引列 进 行空 值 比 较 将使 ORACLE 停用 该 索引 .

低效 : ( 索引失效 )
SELECT … FROM  DEPARTMENT   WHERE  DEPT_CODE IS NOT NULL ;
高效 : ( 索引有效 )
SELECT … FROM  DEPARTMENT   WHERE  DEPT_CODE >= 0;

（ 27 ）   总 是使用索引的第一个列：

如果索引是建立在多个列上 , 只有在它的第一个列 (leading column) 被 where 子句引用 时 , 优 化器才会 选择 使用 该 索引 . 这 也是一条 简单 而重要的 规则 ，当 仅 引用索引的第二个列 时 , 优 化器使用了全表 扫 描而忽略了索引

（ 28 ）   用 UNION-ALL 替 换 UNION ( 如果有可能的 话 ) ：

当 SQL 语 句需要 UNION 两个 查询结 果集合 时 , 这 两个 结 果集合会以 UNION-ALL 的方式被合并 , 然后在 输 出最 终结 果前 进 行排序 . 如果用 UNION ALL 替代 UNION, 这样 排序就不是必要了 . 效率就会因此得到提高 . 需要注意的是 ， UNION ALL 将重 复输 出两个 结 果集合中相同 记录 . 因此各位 还 是要从 业务 需求分析使用 UNION ALL 的可行性 . UNION 将 对结 果集合排序 , 这 个操作会使用到 SORT_AREA_SIZE 这块 内存 . 对 于 这块 内存的 优 化也是相当重要的 . 下面的 SQL 可以用来 查询 排序的消耗量

低效：
SELECT  ACCT_NUM, BALANCE_AMT
FROM  DEBIT_TRANSACTIONS
WHERE TRAN_DATE = '31-DEC-95'
UNION
SELECT ACCT_NUM, BALANCE_AMT
FROM DEBIT_TRANSACTIONS
WHERE TRAN_DATE = '31-DEC-95'
高效 :
SELECT ACCT_NUM, BALANCE_AMT
FROM DEBIT_TRANSACTIONS
WHERE TRAN_DATE = '31-DEC-95'
UNION ALL
SELECT ACCT_NUM, BALANCE_AMT
FROM DEBIT_TRANSACTIONS
WHERE TRAN_DATE = '31-DEC-95'

（ 29 ）   用 WHERE 替代 ORDER BY ：

ORDER BY 子句只在两 种严 格的条件下使用索引 .
ORDER BY 中所有的列必 须 包含在相同的索引中并保持在索引中的排列 顺 序 .
ORDER BY 中所有的列必 须 定 义为 非空 .
WHERE 子句使用的索引和 ORDER BY 子句中所使用的索引不能并列 .

例如 :
表 DEPT 包含以下列 :
DEPT_CODE PK NOT NULL
DEPT_DESC NOT NULL
DEPT_TYPE NULL

低效 : ( 索引不被使用 )
SELECT DEPT_CODE  FROM  DEPT   ORDER BY  DEPT_TYPE
高效 : ( 使用索引 )
SELECT DEPT_CODE   FROM  DEPT   WHERE  DEPT_TYPE > 0

（ 30 ）   避免改 变 索引列的 类 型 .:

当比 较 不同数据 类 型的数据 时 , ORACLE 自 动对 列 进 行 简单 的 类 型 转换 .

假 设 EMPNO 是一个数 值类 型的索引列 .
SELECT …   FROM EMP   WHERE   EMPNO = ‘123'
实际 上 , 经过 ORACLE 类 型 转换 , 语 句 转 化 为 :
SELECT …   FROM EMP  WHERE  EMPNO = TO_NUMBER(‘123')
幸运的是 , 类 型 转换 没有 发 生在索引列上 , 索引的用途没有被改 变 .
现 在 , 假 设 EMP_TYPE 是一个字符 类 型的索引列 .
SELECT …   FROM EMP   WHERE EMP_TYPE = 123
这 个 语 句被 ORACLE 转换为 :
SELECT …   FROM EMP   WHERETO_NUMBER(EMP_TYPE)=123
因 为 内部 发 生的 类 型 转换 , 这 个索引将不会被用到 ! 为 了避免 ORACLE 对 你的 SQL 进 行 隐 式的 类 型 转换 , 最好把 类 型 转换 用 显 式表 现 出来 . 注意当字符和数 值 比 较时 , ORACLE 会 优 先 转换 数 值类 型到字符 类 型

（ 31 ）   需要当心的 WHERE 子句 :

某些 SELECT 语 句中的 WHERE 子句不使用索引 . 这 里有一些例子 .
在下面的例子里 , (1) ‘!=' 将不使用索引 . 记 住 , 索引只能告 诉 你什 么 存在于表中 , 而不能告 诉 你什 么 不存在于表中 . (2) ‘||' 是 字符 连 接函数 . 就象其他函数那 样 , 停用了索引 . (3) ‘+' 是数学函数 . 就象其他数学函数那 样 , 停用了索引 . (4) 相同的索引列不能互相比 较 , 这 将会启用全表 扫 描 .

（ 32 ）   a. 如果 检 索数据量超 过 30% 的表中 记录 数 . 使用索引将没有 显 著的效率提高 .
b. 在特定情况下 , 使用索引也 许 会比全表 扫 描慢 , 但 这 是同一个数量 级 上的区 别 . 而通常情况下 , 使用索引比全表 扫 描要 块 几倍乃至几千倍 !

（ 33 ）   避免使用耗 费资 源的操作 :

带 有 DISTINCT,UNION,MINUS,INTERSECT,ORDER BY 的 SQL 语 句会启 动 SQL 引擎
执 行耗 费资 源的排序 (SORT) 功能 . DISTINCT 需要一次排序操作 , 而其他的至少需要 执 行两次排序 . 通常 , 带 有 UNION, MINUS , INTERSECT 的 SQL 语 句都可以用其他方式重写 . 如果你的数据 库 的 SORT_AREA_SIZE 调 配得好 , 使用 UNION , MINUS, INTERSECT 也是可以考 虑 的 , 毕 竟它 们 的可 读 性很 强

（ 34 ）   优 化 GROUP BY:

提高 GROUP BY 语 句的效率 , 可以通 过 将不需要的 记录 在 GROUP BY 之前 过滤 掉 . 下面两个 查询 返回相同 结 果但第二个明 显 就快了 许 多 .

低效 :
SELECT JOB , AVG(SAL)
FROM EMP
GROUP JOB
HAVING JOB = ‘PRESIDENT'
OR JOB = ‘MANAGER'
高效 :
SELECT JOB , AVG(SAL)
FROM EMP
WHERE JOB = ‘PRESIDENT'
OR JOB = ‘MANAGER'
GROUP JOB