SQL 'where' 子句中条件的执行顺序
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/340139/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Execution order of conditions in SQL 'where' clause
提问by sarego
I have a set of conditions in my where clause like
我的 where 子句中有一组条件，例如
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
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)
我会在执行计划中获得这些详细信息吗？（我无权在此处的数据库中执行此操作，否则我会尝试）
回答by Tony Andrews
Are you sureyou "don't have the authority" to see an execution plan? What about using AUTOTRACE?
您确定您“无权”查看执行计划吗？使用 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:
如您所见，这提供了有关如何执行查询的大量详细信息。它告诉我：
- the condition "emp.ename like 'K%'" will be applied first, on the full scan of EMP
- then the matching DEPT records will be selected via the index on dept.deptno (via the NESTED LOOPS method)
- finally the filter "dept.loc like 'l%' will be applied.
- 条件“emp.ename like 'K%'”将首先应用于 EMP 的完整扫描
- 然后将通过 dept.deptno 上的索引选择匹配的 DEPT 记录（通过 NESTED LOOPS 方法）
- 最后过滤器“dept.loc like 'l%' 将被应用。
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:
这种应用顺序与 WHERE 子句中谓词的排序方式无关，正如我们可以用这个重新排序的查询显示的：
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")
回答by Greg
The database will decide what order to execute the conditions in.
数据库将决定执行条件的顺序。
Normally (but not always) it will use an index first where possible.
通常（但不总是）它会在可能的情况下首先使用索引。
回答by Dave Costa
As has been said, looking at the execution plan will give you some information. However, unless you use the plan stability feature, you can't rely on the execution plan always remaining the same.
如前所述，查看执行计划将为您提供一些信息。但是，除非您使用计划稳定性功能，否则您不能依赖执行计划始终保持不变。
In the case of the query you posted, it doesn't look like the order of evaluation will change the logic in any way, so I guess what you are thinking about is efficiency. It's fairly likely that the Oracle optimizer will choose a plan that is efficient.
在您发布的查询的情况下，评估顺序似乎不会以任何方式改变逻辑，所以我猜您正在考虑的是效率。Oracle 优化器很可能会选择一个有效的计划。
There are tricks you can do to encourage a particular ordering if you want to compare the performance with base query. Say for instance that you wanted the timestamp condition to be executed first. You could do this:
如果您想将性能与基本查询进行比较，您可以采取一些技巧来鼓励特定的排序。例如，假设您希望首先执行时间戳条件。你可以这样做：
WITH subset AS
( SELECT /*+ materialize */
FROM my_table
WHERE CURRENT_TIMESTAMP - 1 < x.CREATION_TIMESTAMP
)
SELECT *
FROM subset
WHERE
d.attribute3 = 'abcd*'
AND x.STATUS != 'P'
AND x.STATUS != 'J'
AND x.STATUS != 'X'
AND x.STATUS != 'S'
AND x.STATUS != 'D'
The "materialize" hint should cause the optimizer to execute the inline query first, then scan that result set for the other conditions.
“实现”提示应该使优化器首先执行内联查询，然后扫描该结果集以查找其他条件。
I'm not advising you do this as a general habit. In most cases just writing the simple query will lead to the best execution plans.
我不建议你把这作为一种普遍的习惯。在大多数情况下，只需编写简单的查询即可得出最佳执行计划。
回答by David Aldridge
To add to the other comments on execution plans, under the cpu-based costing model introduced in 9i and used by default in 10g+ Oracle will also make an assessment of which predicate evaluation order will result in lower computational cost even if that does not affect the table access order and method. If executing one predicate before another results in fewer predicates calculations being executed then that optimisaton can be applied.
补充一下关于执行计划的其他评论，在 9i 中引入并在 10g+ 中默认使用的基于 cpu 的成本计算模型下，Oracle 也会评估哪个谓词评估顺序会导致较低的计算成本，即使这不会影响表访问顺序和方法。如果在另一个谓词之前执行一个谓词会导致执行更少的谓词计算，则可以应用该优化。
See this article for more details: http://www.oracle.com/technology/pub/articles/lewis_cbo.html
有关更多详细信息，请参阅本文：http: //www.oracle.com/technology/pub/articles/lewis_cbo.html
Furthermore, Oracle doesn't even have to execute predicates where comparison with a check constraint or partition definitions indicates that no rows would be returned anyway.
此外，Oracle 甚至不必执行与检查约束或分区定义进行比较表明无论如何都不会返回任何行的谓词。
Complex stuff.
复杂的东西。
回答by Stew S
Finally, relational database theory says that you can neverdepend on the order of execution of the query clauses, so best not to try. As others have said, the cost-based optimizer tries to choose what it thinks is best, but even viewing explain planwon't guaranteethe actual order that's used. Explain plan just tells you what the CBO recommends, but that's still not 100%.
最后，关系数据库理论说你永远不能依赖查询子句的执行顺序，所以最好不要尝试。正如其他人所说，基于成本的优化器试图选择它认为最好的，但即使查看解释计划也不能保证使用的实际顺序。解释计划只是告诉您 CBO 的建议，但这仍然不是 100%。
Maybe if you explain why you're trying to do this, some could suggest a plan?
也许如果你解释你为什么要这样做，有些人可以提出一个计划？
回答by Nikolay Antipov
Tricky question. Just faced the same dilemma. I need to mention a function within a query. The function itself makes another query, so you understand how it affects performance in general. But in most cases we have, the function wouldn't be called so often if the rest of conditions executed first.
棘手的问题。刚刚面临同样的困境。我需要在查询中提及一个函数。该函数本身会进行另一个查询，因此您可以了解它通常如何影响性能。但在大多数情况下，如果先执行其余条件，则不会如此频繁地调用该函数。
Well, thought it would be useful to post here another article for topic.
好吧，认为在此处发布另一篇主题文章会很有用。
The following quote is copied from Donald Burleson's site (http://www.dba-oracle.com/t_where_clause.htm) .
以下引文是从Donald Burleson的站点 ( http://www.dba-oracle.com/t_where_clause.htm) 上复制的。
The ordered_predicateshint is specified in the Oracle WHERE clause of a query and is used to specify the order in which Boolean predicates should be evaluated.
In the absence of ordered_predicates, Oracle uses the following steps to evaluate the order of SQL predicates:
Subqueries are evaluated before the outer Boolean conditions in the WHERE clause.
All Boolean conditions without built-in functions or subqueries are evaluated in reverse from the order they are found in the WHERE clause, with the last predicate being evaluated first.
Boolean predicates with built-in functions of each predicate are evaluated in increasing order of their estimated evaluation costs.
所述ordered_predicates提示是在Oracle指定WHERE查询的子句和用于指定在其中布尔谓词应评估的顺序。
在没有ordered_predicates的情况下，Oracle 使用以下步骤来评估SQL 谓词的顺序：
子查询在 WHERE 子句中的外部布尔条件之前进行评估。
所有没有内置函数或子查询的布尔条件的计算顺序与它们在 WHERE 子句中的顺序相反，首先计算最后一个谓词。
具有每个谓词的内置函数的布尔谓词按照其估计评估成本的递增顺序进行评估。
