Oracle SQL性能優化系列 (四)
13. 計算記錄條數和一般的觀點相反, count(*) 比count(1)稍快 , 當然假如可以通過索引檢索,對索引列的計數仍然是最快的. 例如 COUNT(EMPNO) (譯者按: 在CSDN論壇中,曾經對此有過相當熱烈的討論, 作者的觀點并不十分準確,通過實際的測試,上述三種方法并沒有顯著的性能差別) 14. 用Where子句替換HAVING子句 避免使用HAVING子句, HAVING 只會在檢索出所有記錄之后才對結果集進行過濾. 這個處理需要排序,總計等操作. 假如能通過WHERE子句限制記錄的數目,那就能減少這方面的開銷. 例如: 低效:SELECT REGION，AVG(LOG_SIZE)FROM LOCATIONGROUP BY REGIONHAVING REGION REGION != ‘SYDNEY’AND REGION != ‘PERTH’ 高效SELECT REGION，AVG(LOG_SIZE)FROM LOCATIONWHERE REGION REGION != ‘SYDNEY’AND REGION != ‘PERTH’GROUP BY REGION(譯者按: HAVING 中的條件一般用于對一些集合函數的比較,如COUNT() 等等. 除此而外,一般的條件應該寫在WHERE子句中) 15. 減少對表的查詢在含有子查詢的SQL語句中,要非凡注重減少對表的查詢. 例如: 低效SELECT TAB_NAMEFROM TABLESWHERE TAB_NAME = ( SELECT TAB_NAME FROM TAB_COLUMNSWHERE VERSION = 604)AND DB_VER= ( SELECT DB_VER FROM TAB_COLUMNSWHERE VERSION = 604) 高效SELECT TAB_NAMEFROM TABLESWHERE (TAB_NAME,DB_VER)= ( SELECT TAB_NAME,DB_VER) FROM TAB_COLUMNSWHERE VERSION = 604) Update 多個Column 例子:低效:UPDATE EMPSET EMP_CAT = (SELECT MAX(CATEGORY) FROM EMP_CATEGORIES),SAL_RANGE = (SELECT MAX(SAL_RANGE) FROM EMP_CATEGORIES)WHERE EMP_DEPT = 0020; 高效:UPDATE EMPSET (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 HWHERE H.EMPNO = E.EMPNOAND H.HIST_TYPE = T.HIST_TYPEGROUP BY H.EMPNO,E.ENAME,H.HIST_TYPE,T.TYPE_DESC; 通過調用下面的函數可以提高效率.FUNCTION LOOKUP_HIST_TYPE(TYP IN NUMBER) RETURN VARCHAR2ASTDESC VARCHAR2(30);CURSOR C1 IS SELECT TYPE_DESC FROM HISTORY_TYPEWHERE HIST_TYPE = TYP;BEGIN OPEN C1;FETCH C1 INTO TDESC;CLOSE C1;RETURN (NVL(TDESC,’?’));END; FUNCTION LOOKUP_EMP(EMP IN NUMBER) RETURN VARCHAR2ASENAME VARCHAR2(30);CURSOR C1 IS SELECT ENAMEFROM EMPWHERE 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 HGROUP BY H.EMPNO , H.HIST_TYPE; (譯者按: 經常在論壇中看到如 ’能不能用一個SQL寫出….’ 的貼子, 殊不知復雜的SQL往往犧牲了執行效率. 能夠把握上面的運用函數解決問題的方法在實際工作中是非常有意義的) (待續)
