Oracle 查询优化

1、SELECT 子句避免使用‘*’ ：ORACLE在解析过程中通过查询字典将‘*’一次转换成所有的列名。

2、WHERE子句中的链接顺序 ：ORACLE采用自右向左顺序解析，过滤大数据量记录的条件卸载WHERE子句末尾，CPU占用率会降低很多。

3、选择最有效的表名顺序 ：把记录少的表放在FROM子句的最后面一个表（这是符合ORACLE机制的，因为如果对表进行了统计分析，
ORACLE会自动先进小表的链接，再进行大表的链接）

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

5、减少访问数据库的次数 ： 当执行每条SQL语句时ORACLE在内部解析SQL语句，估算索引的利用率, 绑定变量 , 读数据块等等。

这就涉及到SQL书写带来的影响，同一条SQL得到的结果集相同，但是写法不同就会增加对数据库的访问次数，产生多次硬解析，提高了成本，降低了性能（关于软解析与硬解析的区别以后会详细分析，这里就不具体说明了）

6、用TRUNCATE替代DELETE ：

DELETE删除数据时，回滚段里存放了可恢复的信息，若没有执行COMMIT事务，ORACLE会将数据恢复到执行删除命令之前的状况

TRUNCATE操作之后数据无法恢复，因为没有在回滚段交互，执行时间很短（TRUNCATE是DDL不是DML）

7、尽可能多的使用COMMIT，以释放资源提高性能（这些资源包括：回滚段上用于恢复数据的信息；背程序语句获得的锁；redo log buffer中的空间；ORACLE内部CUP的消耗）

8、用WHERE 子句替换HAVING子句 ：where是用于过滤行的，而having是用来过滤组的，因为行被分组后，having 才能过滤组，所以尽量用户 WHERE 过滤

9、用表连接替换EXISTS     用EXISTS替代IN . NOT EXISTS替代NOT IN ：

Exists 只检查行的存在，而 IN检查实际值

对于用 IN 的 SQL 语句 ORACLE 总是试图将其转换成多个表的连接，如果转换不成功则先执行 IN里面的子查询，再查询外层的表记录
如果转换成功就转换成多个表的连接。因此 用 IN 的 SQL 语句总是多了 一个转换的过程。

NOT IN是不走索引的

10、用EXISTS替换DISTINCT ：EXISTS 使查询更为迅速,因为RDBMS核心模块在子查询的条件一旦满足后立刻返回结果.，DISTINCT会先进行排序,然后会根据排序后的顺序去除相同的行。  例：

1.低效:SELECT DISTINCT DEPT_NO,DEPT_NAME FROM DEPT D,EMP E WHERE D.DEPT_NO = E.DEPT_NO ;
2.高效: SELECT DEPT_NO,DEPT_NAME  FROM DEPT D  WHERE EXISTS ( SELECT * FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO);

11、使用显式的游标(CURSOR) ：使用隐式的游标,将会执行两次操作，第一次检索记录, 第二次检查TOO MANY ROWS 这个exception， 而显式游标不执行第二次操作。

12、在进行多表关联时，多用 Where 语句把单个表的结果集最小化，多用聚合函数汇总结果集后再与其它表做关联，以使结果集数据量最小化。

13、在两张表进行关联时，应考虑可否使用右连接。以提高查询速度。

14、LIKE操作符 ：遇到 需要用到 LIKE 过滤的SQL语句，完全可以用 instr 代替。处理速度将显著提高。

15、where子句避免在索引列上使用计算或函数：

substr(no,1,4)   -->  no like '5400%'

16、尽量使用package ：第一次调用时将整个包load进内存，提高性能。

尽量使用cached sequences  生成primary key ： 提高主键生成速度和使用的性能。

17、 用索引提高效率 ：通常，通过索引查询数据比全表扫描要快。当ORACLE找出执行查询和Update语句的最佳路径时,，ORACLE优化器将使用索引，
除了那些LONG或LONG RAW数据类型，你可以索引几乎所有的列，在大型表中使用索引特别有效。
     虽然使用索引能得到查询效率的提高，但是我们也必须注意到它的代价。索引需要空间来 存储,也需要定期维护，每当有记录在表中增减或索引列被修改时，索引本身也会被修改，这意味着每条记录的INSERT 、DELETE 、UPDATE将为此多付出4 、5 次的磁盘I/O ，因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢。

定期的重构索引是有必要的 ：ALTER INDEX <INDEXNAME> REBUILD <TABLESPACENAME>

18、索引优化规则 ：

• like件中不要以通配符(WILDCARD)开始,否则索引将不被采用.

      例:SELECT LODGING FROM LODGING WHERE MANAGER LIKE ‘％HANMAN';

• 避免在索引列上使用计算或改变索引列的类型或使用‘!=’及 <>

  例: SELECT … FROM DEPT WHERE SAL * 12 > 25000;
        SELECT … FROM EMP WHERE EMP_TYPE=to_char(123);
        select …. Where ACCOUNT_NAME||ACCOUNT_TYPE='AMEXA';
        select …where empno!=8888 

• 避免在索引列上使用NOT .

• 用>=替代> .

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

• 用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, 那就需要返回记录最少的索引列写在最前面
注意：WHERE KEY1 = 10 （返回最少记录）
OR KEY2 = 20 （返回最多记录） 
ORACLE 内部将以上转换为 
　WHERE KEY1 = 10 AND（（NOT KEY1 = 10） AND KEY2 = 20）
6.避免在索引列上使用IS NULL和IS NOT NULL
       避免在索引中使用任何可以为空的列，ORACLE将无法使用该索引 ．对于单列索引，如果列包含空值，索引中将不存在此记录. 对于复合索引，如果每个列都为空，索引中同样不存在此记录.　如果至少有一个列不为空，则记录存在于索引中．
(建议：可以给null值的字段设置一个默认值))

7.如果索引是建立在多个列上,索引时段需要放在where 条件的第一个条件(Oracle8i之前),Oracle8i之后允许跳跃式索引.
 
8. 可能的话)用UNION-ALL 替换UNION.
UNION-ALL就是做简单的合并，不会进行排序， UNION先做简单的合并，然后做进行排序，最后去除重复的记录。
9.避免使用耗费资源的操作
         带有DISTINCT,UNION ,MINUS,INTERSECT,ORDER BY的SQL语句会启动SQL引擎.执行耗费资源的排序(SORT)功能. DISTINCT需要一次排序操作, 而其他的至少需要执行两次排序.例如,一个UNION查询,其中每个查询都带有GROUP BY子句, GROUP BY会触发嵌入排序(NESTED SORT) ; 这样, 每个查询需要执行一次排序, 然后在执行UNION时, 又一个唯一排序(SORT UNIQUE)操作被执行而且它只能在前面的嵌入排序结束后才能开始执行. 嵌入的排序的深度会大大影响查询的效率.通常, 带有UNION, MINUS , INTERSECT的SQL语句都可以用其他方式重写.