Oracle 常用SQL语句优化方式

一、Oracle 优化器的两种方式

1.RBO(Rule-Based Optimization)基于原则的优化器
优化器在分析SQL语句时,所遵循的是Oracle内部预定的一些规则，对数据是不敏感的。它只借助少量的信息来 决定一个sql语句的执行计划，包括：
1）sql语句本身
2）sql中涉及到的table、view、index等的基本信息
3）本地数据库中数据字典中的信息（远程数据库数据字典信息对RBO是无效的）
2.CBO(Cost-Based Optimization)基于成本的优化器
通过代价引擎来估计每个执行计划所需的代价，该代价将每个执行计划所耗费的资源进行量化，CBO根据这个代价选择出最优的执行计划。一个查询所耗费的资源可分为三部分：
1）I/O代价：把数据从磁盘读入内存时所需代价（该代价是查询所需最主要的，所以在优化时一个基本原则就是降低I/O总次数）。
2）CPU代价：是指处理内存中数据所需的代价，数据一旦读入内存，当我们识别出我们所要的数据后，会在这些数据上执行排序（sort）或连接（join）操作，这需要消耗CPU资源。
3）NETWORK代价：对于访问远程节点来说，network代价的花费也是很大的。
优化器判断选用哪一种方式：
1）表及索引的统计信息。统计信息给出表的大小、有多少行、每行的长度等信息。这些统计信息起初在库内是没有的，是做Analyze(解析)后才出现的，很多的时侯过期统计信息会令优化器做出一个错误的执行计划,因些应及时更新这些信息。

二、SQL语句的常见优化方法

1.根据ORACLE解析器按照从右到左的顺序处理FROM子句中的表名的原理
FROM子句中写在最后的表将被最先处理. 可以分以下情况讨论：
1）在FROM子句中包含多个表的情况下, 你必须选择记录条数最少的表作为基础表.
2）当ORACLE处理多个表时, 会运用排序及合并 的方式连接它们.
首先,扫描第一个表(FROM子句中最后的那个表)并对记录进行排序,然后扫描第二个表(FROM子句中最后第二个表),最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并. 举例：
表 TAB1 的记录数远大于 表TAB2的
选择TAB2作为基础表 (最好的方法)
select count() from tab1,tab2
选择TAB2作为基础表 (不佳的方法)
select count() from tab2,tab1
注：如果有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指那个被其他表所引用的表.
2.根据ORACLE解析器采用自下而上的顺序解析where子句的原理
表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾.
3.共享sql语句，避免重复解析相同的sql语句，提高sql的执行效能。共用sql需要满足三个条件：
1）执行语句必须与共享池语句完全一样，包括(大小写，空格，换行等)。
2）两条语句所指的对象必须完全相同。
3）两个SQL语句绑定变量的名字必须相同。
4.SELECT子句中避免使用 ‘ * ’星号
当你想在SELECT子句中列出所有的COLUMN时,使用动态SQL列引用 ‘’ 是一个方便的方法.不幸的是,这是一个非常低效的方法. 实际上,ORACLE在解析的过程中, 会将’’ 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间.
5.减少访问数据库的次数
当执行每条SQL语句时, ORACLE在内部执行了许多工作: 解析SQL语句, 估算索引的利用率, 绑定变量 , 读数据块等等. 由此可见, 减少访问数据库的次数 , 就能实际上减少ORACLE的工作量.
方法1 (低效) -
SELECT EMP_NAME , SALARY , GRADE FROM EMP
WHERE EMP_NO = 123;
SELECT EMP_NAME , SALARY , GRADE FROM EMP
WHERE EMP_NO = 456;
方法2 (高效)
SELECT A.EMP_NAME , A.SALARY , A.GRADE, B.EMP_NAME , B.SALARY , B.GRADE
FROM EMP A,EMP B
WHERE A.EMP_NO = 123
AND B.EMP_NO = 456;
6.Oracle数据库访问表中记录的方式有三种：
1）全表扫描（对于较大的表上尽量避免使用全表扫描）
2）通过ROWID访问表（Oracle存取单行数据的最快方法）
3）索引扫描（索引的正确使用可以有效地提升SQL语句的查询效率，但是如果有太多的索引，DML 的性能就会受到影响，而索引太少， 又会影响查询（包括插入、 更新和刪除） 的性能）
7.减少使用having
用Where子句替换HAVING子句，避免使用HAVING子句, HAVING 只会在检索出所有记录之后才对结果集进行过滤. 这个处理需要排序,总计等操作. 如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销.
8.使用表的别名
当在sql语句中连接对个表的时候，请使用表的别名并把别名放在每个column的前面点出来；这样一来就可以减少解析的时间并且减少由column歧义引起的语法错误。
9.用exists可替换distinct；用表连接可替换exists。
10.用 UNION-ALL 替换UNION
当 SQL 语句需要 UNION 两个查询结果集合时，這两个结果集合会以 UNION-ALL 的方式被合并， 然后在输出最终结果前進行排序。 如果用 UNION ALL 替代 UNION ， 这样排序就不是必要了，效率就会因此得到提高。
11.用exists(not exists)代替in(not in)
在子查询中，not in子句讲执行一个内部的排序和合并，而且对于子查询中的表执行一个全表遍历，所以效率比较低
12.索引方面的使用
1）索引只能告诉你什么存在于表中，而不能告诉你什么不存在表中。因此使用 ‘!=’ 将不使用索引，所以尽量避免使用 ‘!=’ 在sql语句中。
2）‘||’是字符连接符，在sql语句中将通知使用索引。
3）‘+’是数学函数，其他类似的数学函数也一样，在sql语句中停止使用索引
4）相同的索引列不能相互比较，这将会启动全表扫描；例如：
不使用索引:
SELECT ACCOUNT_NAME, AMOUNT FROM TRANSACTION
WHERE ACCOUNT_NAME = NVL(:ACC_NAME,ACCOUNT_NAME);
使用索引:
SELECT ACCOUNT_NAME, AMOUNT FROM TRANSACTION
WHERE ACCOUNT_NAME LIKE NVL(:ACC_NAME,’%’);
5）ORDER BY 子句只在两种严格的条件下使用索引：
（1）ORDER BY中所有的列必须包含在相同的索引中并保持在索引中的排列顺序.
（2）ORDER BY中所有的列必须定义为非空.
6）如果索引是建立在多个列上, 只有在它的第一个列被where子句引用时,优化器才会选择使用该索引.
7）避免在索引列上使用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;
8）避免在索引列上面使用计算
where子句中，如果索引列是函数的一部分，优化器将不使用索引而使用全表扫描
例如：
select * from DEPT where sal*2<10000(低效)
select * from DEPT where sal<10000/2(高效)