ORACLE SQL性能优化系列 3

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

而当运用TRUNCATE时, 回滚段不再存放任何可被恢复的信息.当命令运行后,数据不能被恢复.因此很少的资源被调用,执行时间也会很短.

(译者按: TRUNCATE只在删除全表适用,TRUNCATE是DDL不是DML)


12. 尽量多使用COMMIT
只要有可能,在程序中尽量多使用COMMIT, 这样程序的性能得到提高,需求也会因为COMMIT所释放的资源而减少:

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

(译者按: 在使用COMMIT时必须要注意到事务的完整性,现实中效率和事务完整性往往是鱼和熊掌不可得兼)

如果DECODE取值为NULL，SUM（NULL）的值是NULL -->如果所有的值都是NULL , SUM(NULL) = NULL 但是只要有一个值不是NULL,SUM()<> NULL 所以原SQL应该没有什么逻辑上的问题

menlion (2003-9-4 12:38:01)

关于第八点的个人看法：如果DECODE取值为NULL，SUM（NULL）的值是NULL，不会正常求和的。可以改成如下所示就好了：

SELECT COUNT ( DECODE( DEPT_NO,0020,'X', NULL))  D0020_COUNT, COUNT(DECODE( DEPT_NO, 0030,'X', NULL))  D0030_COUNT, SUM(DECODE( DEPT_NO, 0020, SAL, 0))  D0020_SAL, SUM( DECODE(DEPT_NO, 0030, SAL, 0))  D0030_SAL

FROM EMP

WHERE ENAME LIKE‘SMITH%';

 
13. 计算记录条数
和一般的观点相反, count(*) 比count(1)稍快, 当然如果可以通过索引检索,对索引列的计数仍旧是最快的. 例如COUNT(EMPNO)

(译者按: 在CSDN论坛中,曾经对此有过相当热烈的讨论, 作者的观点并不十分准确,通过实际的测试,上述三种方法并没有显著的性能差别)


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

低效:
    SELECT  REGION，AVG(LOG_SIZE)
       FROM LOCATION
GROUP BY  REGION
    HAVING  REGION REGION != ‘SYDNEY'
          AND REGION !=‘PERTH'

高效
SELECT  REGION，AVG(LOG_SIZE)
   FROM LOCATION
WHERE  REGION  REGION  !=  ‘SYDNEY'
     AND  REGION!= ‘PERTH'
GROUP BY  REGION

(译者按: HAVING 中的条件一般用于对一些集合函数的比较,如COUNT() 等等. 除此而外,一般的条件应该写在WHERE子句中)

15. 减少对表的查询
在含有子查询的SQL语句中,要特别注意减少对表的查询.
例如:

低效
SELECT  TAB_NAME
   FROM TABLES
WHERE  TAB_NAME  = ( SELECT  TAB_NAME   FROMTAB_COLUMNS
                                          WHERE  VERSION = 604)
     AND　DB_VER = ( SELECT DB_VER  TAB_COLUMNS
                                     WHERE  VERSION = 604)

高效
SELECT  TAB_NAME
   FROM TABLES
WHERE   ( TAB_NAME, DB_VER ) = ( SELECT TAB_NAME,DB_VER)
                                                                  FROM TAB_COLUMNS
                                                               WHERE  VERSION = 604 )

Update 多个Column例子:

低效:
UPDATE  EMP
      SET  EMP_CAT =  ( SELECT   MAX(CATEGORY)  FROM  EMP_CATEGORIES),
      SAL_RANGE = ( SELECT MAX(SAL_RANGE) FROMEMP_CATEGORIES)
       WHERE EMP_DEPT = 0020;

高效:
UPDATE   EMP
      SET   (EMP_CAT,SAL_RANGE)  = (SELECT  MAX(CATEGORY) ,MAX(SAL_RANGE)
                                                                   FROM EMP_CATEGORIES)
 WHERE  EMP_DEPT= 0020;


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

SELECT  H.EMPNO,  E.ENAME, H.HIST_TYPE, T.TYPE_DESC,COUNT(*)
   FROM HISTORY_TYPE T,  EMP E,  EMP_HISTORY H
WHERE H.EMPNO = E.EMPNO
    AND   H.HIST_TYPE= T.HIST_TYPE
GROUP BY H.EMPNO,  E.ENAME, H.HIST_TYPE,  T.TYPE_DESC;

通过调用下面的函数可以提高效率.
FUNCTIONLOOKUP_HIST_TYPE(TYP IN NUMBER) RETURN VARCHAR2
AS
   TDESC VARCHAR2(30);
CURSOR C1 IS
   SELECT   TYPE_DESC  FROM  HISTORY_TYPE  WHERE HIST_TYPE =TYP;
BEGIN
   OPEN  C1;
   FETCH C1  INTO  TDESC;
   CLOSE C1;
   RETURN (NVL(TDESC,'?'));
END;


FUNCTION LOOKUP_EMP(EMPIN NUMBER) RETURN VARCHAR2
AS
ENAME VARCHAR2(30);
CURSOR C1 IS
   SELECT ENAME   FROMEMP  WHERE EMPNO=EMP;
BEGIN
   OPEN C1;
   FETCH C1 INTO ENAME;
   CLOSE C1;
   RETURN (NVL(ENAME,'?'));
END;


      SELECT   H.EMPNO,  LOOKUP_EMP(H.EMPNO), 

H.HIST_TYPE, LOOKUP_HIST_TYPE(H.HIST_TYPE),  COUNT(*)
       FROM   EMP_HISTORYH
GROUP BY   H.EMPNO, H.HIST_TYPE;

(译者按: 经常在论坛中看到如 '能不能用一个SQL写出….' 的贴子, 殊不知复杂的SQL往往牺牲了执行效率. 能够掌握上面的运用函数解决问题的方法在实际工作中是非常有意义的)