Oracle SQL优化

概述：

一、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条具有相同键值的记录,当然它们都是空!