Does Oracle use short-circuit evaluation?

Question

I have an Oracle query that is structured as followed:

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:

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

Answer 1

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.

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).

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.

Answer 2

Caveat: Oracle is not my primary area of expertise.

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 not short-circuited like && and || are in C and its derivatives.

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)