Oracle 语句优化

一篇读书笔记. 自己做的摘要和总结, 再加上其他地方看到的优化方法.

源文地址:    http://blog.csdn.net/a286352250/article/details/20296293

1.选用适合的Oracle优化器 Oracle的优化器共有3种： a.RULE(基于规则) b.COST(基于成本) c.CHOOSE(选择性). 缺省的优化器，可以通过对init.ora文件中OPTIMIZER_MODE
, 成本的优化器(CBO，Cost-Based Optimizer)，你必须经常运行analyze命令，以增加数据库中的对象统计信息(object statistics)的准确性。 
如果选择choose, 若表被analyze过,优化器模式成为CBO, 反之采用rule形式的优化.

2. 访问table的方式 a)全表扫描 b) 通过Rowid, 记录物理位置信息.
oracle采用index实现数据和存储数据的物理位置rowid之间的联系.

3. 共享SQL语句. 全局区域SGA(system global area)的共享池(hare buffer pool)被所有数据库用户共享. 共对单表实现提供高速缓冲(cache buffering) , SQL语句必须完全一样, 包括大小写, 绑定变量名, 对象

4. 选择最有效率的表名顺序, 字段最少的表放from的最后面.from最后的表也叫基础表.

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

6. SELECT子句中避免使用‘ * ’ 
Oracle在解析的过程中，会将‘*’依次转换成所有的列名，这个工作是通过查询数据字典完成的，这意味着将耗费更多的时间

7.减少访问数据库的次数, 整合简单无关联的SQL
高效SQL:
SELECT A.EMP_NAME，A.SALARY，A.GRADE， 
B.EMP_NAME， B.SALARY， B.GRADE 
FROM EMP A，EMP B 
WHERE A.EMP_NO = 342 
AND B.EMP_NO = 291;  

8.使用DECODE函数来减少处理时间 
使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表。


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


10.删除重复记录 
最高效的删除重复记录方法(因为使用了ROWID) 
DELETE FROM EMP E 
WHERE E.ROWID > (SELECT MIN(X.ROWID) 
FROM EMP X 
WHERE X.EMP_NO = E.EMP_NO);  

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


12.尽量多使用COMMIT 
commit后会释放资源. 

 a.回滚段上用于恢复数据的信息。 
b.被程序语句获得的锁 
c.redo log buffer中的空间 
d.Oracle为管理上述3种资源中的内部花费

13. 计算记录条数 
count(*) 比count(1)稍快，当然如果可以通过索引检索，对索引列的计数仍旧是最快的

14.用Where子句替换HAVING子句 
避免使用HAVING子句，HAVING 只会在检索出所有记录之后才对结果集进行过滤。这个处理需要排序，总计等操作。如果能通过WHERE子句限制记录的数目，那就能减少这方面的开销。

15.减少对表的查询 
在含有子查询的SQL语句中，要特别注意减少对表的查询。 
高效:;

SELECT TAB_NAME 
FROM TABLES 
WHERE (TAB_NAME，DB_VER) 
= ( SELECT TAB_NAME，DB_VER) 
FROM TAB_COLUMNS 
WHERE VERSION = 604) 

16.通过内部函数提高SQL效率 

17.使用表的别名(Alias) 

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

18.用EXISTS替代IN 

19.用NOT EXISTS替代NOT IN 

20.用表连接替换EXISTS 

21.用EXISTS替换DISTINCT 

22.SQL工具识别‘低效执行’的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;  

23.使用TKPROF工具来查询SQL性能状态 
SQL trace工具收集正在执行的SQL的性能状态数据并记录到一个跟踪文件中。这个跟踪文件提供了许多有用的信息，例如解析次数。执行次数，CPU使用时间等

24.用EXPLAIN PLAN 分析SQL语句 
EXPLAIN PLAN是一个很好的分析SQL语句的工具，它甚至可以在不执行SQL的情况下分析语句

25.用索引提高效率 

26.索引的操作(唯一性索引更快) 

27.基础表的选择 
基础表(Driving Table)是指被最先访问的表(通常以全表扫描的方式被访问)。根据优化器的不同，SQL语句中基础表的选择是不一样的。 
如果你使用的是CBO (COST BASED OPTIMIZER)，优化器会检查SQL语句中的每个表的物理大小，索引的状态，然后选用花费最低的执行路径。 
如果你用RBO (RULE BASED OPTIMIZER)，并且所有的连接条件都有索引对应，在这种情况下，基础表就是FROM 子句中列在最后的那个表。blog 

28.多个平等的索引 
不同表中两个想同等级的索引将被引用，FROM子句中表的顺序将决定哪个会被率先使用。FROM子句中最后的表的索引将有最高的优先级。 
如果相同表中两个想同等级的索引将被引用，WHERE子句中最先被引用的索引将有最高的优先级。

29.等式比较和范围比较 
当WHERE子句中有索引列，Oracle不能合并它们，Oracle将用范围比较。 
SELECT ENAME 
FROM EMP 
WHERE DEPTNO > 20 
AND EMP_CAT = ‘A’;  
这里只有EMP_CAT索引被用到，然后所有的记录将逐条与DEPTNO条件进行比较

30.不明确的索引等级 
当Oracle无法判断索引的等级高低差别，优化器将只使用一个索引，它就是在WHERE子句中被列在最前面的。 

31. 索引的使用

索引是表的一部分, 提升检索数据的效率.提供了主键的唯一性验证

索引需要空间, 需要维护, 数据增删的时候, 索引也会相应的变更操作

索引上避免计算操作, 避免 is null, null,  避免not, 三种情况下,都不会使用索引.

使用通配符%可能会停用索引.

避免改变索引的类型

32. Union all 替换OR(索引列)

Union all 替换Union

Order by 加在索引列上, 最好是PK上.