SQL'where'子句中条件的执行顺序 [英] Execution order of conditions in SQL 'where' clause

问题描述

我的where子句中有一组条件

I have a set of conditions in my where clause like

WHERE 
d.attribute3 = 'abcd*'  
AND x.STATUS != 'P' 
AND x.STATUS != 'J' 
AND x.STATUS != 'X' 
AND x.STATUS != 'S' 
AND x.STATUS != 'D' 
AND CURRENT_TIMESTAMP - 1 < x.CREATION_TIMESTAMP

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

Which of these conditions will be executed first? I am using oracle.

我将在执行计划中获得这些详细信息吗? (我没有权限在此处的数据库中执行此操作，否则我会尝试过的)

Will I get these details in my execution plan? (I do not have the authority to do that in the db here, else I would have tried)

推荐答案

您是否确定您没有权限"查看执行计划?使用AUTOTRACE怎么办?

Are you sure you "don't have the authority" to see an execution plan? What about using AUTOTRACE?

SQL> set autotrace on
SQL> select * from emp
  2  join dept on dept.deptno = emp.deptno
  3  where emp.ename like 'K%'
  4  and dept.loc like 'l%'
  5  /

no rows selected


Execution Plan
----------------------------------------------------------

----------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |     1 |    62 |     4   (0)|
|   1 |  NESTED LOOPS                |              |     1 |    62 |     4   (0)|
|*  2 |   TABLE ACCESS FULL          | EMP          |     1 |    42 |     3   (0)|
|*  3 |   TABLE ACCESS BY INDEX ROWID| DEPT         |     1 |    20 |     1   (0)|
|*  4 |    INDEX UNIQUE SCAN         | SYS_C0042912 |     1 |       |     0   (0)|
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("EMP"."ENAME" LIKE 'K%' AND "EMP"."DEPTNO" IS NOT NULL)
   3 - filter("DEPT"."LOC" LIKE 'l%')
   4 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")

如您所见，这提供了有关如何执行查询的很多详细信息.它告诉我:

As you can see, that gives quite a lot of detail about how the query will be executed. It tells me that:

• 在对EMP进行全面扫描时，将首先应用条件"emp.ename如'K％'"
• 然后将通过dept.deptno上的索引(通过NESTED LOOPS方法)选择匹配的DEPT记录
• 最后，将应用过滤器"dept.loc，如'l％'.

此应用程序顺序与WHERE子句中谓词的排序方式无关，如下面的重新排序查询所示:

This order of application has nothing to do with the way the predicates are ordered in the WHERE clause, as we can show with this re-ordered query:

SQL> select * from emp
  2  join dept on dept.deptno = emp.deptno
  3  where dept.loc like 'l%'
  4  and emp.ename like 'K%';

no rows selected


Execution Plan
----------------------------------------------------------

----------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |     1 |    62 |     4   (0)|
|   1 |  NESTED LOOPS                |              |     1 |    62 |     4   (0)|
|*  2 |   TABLE ACCESS FULL          | EMP          |     1 |    42 |     3   (0)|
|*  3 |   TABLE ACCESS BY INDEX ROWID| DEPT         |     1 |    20 |     1   (0)|
|*  4 |    INDEX UNIQUE SCAN         | SYS_C0042912 |     1 |       |     0   (0)|
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("EMP"."ENAME" LIKE 'K%' AND "EMP"."DEPTNO" IS NOT NULL)
   3 - filter("DEPT"."LOC" LIKE 'l%')
   4 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")

这篇关于SQL'where'子句中条件的执行顺序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！