ORACLE SQL效能最佳化系列 (四) (轉)

ORACLE SQL效能最佳化系列 (四) (轉)[@more@]

:namespace prefix = o ns = "urn:schemas--com::office" />

13.  計算記錄條數

  和一般的觀點相反, count(*) 比count(1)稍快 , 當然如果可以透過檢索,對索引列的計數仍舊是最快的. 例如 COUNT(EMPNO)

(譯者按: 在CSDN論壇中,曾經對此有過相當熱烈的討論, 作者的觀點並不十分準確,透過實際的測試,上述三種方法並沒有顯著的差別)

14.  用Where子句替換HAVING子句

  避免使用HAVING子句, HAVING 只會在檢索出所有記錄之後才對結果集進行過濾. 這個處理需要排序,總計等操作. 如果能透過WHERE子句限制記錄的數目,那就能減少這方面的開銷.

例如:

  低效:

  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.  減少對錶的查詢

在含有子查詢的語句中,要特別注意減少對錶的查詢.

 

例如:

  低效

   SELECT TAB_NAME

  FROM TABLES

  WHERE TAB_NAME = ( SELECT TAB_NAME

  FROM TAB_COLUMNS

   WHERE VERSION = 604)

  AND　_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;

 

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;

透過下面的函式可以提高效率.

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;

(譯者按: 經常在論壇中看到如 ’能不能用一個SQL寫出….’ 的貼子, 殊不知複雜的SQL往往犧牲了效率. 能夠掌握上面的運用函式解決問題的方法在實際工作中是非常有意義的)