oracle的一些调优建议（4）

24. 用>=替代>

如果DEPTNO上有一个索引,
高效:
  

SELECT *
   FROM EMP
   WHERE DEPTNO >=4

  
   低效:
  

SELECT *
   FROM EMP
   WHERE DEPTNO >3

    
两者的区别在于, 前者DBMS将直接跳到第一个DEPT等于4的记录而后者将首先定位到DEPTNO=3的记录并且向前扫描到第一个DEPT大于3的记录.

25.用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) 

   


26. 避免在索引列上使用IS NULL和IS NOT NULL

避免在索引中使用任何可以为空的列，ORACLE将无法使用该索引 ．对于单列索引，如果列包含空值，索引中将不存在此记录. 对于复合索引，如果每个列都为空，索引中同样不存在此记录.　如果至少有一个列不为空，则记录存在于索引中．
因为空值不存在于索引列中,所以WHERE子句中对索引列进行空值比较将使ORACLE停用该索引.
举例:
低效: (索引失效)

SELECT …
FROM DEPARTMENT
WHERE DEPT_CODE IS NOT NULL;

高效: (索引有效)

SELECT …
FROM DEPARTMENT
WHERE DEPT_CODE >=0; 

27.总是使用索引的第一个列

如果索引是建立在多个列上, 只有在它的第一个列(leading column)被where子句引用时,优化器才会选择使用该索引.
当仅引用索引的第二个列时,优化器使用全表扫描而忽略索引


28.用UNION-ALL 替换UNION ( 如果有可能的话)

当SQL语句需要UNION两个查询结果集合时,这两个结果集合会以UNION-ALL的方式被合并, 然后在输出最终结果前进行排序.
如果用UNION ALL替代UNION, 这样排序就不是必要了. 效率就会因此得到提高.
举例:
   低效：
　　　　

SELECT ACCT_NUM, BALANCE_AMT
        FROM DEBIT_TRANSACTIONS
        WHERE TRAN_DATE = ’31-DEC-95’
        UNION
        SELECT ACCT_NUM, BALANCE_AMT
        FROM DEBIT_TRANSACTIONS
        WHERE TRAN_DATE = ’31-DEC-95’

高效:
      

 SELECT ACCT_NUM, BALANCE_AMT
        FROM DEBIT_TRANSACTIONS
        WHERE TRAN_DATE = ’31-DEC-95’
        UNION ALL
        SELECT ACCT_NUM, BALANCE_AMT
        FROM DEBIT_TRANSACTIONS
        WHERE TRAN_DATE = ’31-DEC-95’

注:
需要注意的是，UNION ALL 将重复输出两个结果集合中相同记录. 因此各位还是
要从业务需求分析使用UNION ALL的可行性.

29.使用提示(Hints)

对于表的访问,可以使用两种Hints.
FULL 和 ROWID
FULL hint 告诉ORACLE使用全表扫描的方式访问指定表.
例如:
  

SELECT /*+ FULL(EMP) */ *
   FROM EMP
   WHERE EMPNO = 7893;
   ROWID hint 告诉ORACLE使用TABLE ACCESS BY ROWID

的操作访问表.
   通常, 你需要采用TABLE ACCESS BY ROWID的方式特别是当访问大表的时候, 使用这种方式, 你需要知道ROIWD的值或者使用索引.
   如果一个大表没有被设定为缓存(CACHED)表而你希望它的数据在查询结束是仍然停留
在SGA中,你就可以使用CACHE hint 来告诉优化器把数据保留在SGA中. 通常CACHE hint 和 FULL hint 一起使用.
例如:

SELECT /*+ FULL(WORKER) CACHE(WORKER)*/ *
FROM WORK;

   索引hint 告诉ORACLE使用基于索引的扫描方式. 你不必说明具体的索引名称
例如:
  

SELECT /*+ INDEX(LODGING) */ LODGING
   FROM LODGING
   WHERE MANAGER = ‘BILL GATES’;

  
   在不使用hint的情况下, 以上的查询应该也会使用索引,然而,如果该索引的重复值过多而你的优化器是CBO, 优化器就可能忽略索引. 在这种情况下, 你可以用INDEX hint强制ORACLE使用该索引.
   ORACLE hints 还包括ALL_ROWS, FIRST_ROWS, RULE,USE_NL, USE_MERGE, USE_HASH 等等.
  
注:
   使用hint , 表示我们对ORACLE优化器缺省的执行路径不满意,需要手工修改.
这是一个很有技巧性的工作. 我建议只针对特定的,少数的SQL进行hint的优化.
对ORACLE的优化器还是要有信心(特别是CBO)