Does Oracle use short-circuit evaluation?

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.

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)

Regardless of what the optimizer may or may not do with AND and 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.

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.

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

Does Oracle use short-circuit evaluation?

Related

Recent Posts