13.       計(jì)算記錄條數(shù)

     和一般的觀點(diǎn)相反, count(*) 比count(1)稍快 , 當(dāng)然如果可以通過索引檢索,對索引列的計(jì)數(shù)仍舊是最快的. 例如 COUNT(EMPNO)



(譯者按: 在CSDN論壇中,曾經(jīng)對此有過相當(dāng)熱烈的討論, 作者的觀點(diǎn)并不十分準(zhǔn)確,通過實(shí)際的測試,上述三種方法并沒有顯著的性能差別)



14.       用Where子句替換HAVING子句



     避免使用HAVING子句, HAVING 只會(huì)在檢索出所有記錄之后才對結(jié)果集進(jìn)行過濾. 這個(gè)處理需要排序,總計(jì)等操作. 如果能通過WHERE子句限制記錄的數(shù)目,那就能減少這方面的開銷.



例如:



     低效:

     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 中的條件一般用于對一些集合函數(shù)的比較,如COUNT() 等等. 除此而外,一般的條件應(yīng)該寫在WHERE子句中)



15.       減少對表的查詢

在含有子查詢的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 多個(gè)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.       通過內(nèi)部函數(shù)提高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;



通過調(diào)用下面的函數(shù)可以提高效率.

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;



(譯者按: 經(jīng)常在論壇中看到如 ’能不能用一個(gè)SQL寫出….’ 的貼子, 殊不知復(fù)雜的SQL往往犧牲了執(zhí)行效率. 能夠掌握上面的運(yùn)用函數(shù)解決問題的方法在實(shí)際工作中是非常有意義的)



(待續(xù))