ORACLE SQL性能优化系列 (四)

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

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

1. 用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子句中)

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

例如:
低效
SELECT TAB_NAME
FROM TABLES
WHERE TAB_NAME = ( SELECT TAB_NAME
FROM TAB_COLUMNS
WHERE VERSION = 604)
AND　DB_VER= ( SELECT DB_VER
FROM 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) FROM EMP_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;

1. 通过内部函数提高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;

通过调用下面的函数可以提高效率.
FUNCTION LOOKUP_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(EMP IN NUMBER) RETURN VARCHAR2
AS
ENAME VARCHAR2(30);
CURSOR C1 IS
SELECT ENAME
FROM EMP
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_HISTORY H
GROUP BY H.EMPNO , H.HIST_TYPE;