Oracle是否使用短路评估? [英] Does Oracle use short-circuit evaluation?

问题描述

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

I have an Oracle query that is structured as followed:

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

如果Oracle看到X等于'true'，它将仍然尝试评估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.

这篇关于Oracle是否使用短路评估?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！