ORACLE的优化器(二)

8. 使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表. 
例如: 
SELECT COUNT(*)，SUM(SAL) 
FROM　EMP 
WHERE DEPT_NO = 0020 
AND ENAME LIKE　‘SMITH%'; 

SELECT COUNT(*)，SUM(SAL) 
FROM　EMP 
WHERE DEPT_NO = 0030 
AND ENAME LIKE　‘SMITH%'; 


你可以用DECODE函数高效地得到相同结果 

SELECT COUNT(DECODE(DEPT_NO,0020,'X',NULL)) D0020_COUNT, 
COUNT(DECODE(DEPT_NO,0030,'X',NULL)) D0030_COUNT, 
SUM(DECODE(DEPT_NO,0020,SAL,NULL)) D0020_SAL, 
SUM(DECODE(DEPT_NO,0030,SAL,NULL)) D0030_SAL 
FROM EMP WHERE ENAME LIKE ‘SMITH%'; 

类似的,DECODE函数也可以运用于GROUP BY 和ORDER BY子句中. 

如果DECODE取值为NULL，SUM（NULL）的值是NULL -->如果所有的值都是NULL , SUM(NULL) = NULL 但是只要有一个值不是NULL,SUM() <> NULL 所以原SQL应该没有什么逻辑上的问题 
menlion (2003-9-4 12:38:01) 
关于第八点的个人看法：如果DECODE取值为NULL，SUM（NULL）的值是NULL，不会正常求和的。可以改成如下所示就好了： SELECT COUNT(DECODE(DEPT_NO,0020,'X',NULL)) D0020_COUNT, COUNT(DECODE(DEPT_NO,0030,'X',NULL)) D0030_COUNT, SUM(DECODE(DEPT_NO,0020,SAL,0)) D0020_SAL, SUM(DECODE(DEPT_NO,0030,SAL,0)) D0030_SAL FROM EMP 
WHERE ENAME LIKE ‘SMITH%'; 

9. 整合简单，无关联的数据库访问
如果你有几个简单的数据库查询语句,你可以把它们整合到一个查询中(即使它们之间没有关系) 
例如: 
SELECT NAME FROM EMP 
WHERE EMP_NO = 1234; 

SELECT NAME 
FROM DPT 
WHERE DPT_NO = 10 ; 

SELECT NAME FROM CAT 
WHERE CAT_TYPE = ‘RD'; 

上面的3个查询可以被合并成一个: 

SELECT E.NAME , D.NAME , C.NAME 
FROM CAT C , DPT D , EMP E,DUAL X 
WHERE NVL(‘X',X.DUMMY) = NVL(‘X',E.ROWID(+)) 
AND NVL(‘X',X.DUMMY) = NVL(‘X',D.ROWID(+)) 
AND NVL(‘X',X.DUMMY) = NVL(‘X',C.ROWID(+)) 
AND E.EMP_NO(+) = 1234 
AND D.DEPT_NO(+) = 10 
AND C.CAT_TYPE(+) = ‘RD'; 
(译者按: 虽然采取这种方法,效率得到提高,但是程序的可读性大大降低,所以读者 还是要权衡之间的利弊)

 

10. 最高效的删除重复记录方法 ( 因为使用了ROWID) 
DELETE FROM EMP E 
WHERE E.ROWID > (SELECT MIN(X.ROWID) 
FROM EMP X 
WHERE X.EMP_NO = E.EMP_NO); 

11. 使用Truncate代替delete
当删除表中的记录时,在通常情况下, 回滚段(rollback segments ) 用来存放可以被恢复的信息. 如果你没有COMMIT事务,ORACLE会将数据恢复到删除之前的状态(准确地说是恢复到执行删除命令之前的状况) 而当运用TRUNCATE时, 回滚段不再存放任何可被恢复的信息.当命令运行后,数据不能被恢复.因此很少的资源被调用,执行时间也会很短. 
(译者按: TRUNCATE只在删除全表适用,TRUNCATE是DDL不是DML)

12. 尽量多使用Commit
只要有可能,在程序中尽量多使用COMMIT, 这样程序的性能得到提高,需求也会因为COMMIT所释放的资源而减少: 
COMMIT所释放的资源: 
a. 回滚段上用于恢复数据的信息. 
b. 被程序语句获得的锁 
c. redo log buffer 中的空间 
d. ORACLE为管理上述3种资源中的内部花费 
(译者按: 在使用COMMIT时必须要注意到事务的完整性,现实中效率和事务完整性往往是鱼和熊掌不可得兼) 

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


17. 使用表的别名
当在SQL语句中连接多个表时, 请使用表的别名并把别名前缀于每个Column上.这样一来,就可以减少解析的时间并减少那些由Column歧义引起的语法错误. 
(译者注: Column歧义指的是由于SQL中不同的表具有相同的Column名,当SQL语句中出现这个Column时,SQL解析器无法判断这个Column的归属) 

18. 使用Exists代替in
在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接.在这种情况下, 使用EXISTS(或NOT EXISTS)通常将提高查询的效率. 
低效: 
SELECT * FROM EMP (基础表) 
WHERE EMPNO > 0 
AND DEPTNO IN (SELECT DEPTNO 
  FROM DEPT 
  WHERE LOC = ‘MELB') 

高效: 
SELECT * FROM EMP (基础表) 
WHERE EMPNO > 0 
AND EXISTS (SELECT ‘X' 
  FROM DEPT 
  WHERE DEPT.DEPTNO = EMP.DEPTNO 
  AND LOC = ‘MELB') 
(译者按: 相对来说,用NOT EXISTS替换NOT IN 将更显著地提高效率,下一节中将指出)

19. 使用not exists代替not in
在子查询中,NOT IN子句将执行一个内部的排序和合并. 无论在哪种情况下,NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历). 避免使用NOT IN ,我们可以把它改写成外连接(Outer Joins)或NOT EXISTS. 
例如: 
SELECT … FROM EMP 
WHERE DEPT_NO NOT IN (SELECT DEPT_NO 
  FROM DEPT 
  WHERE DEPT_CAT='A'); 

为了提高效率.改写为: 

(方法一: 高效) 
SELECT …. 
FROM EMP A,DEPT B 
WHERE A.DEPT_NO = B.DEPT(+) 
AND B.DEPT_NO IS NULL 
AND B.DEPT_CAT(+) = ‘A' 

(方法二: 最高效) 
SELECT …. 
FROM EMP E 
WHERE NOT EXISTS (SELECT ‘X' 
FROM DEPT D 
WHERE D.DEPT_NO = E.DEPT_NO 
AND DEPT_CAT = ‘A'); 

20. 使用表连接替换exists
通常来说 , 采用表连接的方式比EXISTS更有效率 
SELECT ENAME FROM EMP E 
WHERE EXISTS (SELECT ‘X' 
FROM DEPT 
WHERE DEPT_NO = E.DEPT_NO 
AND DEPT_CAT = ‘A'); 

(更高效) 
SELECT ENAME FROM DEPT D,EMP E 
WHERE E.DEPT_NO = D.DEPT_NO 
AND DEPT_CAT = ‘A' ; 
(译者按: 在RBO的情况下,前者的执行路径包括FILTER,后者使用NESTED LOOP) 


21. 使用exists替换distinct
当提交一个包含一对多表信息(比如部门表和雇员表)的查询时,避免在SELECT子句中使用DISTINCT. 一般可以考虑用EXIST替换 
例如: 
低效: 
SELECT DISTINCT DEPT_NO,DEPT_NAME FROM DEPT D,EMP E 
WHERE D.DEPT_NO = E.DEPT_NO 
高效: 
SELECT DEPT_NO,DEPT_NAME FROM DEPT D 
WHERE EXISTS ( SELECT ‘X' 
FROM EMP E 
WHERE E.DEPT_NO = D.DEPT_NO); 
EXISTS 使查询更为迅速,因为RDBMS核心模块将在子查询的条件一旦满足后,立刻返回结果. 


22. 用下列SQL工具找出低效SQL: 
SELECT EXECUTIONS , DISK_READS, BUFFER_GETS, 
ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2) Hit_radio, 
ROUND(DISK_READS/EXECUTIONS,2) Reads_per_run, 
SQL_TEXT 
FROM V$SQLAREA 
WHERE EXECUTIONS>0 
AND BUFFER_GETS > 0 
AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS < 0.8 
ORDER BY 4 DESC; 
(译者按: 虽然目前各种关于SQL优化的图形化工具层出不穷,但是写出自己的SQL工具来解决问题始终是一个最好的方法)