20

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)
Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
aoi222
  • 723
  • 1
  • 5
  • 11

4 Answers4

35

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.

Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • "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;` – Isaac Kleinman Feb 08 '12 at 17:25
  • 6
    @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;` – Justin Cave Feb 08 '12 at 17:54
  • Hi Justin, am I reading the query plan right that [`SELECT * FROM abc WHERE 1 = 0`](http://sqlfiddle.com/#!4/644d7/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. :) ) – Jon Seigel Apr 01 '14 at 17:44
  • @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. – Justin Cave Apr 01 '14 at 17:48
  • 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)? – Jon Seigel Apr 01 '14 at 17:56
  • Conveniently, SQLFiddle appears to have exploded at the minute. – Jon Seigel Apr 01 '14 at 17:58
  • @JonSeigel - Ahh, didn't notice that your query was a link. Let me take a look once it's back up. – Justin Cave Apr 01 '14 at 18:00
  • It's back now. They must've been deploying. Thanks. – Jon Seigel Apr 01 '14 at 18:02
  • @JonSeigel - That's doing short-circuit evaluation. The `filter` step is a special `null is not null` operation that allows Oracle to avoid actually looking at the table. For example, if I populate the table with 200,000 rows, the execution time doesn't change. If I add a predicate that would do a division by 0 (http://sqlfiddle.com/#!4/5885d/1) I don't get an error even though that predicate is applied to the table scan which would appear to come before the filter. – Justin Cave Apr 01 '14 at 18:25
  • I got a division by zero error when I tried it... I have too many follow-up questions for comments now, so I'll ask a question on DBA. Thanks. – Jon Seigel Apr 01 '14 at 18:53
13

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)
Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
2

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.

  • This doesn't enforce short circuit evaluation. It may or may not happen. Try selecting sequences in several exclusive clauses. For something like this: `select case when 1=1 then 'A' WHEN 1=2 THEN 'A' || some_seq.NEXTVAL else 'A' || some_seq.NEXTVAL end from dual;` they all execute. – Gerrat Apr 07 '20 at 14:20
  • @Gerrat - Oracle does take liberties, sometimes, with the expressions in the `then` clause. It does not with the expressions in the `when` clause. Rewrite your example to call `NEXTVAL` in the `when` clauses - what happens then? –  Apr 07 '20 at 14:31
  • Putting the sequence in the `select`, like your example leads to `ORA-02287: sequence number not allowed here`. My goal was to *avoid* calling `some_seq.NEXTVAL` in some cases via short-circuit, but I don't think it's possible. – Gerrat Apr 07 '20 at 15:00
1

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

sf_jeff
  • 374
  • 2
  • 10