Oracle查询优化

Oracle查询优化

1>选择最有效率的表名顺序：

把记录少的表放在from子句的最后面一个表.

 

2.如果有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指那个被其他表所引用的表.

原因：ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名,因此FROM子句中写在最后的表(基础表 driving table)将被最先处理. 在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表.当ORACLE处理多个表时, 会运用排序及合并的方式连接它们.首先,扫描第一个表(FROM子句中最后的那个表)并对记录进行排序,然后扫描第二个表(FROM子句中最后第二个表),最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并

 

2> WHERE子句中的连接顺序 ：

     ORACLE采用自右向左的顺序解析WHERE子句,, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾.

3.>SELECT子句中避免使用 ‘ * ‘

       ORACLE在解析的过程中, 需要通过查询数据字典将'*' 依次转换成所有的列名.

 

4.> 使用表的别名(Alias)

        当在SQL语句中连接多个表时, 请使用表的别名并把别名前缀于每个Column上.这样一来,就可以减少解析的时间并减少那些由Column歧义引起的语法错误.
(Column歧义指的是由于SQL中不同的表具有相同的Column名,当SQL语句中出现这个Column时,SQL解析器无法判断这个Column的归属) 

5.>减少访问数据库的次数:

       当执行每条SQL语句时, ORACLE在内部执行了许多工作: 解析SQL语句, 估算索引的利用率, 绑定变量 , 读数据块等等. 由此可见, 减少访问数据库的次数 , 就能实际上减少ORACLE的工作量

 

6.>(可能的话)用TRUNCATE替代DELETE.

      当删除表中的记录时,在通常情况下, 回滚段(rollback segments ) 用来存放可以被恢复的信息. 如果你没有COMMIT事务,ORACLE会将数据恢复到执行删除命令之前的状况.

      而当运用TRUNCATE时, 回滚段不再存放任何可被恢复的信息.当命令运行后,数据不能被恢复.因此很少的资源被调用,执行时间也会很短. (TRUNCATE只在删除全表里的记录时适用,TRUNCATE是DDL不是DML)

 

7.> (可能的话)使用COMMIT

      只要有可能,在程序中尽量多使用COMMIT, 这样程序的性能得到提高,需求也会因为COMMIT所释放的资源而减少:
COMMIT所释放的资源: 
a. 回滚段上用于恢复数据的信息. 
b. 被程序语句获得的锁

   c. redo log buffer 中的空间

   d. ORACLE为管理上述3种资源中的内部花费

 

8.>(可能的话)用Where子句替换HAVING子句

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

 

9.>(某些情况下)可以用EXISTS替代IN . NOT EXISTS替代NOT IN

  性能比较:

     1.Select * from t1 where x in ( select y from t2)

     2.select * from t1 where exists ( select 1 from t2 where t2. y = t1.x )

      当t1记录比较少,t2比较大时适合用exists(exists大部分情况会利用到index),当子查询记录集很小时用in比较合适.

 

    原因分析：

    1.Select * from T1 where x in ( select y from T2 )
     执行的过程相当于:
    select *   from t1, ( select distinct y from t2 ) t3 where t1.x = t3.y;

    2. select * from t1 where exists ( select 1 from t2 where t2.y = t1.x )
执行的过程相当于:
for x in ( select * from t1 ) loop
      if ( exists ( select 1 from t2 where t2.y = t1.x )
      then 
         OUTPUT THE RECORD
      end if
end loop
这样表 T1 要被完全扫描一遍 .

      所以可以得出结论:当t1记录比较少,t2比较大时适合用exists(exists大部分情况会利用 到index),当子查询记录集很小时用in比较合适.

 

10.>用表连接替换EXISTS
改进第9打优化规则的例子.

 

11.>用EXISTS替换DISTINCT

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

 

12.>使用显式的游标(CURSOR)

      使用隐式的游标,将会执行两次操作. 第一次检索记录, 第二次检查TOO MANY ROWS 这个exception . 而显式游标不执行第二次操作.

 

11-例：

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);

 

13.用索引提高效率

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

         定期的重构索引是有必要的.

      ALTER INDEX <INDEXNAME> REBUILD <TABLESPACENAME>

 

索引优化规则：

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

      例:SELECT LODGING FROM LODGING 

       WHERE MANAGER LIKE ‘％HANMAN';

 

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

 例: 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 ;

 

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

 

4.用>=替代> .

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

 

5.用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语句都可以用其他方式重写.

转自：http://blog.csdn.net/whereusejava/article/details/7733422