Oracle 是否使用短路评估？

Does Oracle use short-circuit evaluation?

提问by aoi222

I have an Oracle query that is structured as followed:

我有一个结构如下的 Oracle 查询：

SELECT   *
FROM     table
WHERE    X='true' OR
         Y IN (complicated subquery)

If Oracle sees that X does equal 'true' will it still try to evaluate the Y IN (subquery) part of the WHERE clause? Also, in a statement such as this would the subquery be executed multiple times for each entry in the table? Would I be better off with something like:

如果 Oracle 发现 X 确实等于“真”，它还会尝试评估 WHERE 子句的 Y IN（子查询）部分吗？此外，在这样的语句中，子查询是否会针对表中的每个条目执行多次？我会不会更好地使用以下内容：

WITH subQ as (complicated subquery)
SELECT   *
FROM     table
WHERE    X='true' OR
         Y IN (SELECT id FROM subQ)

回答by Justin Cave

It depends. . In general, Oracle does not guarantee that a SQL statement will use short-circuit evaluation (though PL/SQL is guaranteed to perform short-circuit evaluation). The Oracle optimizer is free to evaluate the predicates in whatever order it expects to be most efficient. That might mean that the first predicate is evaluated first and only the matching rows have the second predicate evaluated but it is entirely possible that either the reverse happens or that Oracle transforms the query into a sort of UNIONand fully evaluates both predicates before combining the results.

这取决于。. 一般情况下，Oracle 不保证 SQL 语句会使用短路求值（尽管 PL/SQL 保证执行短路求值）。Oracle 优化器可以按照它认为最有效的任何顺序自由地评估谓词。这可能意味着首先评估第一个谓词，只有匹配的行才评估第二个谓词，但完全有可能发生相反的情况，或者 Oracle 将查询转换为某种类型UNION并在组合结果之前完全评估这两个谓词。

That being said, if the optimizer can determine at compile time that a predicate will always evaluate to TRUEor FALSE, the optimizer should just treat that as a constant. So if, for example, there is a constraint on the table that prevents Xfrom ever having a value of 'true', the optimizer shouldn't evaluate the second predicate at all (though different versions of the optimizer will have different abilities to detect that something is a constant at compile time).

话虽如此，如果优化器可以在编译时确定谓词总是评估为TRUEor FALSE，优化器应该将其视为常量。因此，例如，如果表上的约束阻止X了“true”的值，则优化器根本不应评估第二个谓词（尽管不同版本的优化器将具有不同的检测能力）有些东西在编译时是一个常数）。

As for the second part of your question, without seeing the query plans, it's very hard to tell. The Oracle optimizer tends to be pretty good at transforming queries from one form to another if there are more efficient ways of evaluating it. In general, however, if subQis going to return a relatively large number of rows compared to table, it may be more efficient to structure the query as an EXISTSrather than as an IN.

至于你的问题的第二部分，没有看到查询计划，很难说。如果有更有效的评估方法，Oracle 优化器往往非常擅长将查询从一种形式转换为另一种形式。但是，一般而言，如果subQ要返回与 相比相对较多的行table，则将查询结构化为 anEXISTS而不是可能更有效IN。

回答by Jonathan Leffler

Caveat: Oracle is not my primary area of expertise.

警告：Oracle 不是我的主要专业领域。

The cost-based optimizer should know that the cost of X = 'true'is less than the sub-query, so it will likely evaluate the simpler alternative first. But the AND and OR conditions in SQL are notshort-circuited like &&and ||are in C and its derivatives.

基于成本的优化器应该知道 的成本X = 'true'小于子查询，因此它可能会首先评估更简单的替代方案。但AND和OR在SQL条件不短路一样&&，并||在C及其衍生物。

The sub-query can be one of two forms: correlated and non-correlated.

子查询可以是以下两种形式之一：相关和非相关。

• A correlated sub-query must be executed many times (which is why they are dangerous to performance) because the correlation means that the sub-query result depends in some way on the row 'currently being evaluated').
• A non-correlated sub-query will only be executed once.

• 相关子查询必须执行多次（这就是它们对性能有害的原因），因为相关性意味着子查询结果在某种程度上取决于“当前正在评估”的行。
• 不相关的子查询只会执行一次。

Example correlated sub-query:

示例相关子查询：

SELECT *
  FROM Table1
 WHERE X = 'true'
    OR Y IN (SELECT Z FROM Table2 WHERE Table2.A = Table1.B)

Example non-correlated sub-query:

示例非相关子查询：

SELECT *
  FROM Table1
 WHERE X = 'true'
    OR Y IN (SELECT Z FROM Table2 WHERE Table2.A > 13)

回答by mathguy

Regardless of what the optimizer may or may not do with ANDand OR, if for any reason you must enforce a specific order of evaluation, you can rewrite the query, using other tools where short-circuit evaluation is guaranteed.

无论优化器可能会或可能不会对ANDand做什么OR，如果出于任何原因您必须强制执行特定的评估顺序，您都可以使用其他保证短路评估的工具重写查询。

For example:

例如：

select * from table 1
where case when X = 'true' then 1
           when Y in (select ....)   then 1
      end  = 1

If X is 'true' then the case expression evaluates to 1, the second "when" is skipped and the condition evaluates to TRUE. If X is not 'true' then the IN condition is evaluated.

如果 X 为“真”，则 case 表达式的计算结果为 1，跳过第二个“when”，条件计算结果为 TRUE。如果 X 不是“真”，则评估 IN 条件。

回答by sf_jeff

I came here looking for an answer on how to avoid crashing using short circuit evaluation. What I eventually got working is:

我来到这里寻找有关如何使用短路评估避免崩溃的答案。我最终得到的工作是：

...
where case when [its not going to crash] 
           then [short circuit expression] 
           else [safe, never used value] 
       end = comparison_value
...

So, for example, if you are worried about a to_number expression crashing, you would put something like "REGEXP_LIKE(my_possible_number, '^[[:digit:]]+$')" in the when clause (for positive integers - adjust for non-positive or non-integer).

因此，例如，如果您担心 to_number 表达式崩溃，您可以在 when 子句中添加类似“REGEXP_LIKE(my_possible_number, '^[[:digit:]]+$')”的内容（对于正整数 - 调整为非正数或非整数）。