oracle 用EXISTS替代IN(转)

1. 1.用EXISTS替代IN    
2. 在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接.在这种情况下, 使用EXISTS(或NOT EXISTS)通常将提高查询的效率.    
3.   
4. 低效:    
5. SELECT *    
6. FROM EMP (基础表)    
7. WHERE EMPNO > 0    
8. AND DEPTNO IN (SELECT DEPTNO    
9. FROM DEPT    
10. WHERE LOC = ‘MELB’)    
11.        
12. 高效:    
13. SELECT *    
14. FROM EMP (基础表)    
15. WHERE EMPNO > 0    
16. AND EXISTS (SELECT ‘X’    
17. FROM DEPT    
18. WHERE DEPT.DEPTNO = EMP.DEPTNO    
19. AND LOC = ‘MELB’)    
20.   (相对来说,用NOT EXISTS替换NOT IN 将更显著地提高效率)    
21.      
22. 2. 用NOT EXISTS替代NOT IN    
23. 在子查询中,NOT IN子句将执行一个内部的排序和合并. 无论在哪种情况下,NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历).  为了避免使用NOT IN ,我们可以把它改写成外连接(Outer Joins)或NOT EXISTS.    
24. 例如:    
25. SELECT …    
26. FROM EMP    
27. WHERE DEPT_NO NOT IN (SELECT DEPT_NO    
28.                         FROM DEPT    
29.                         WHERE DEPT_CAT=’A’);    
30.   
31. 为了提高效率.改写为:    
32.   
33. (方法一: 高效)    
34. SELECT ….    
35. FROM EMP A,DEPT B    
36. WHERE A.DEPT_NO = B.DEPT(+)    
37. AND B.DEPT_NO IS NULL    
38. AND B.DEPT_CAT(+) = ‘A’    
39.   
40.   
41. (方法二: 最高效)    
42. SELECT ….    
43. FROM EMP E    
44. WHERE NOT EXISTS (SELECT ‘X’    
45.                     FROM DEPT D    
46.                     WHERE D.DEPT_NO = E.DEPT_NO    
47.                     AND DEPT_CAT = ‘A’);    
48.   
49. 本人在使用中发现左外连接性能比not exist更高效，请大家进一步验证。    
50. 3.用表连接替换EXISTS    
51.       通常来说 , 采用表连接的方式比EXISTS更有效率    
52.       SELECT ENAME    
53.       FROM EMP E    
54.       WHERE EXISTS (SELECT ‘X’    
55.                       FROM DEPT    
56.                       WHERE DEPT_NO = E.DEPT_NO    
57.                       AND DEPT_CAT = ‘A’);    
58.   
59.     (更高效)    
60.       SELECT ENAME    
61.       FROM DEPT D,EMP E    
62.       WHERE E.DEPT_NO = D.DEPT_NO    
63.       AND DEPT_CAT = ‘A’ ;    
64. (在RBO的情况下,前者的执行路径包括FILTER,后者使用NESTED LOOP)    
65.   
66. 4.用EXISTS替换DISTINCT    
67. 当提交一个包含一对多表信息(比如部门表和雇员表)的查询时,避免在SELECT子句中使用DISTINCT. 一般可以考虑用EXIST替换    
68. 例如:    
69. 低效:    
70.     SELECT DISTINCT DEPT_NO,DEPT_NAME    
71.     FROM DEPT D,EMP E    
72.     WHERE D.DEPT_NO = E.DEPT_NO    
73. 高效:    
74.     SELECT DEPT_NO,DEPT_NAME    
75.     FROM DEPT D    
76.     WHERE EXISTS ( SELECT ‘X’    
77.                     FROM EMP E    
78.                     WHERE E.DEPT_NO = D.DEPT_NO);    
79.   EXISTS 使查询更为迅速,因为RDBMS核心模块将在子查询的条件一旦满足后,立刻返回结果.