SQL’where’子句中的条件的执行顺序

前端之家收集整理的这篇文章主要介绍了SQL’where’子句中的条件的执行顺序前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。
我在我的where子句中有一组条件
  1. WHERE
  2. d.attribute3 = 'abcd*'
  3. AND x.STATUS != 'P'
  4. AND x.STATUS != 'J'
  5. AND x.STATUS != 'X'
  6. AND x.STATUS != 'S'
  7. AND x.STATUS != 'D'
  8. AND CURRENT_TIMESTAMP - 1 < x.CREATION_TIMESTAMP

首先执行以下哪些条件?我正在使用oracle.

我在执行计划中会得到这些细节吗?
(我没有权限在db中这样做,否则我会尝试)

解决方法

你确定你没有权限去看执行计划吗?如何使用AUTOTRACE?
  1. sql> set autotrace on
  2. sql> select * from emp
  3. 2 join dept on dept.deptno = emp.deptno
  4. 3 where emp.ename like 'K%'
  5. 4 and dept.loc like 'l%'
  6. 5 /
  7.  
  8. no rows selected
  9.  
  10.  
  11. Execution Plan
  12. ----------------------------------------------------------
  13.  
  14. ----------------------------------------------------------------------------------
  15. | Id | Operation | Name | Rows | Bytes | Cost (%cpu)|
  16. ----------------------------------------------------------------------------------
  17. | 0 | SELECT STATEMENT | | 1 | 62 | 4 (0)|
  18. | 1 | NESTED LOOPS | | 1 | 62 | 4 (0)|
  19. |* 2 | TABLE ACCESS FULL | EMP | 1 | 42 | 3 (0)|
  20. |* 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 (0)|
  21. |* 4 | INDEX UNIQUE SCAN | SYS_C0042912 | 1 | | 0 (0)|
  22. ----------------------------------------------------------------------------------
  23.  
  24. Predicate Information (identified by operation id):
  25. ---------------------------------------------------
  26.  
  27. 2 - filter("EMP"."ENAME" LIKE 'K%' AND "EMP"."DEPTNO" IS NOT NULL)
  28. 3 - filter("DEPT"."LOC" LIKE 'l%')
  29. 4 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")

可以看到,这给出了关于如何执行查询的很多细节.它告诉我:

>将在EMP的全面扫描上首先应用条件“emp.ename like”K%“”
>那么匹配的DEPT记录将通过dept.deptno上的索引(通过NESTED LOOPS方法)来选择,
>最后将过滤“dept.loc like”l%“将被应用.

这个应用顺序与在WHERE子句中排除谓词的方式无关,我们可以用这个重新排序的查询显示

  1. sql> select * from emp
  2. 2 join dept on dept.deptno = emp.deptno
  3. 3 where dept.loc like 'l%'
  4. 4 and emp.ename like 'K%';
  5.  
  6. no rows selected
  7.  
  8.  
  9. Execution Plan
  10. ----------------------------------------------------------
  11.  
  12. ----------------------------------------------------------------------------------
  13. | Id | Operation | Name | Rows | Bytes | Cost (%cpu)|
  14. ----------------------------------------------------------------------------------
  15. | 0 | SELECT STATEMENT | | 1 | 62 | 4 (0)|
  16. | 1 | NESTED LOOPS | | 1 | 62 | 4 (0)|
  17. |* 2 | TABLE ACCESS FULL | EMP | 1 | 42 | 3 (0)|
  18. |* 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 (0)|
  19. |* 4 | INDEX UNIQUE SCAN | SYS_C0042912 | 1 | | 0 (0)|
  20. ----------------------------------------------------------------------------------
  21.  
  22. Predicate Information (identified by operation id):
  23. ---------------------------------------------------
  24.  
  25. 2 - filter("EMP"."ENAME" LIKE 'K%' AND "EMP"."DEPTNO" IS NOT NULL)
  26. 3 - filter("DEPT"."LOC" LIKE 'l%')
  27. 4 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")

猜你在找的MsSQL相关文章