Does Oracle use short-circuit evaluation?

evaluate function in oracle
does oracle or short circuit
pl/sql evaluate expression

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)

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.

Short-Circuit Evaluation in PL/SQL, It depends. . In general, Oracle does not guarantee that a SQL statement will use short-circuit evaluation (though PL/SQL is guaranteed to  Short-Circuit Evaluation in PL/SQL As soon as the final outcome of a boolean expression can be determined, PL/SQL stops evaluating the expression. This is known as short-circuit evaluation and it can be used to improve the performance of some boolean expressions in your PL/SQL.

Does Oracle use short-circuit evaluation?, As logical expressions are evaluated left to right, they are tested for possible “​short-circuit” evaluation using these rules: false && anything is short-circuit  Short-Circuit Evaluation. anything is short-circuit evaluated to true. The rules of logic guarantee that these evaluations are always correct.

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.

Short-Circuit Evaluation, Does Oracle perform short-circuit boolean evaluation when evaluating in order to take advantage of short circuiting techniques, I have to use DECODE and  For PL/SQL Oracle assures that it will use short-circuit evaluation: When evaluating a logical expression, PL/SQL uses short-circuit evaluation. That is, PL/SQL stops evaluating the expression as soon as it can determine the result. Therefore, you can write expressions that might otherwise cause errors. From: 2 PL/SQL Language Fundamentals

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

Evaluation of expressions - Ask Tom, Oracle Database uses short-circuit evaluation. The database evaluates each expr value and determines whether it is NULL, rather than  This content has been marked as final. Show 11 replies. If I remember correctly, Oracle does do short circuit evaluation. Howver, Oracle evaluates the WHERE clause like this. If an index is used as the access path, then that column (s) is evaluated first. Then, the remaining conditions are evaluated from bottom to top.

Will a SELECT . . . INTO use short-circuit evaluation for a COALESCE?, You can use a CASE expression in any statement or clause that accepts a valid Oracle uses short-circuit evaluation for the simple CASE expression. It means  See Database SQL Language Reference (Oracle 12c): COALESCE(), where it says: Oracle Database uses short-circuit evaluation. The database evaluates each expr value and determines whether it is NULL, rather than evaluating all of the expr values before determining whether any of them is NULL.

Using Oracle CASE Expression By Practical Examples, D provides the following binary logical operators for use in your programs. The logical AND operator performs short-circuit evaluation: if the left-hand operand is false, the The logical XOR operator does not short-circuit: both expression operands are always evaluated. 2010, Oracle Corporation and/or its affiliates. Oracle Database uses short-circuit evaluation. For a simple CASE expression, the database evaluates each comparison_expr value only before comparing it to expr, rather than evaluating all comparison_expr values before comparing any of them with expr.

Logical Operators, Short-circuit evaluation, minimal evaluation, or McCarthy evaluation (after John McCarthy) is the semantics of some Boolean operators in some programming  select decode ( (select f (null) from dual), null, (SELECT f (2) FROM DUAL), (select f (null) from dual) ) from ( select (select f (null) from dual) f1 from dual ); the subquery (select f (null) from dual) maybe evaluated only once due to shortcircuiting and hence no visible demonstration of subquery caching.

Comments
  • "though PL/SQL is guaranteed to perform short-circuit evaluation" where do you take that from? I tried this and get an error: declare var varchar2(30); begin select 'short circuit' into var from dual where 1=0 and 1/0 = 0; end;
  • @IsaacKleinman - That's still SQL. If you put the conditions in PL/SQL, you'll see short circuiting. begin if( 1<2 or 1/0 = 1 ) then dbms_output.put_line( 'Short circuit' ); end if; end;
  • Hi Justin, am I reading the query plan right that SELECT * FROM abc WHERE 1 = 0 does not short-circuit? Can I get it to short-circuit? (Yes, I have a real use case for this; it's not just a thought experiment. :) )
  • @JonSeigel - I would expect WHERE 1 = 0 to short-circuit. Oracle does not guarantee that a SQL statement will use short-circuit evaluation but if you're comparing two different constants, the optimizer should detect that and determine that it doesn't actually have to do anything. If that's not what you're seeing, that may need to be a separate question that includes the query plan you're looking at.
  • In the Fiddle I linked to, there's TABLE ACCESS FULL / FILTER / SELECT. I'm not an Oracle expert, but that doesn't look like a constant scan to me, and so I sought your opinion after finding this answer in search. Should I ask a new question (on DBA.SE, of course)?