Oracle 是否使用短路评估?
我有一个结构如下的 Oracle 查询:
I have an Oracle query that is structured as followed:
SELECT *
FROM table
WHERE X='true' OR
Y IN (complicated subquery)
如果 Oracle 发现 X 确实等于真"，它还会尝试评估 WHERE 子句的 Y IN(子查询)部分吗?此外，在这样的语句中，子查询是否会针对表中的每个条目执行多次?我会不会更好地使用以下内容:
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:
WITH subQ as (complicated subquery)
SELECT *
FROM table
WHERE X='true' OR
Y IN (SELECT id FROM subQ)
推荐答案
视情况而定..一般情况下，Oracle 不保证 SQL 语句会使用短路求值(尽管 PL/SQL 保证执行短路求值).Oracle 优化器可以按照它认为最有效的任何顺序自由地评估谓词.这可能意味着首先评估第一个谓词，只有匹配的行才评估第二个谓词，但完全有可能发生相反的情况，或者 Oracle 将查询转换为一种 UNION 并且完全在组合结果之前评估两个谓词.
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 UNION and fully evaluates both predicates before combining the results.
话虽如此，如果优化器可以在编译时确定谓词的计算结果始终为 TRUE 或 FALSE，优化器应该将其视为常量.因此，例如，如果表上有一个约束，阻止 X 的值为 'true'，优化器根本不应该评估第二个谓词(尽管不同版本的优化器将具有不同的能力来在编译时检测某些内容是否为常量).
That being said, if the optimizer can determine at compile time that a predicate will always evaluate to TRUE or FALSE, the optimizer should just treat that as a constant. So if, for example, there is a constraint on the table that prevents X from 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).
至于你问题的第二部分，没有看到查询计划，很难说.如果有更有效的评估方法，Oracle 优化器往往非常擅长将查询从一种形式转换为另一种形式.但是，一般而言，如果 subQ 与 table 相比将返回相对较多的行，则将查询结构化为 EXISTS 可能更有效 而不是 IN.
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 subQ is going to return a relatively large number of rows compared to table, it may be more efficient to structure the query as an EXISTS rather than as an IN.
相关文章