ORACLE SQL性能优化系列 (四)

ORACLE SQL性能优化系列 (四)

作者: black_snail

 

关键字 ORACLE SQL PERFORMANCE TUNING 

出处 http://WWW.DBASUPPORT.COM 


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



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往往牺牲了执行效率. 能够掌握上面的运用函数解决问题的方法在实际工作中是非常有意义的)