概述:
一, Oracle 的优化器
Oracle 的优化器共有两种模式: RBO (基于规则)和 CBO(基于成本).
RBO 方式: 优化器在分析 SQL 语句时, 所遵循的是 Oracle 内部预定的一些规则. 比如我们常见的, 当一个 where 子句中的一列有索引时去走索引.
CBO 方式: 它是看语句的代价(Cost), 这里的代价主要指 CPU 和内存. 优化器在判断是否用这种方式时, 主要参照的是表及索引的统计信息. 统计信息给出表的大小, 有少行, 每行的长度等信息. 这些统计信息起初在库内是没有的, 是通过对表进行分析后才出现的, 很多时侯过期统计信息会令优化器做出一个错误的执行计划, 因些应及时更新这些信息.
CBO 和 RBO 作为不同的 SQL 优化器, 对 SQL 语句的执行计划产生重大影响.
二, SQL 优化的一般性原则:
目标:
减少服务器资源消耗(主要是磁盘 IO);
设计方面:
尽量依赖 oracle 的优化器, 并为其提供条件;
合适的索引, 索引的双重效应, 列的选择性;
编码方面:
利用索引, 避免大表 FULL TABLE SCAN;
合理使用临时表;
避免写过于复杂的 sql, 不一定非要一个 sql 解决问题;
在不影响业务的前提下减小事务的粒度
三, 具体如何优化, 举例?
1, 通常我们在 SELECT 子句中列出所有的 COLUMN 时, 使用动态 SQL 列引用 '*' 是一个方便的方法. 不幸的是, 这是一个非常低效的方法.
实际上, ORACLE 在解析的过程中, 会将'*' 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间.
2, 带有 DISTINCT,UNION,MINUS,INTERSECT,ORDER BY 的 SQL 语句会启动 SQL 引擎执行耗费资源的排序 (SORT) 功能. DISTINCT 需要一次排序操作,
而其他的至少需要执行两次排序. 例如, 一个 UNION 查询, 其中每个查询都带有 GROUP BY 子句, GROUP BY 会触发嵌入排序(NESTED SORT) ;
这样, 每个查询需要执行一次排序, 然后在执行 UNION 时, 又一个唯一排序 (SORT UNIQUE) 操作被执行而且它只能在前面的嵌入排序结束后才能
开始执行. 嵌入的排序的深度会大大影响查询的效率. 通常, 带有 UNION, MINUS , INTERSECT 的 SQL 语句都可以用其他方式重写.
3, 给优化器更明确的命令
[1] 如果表中有两个以上 (包括两个) 索引, 其中有一个唯一性索引, 而其他是非唯一性.在这种情况下, ORACLE 将使用唯一性索引而完全忽略非唯
一性索引.
[2] WHERE 子句中, 如果索引列是函数的一部分.优化器将不使用索引而使用全表扫描.
如:
低效:
SELECT ...
- FROM DEPT
- WHERE SAL * 12> 25000;
高效:
SELECT ...
- FROM DEPT
- WHERE SAL > 25000/12;
4,WHERE 子句中, 如果索引列所对应的值的第一个字符由通配符 (WILDCARD) 开始, 索引将不被采用. 这种情况下通常都是全表索引.
如:
SELECT USER_NO,USER_NAME,ADDRESS FROM USER_FILES WHERE USER_NO LIKE '%109204421';
5, 通常, 我们要避免在索引列上使用 NOT, NOT 会产生在和在索引列上使用函数相同的影响. 当 ORACLE"遇到"NOT, 他就会停止使用索引转而执行全表扫描.
如:
低效: (这里, 不使用索引)
SELECT ...
- FROM DEPT
- WHERE DEPT_CODE NOT = 0;
高效: (这里, 使用了索引)
SELECT ...
- FROM DEPT
- WHERE DEPT_CODE> 0;
6, 任何在 where 子句中使用 is null 或 is not null 的语句优化器是不允许使用索引的.
如果唯一性索引建立在表的 A 列和 B 列上, 并且表中存在一条记录的 A,B 值为 (123,null) , ORACLE 将不接受下一条具有相同 A,B 值(123,null) 的记录(插 入). 然而如果所有的索引列都为空, ORACLE 将认为整个键值为空而空不等于空. 因此你可以插入 1000 条具有相同键值的记录, 当然它们都是空!
来源: http://www.bubuko.com/infodetail-3016602.html