Does Oracle Use Short-Circuit Evaluation

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.

Does Oracle use short-circuiting logical operators?

Yes, there is short-circuit evaluation in PL/SQL:

When evaluating a logical expression, PL/SQL uses short-circuit evaluation. That is, PL/SQL stops evaluating the expression as soon as the result can be determined. This lets you write expressions that might otherwise cause an error.

Source: Fundamentals of the PL/SQL Language

CASE and COALESCE short-circuit evaluation works with sequences in PL/SQL but not in SQL

Explanation of why the short-circuit evaluation does not apply to sequences might be the following. What is a sequence? Putting internals aside, it's a combination of sequence definition(record in seq$ data dictionary table) and some internal SGA component, it's not a function and might be considered, although the documentation does not state it directly(but execution plan does) as row source. And every time a sequence is being referenced directly in the select list of a query, it has to be evaluated by the optimizer when it searches for optimal execution plan. During the process of forming an optimal execution plan a sequence gets incremented if nextval pseudocolumn is referenced:

SQL> create sequence seq1;
Sequence created

Here is our sequence:

SQL> select o.obj#
  2       , o.name
  3       , s.increment$
  4       , s.minvalue
  5       , s.maxvalue
  6       , s.cache
  7    from sys.seq$ s
  8    join sys.obj$ o
  9       on (o.obj# = s.obj#)
 10    where o.name = 'SEQ1'
 11  ;


      OBJ# NAME    INCREMENT$   MINVALUE   MAXVALUE      CACHE
---------- ------- ---------- ---------- ---------- ----------
     94442 SEQ1             1          1       1E28         20

Lets trace below query, and also take a look at its execution plan

SQL> ALTER SESSION SET EVENTS '10046 trace name context forever, level 4';
Session altered

SQL> select case
  2           when 1 = 1 then 1
  3           when 2 = 1 then seq1.nextval
  4         end as res
  5    from dual;

       RES
----------
         1

/* sequence got incremented by 1 */

SQL> select seq1.currval from dual;

   CURRVAL
----------
         3

Trace file information:

STAT #1016171528 id=1 cnt=1 pid=0 pos=1 obj=94442 op='SEQUENCE SEQ1 ...

STAT #1016171528 id=2 cnt=1 pid=1 pos=1 obj=0 op='FAST DUAL ...

CLOSE #1016171528:c=0,e=12,dep=0,type=0,tim=12896600071500 /* close the cursor */

The execution plan will show us basically the same:

SQL> explain plan for select case
  2                            when 1 = 1 then 1
  3                            else seq1.nextval
  4                          end
  5                      from dual
  6  /
Explained
Executed in 0 seconds

SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
---------------------------------------------------------------
Plan hash value: 51561390
-----------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     2   (0)| 00:00:01 |
|   1 |  SEQUENCE        | SEQ1 |       |            |          |
|   2 |   FAST DUAL      |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------
9 rows selected
Executed in 0.172 seconds

In terms of evaluation, referencing a sequence directly in a query, roughly the same as including a correlated sub-query. That correlated sub-query will always be evaluated by the optimizer:

SQL> explain plan for select case
  2                            when 1 = 1 then 1
  3                            when 2 = 1 then (select 1
  4                                               from dual)
  5                          end as res
  6                      from dual;
Explained
Executed in 0 seconds

SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------
Plan hash value: 1317351201
-----------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     4   (0)| 00:00:01 |
|   1 |  FAST DUAL       |      |     1 |     2   (0)| 00:00:01 |
|   2 |  FAST DUAL       |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------
9 rows selected
Executed in 0.063 seconds  

We can see that dual table has been included in the execution plan twice.

The analogy with a sub-query was made in a rush. There are more differences than similarities, of course. Sequences are absolutely different mechanisms. But, a sequences are viewed by the optimizer as a row source, and as long as it doesn't see the nextval pseudocolumn of a sequence being directly referenced in the select list of a top-level query, it won't evaluate the sequence, otherwise sequence will be incremented, whether a short-circuit evaluation logic is being used or not. PL/SQL engine,obviously, (starting from Oracle 11g r1) has a different way to access a sequence value. Should be noted that in previous 11gR1 versions of RDBMS we should write a query to reference a sequence in PL/SQL block, which PL/SQL engine sent directly to the SQL engine.

The answer to the "why a sequence gets incremented during generating an execution plan by the optimizer" question, lies in the internal implementation of sequences.

Oracle CASE short-circuit not working in group by

Use

CASE WHEN cond = 2 
     THEN SUM(case when div = 0 then 0 else num / div end)
     ELSE -1
END

No short-circuit OR with an Oracle function?

It doesn't lose the short-circuit aspect. But SQL is not a procedural language, and there is no guarantee of the order of evaluation of multiple predicates.

In C, if you write a || b, you know that a will be evaluated first, then b will be evaluated only if necessary.

In SQL, if you write a OR b, you know only that either a or b will be evaluated first, and that the other expression (at least in Oracle) will be evaluated only if necessary.

Looking at the execution plan for the two queries may give some indication of the order of evaluation, or it may not.

I would guess that, in your first case, Oracle can see that the first expression will have the same value for every row, so evaluates it first. When you change to the second case, Oracle now sees a function that could have different results each time it is evaluated, so it will have to check each row, so it tries to do the simple equality check on a column before doing the function call.

I wonder if you would get different results if you marked the function DETERMINISTIC so Oracle would know that it is essentially a constant.

Is the SQL WHERE clause short-circuit evaluated?

ANSI SQL Draft 2003 5WD-01-Framework-2003-09.pdf

6.3.3.3 Rule evaluation order

[...]

Where the precedence is not determined by the Formats or by
parentheses, effective evaluation of expressions is generally
performed from left to right. However, it is
implementation-dependent whether expressions are actually evaluated left to right, particularly when operands or operators might
cause conditions to be raised or if the results of the expressions
can be determined without completely evaluating all parts of the
expression.

Oracle CASE WHEN Short-Circuit Return Expressions

Oracle does process the CASE expressions in order. Unfortunately, because the aggregation, it also needs to evaluate all the aggregation expressions before it gets around to the CASE. And that is where the error is occurring. Note: I'm not sure where this is documented for Oracle, but I've seen the problem in other databases.

The solution is to use another CASE expression to avoid errors:

WITH tbl AS (
      SELECT 0 as val, 'avg' as calc_type FROM DUAL
      UNION ALL
      SELECT 1 as val, 'geom_mean' FROM DUAL
     )
SELECT CASE WHEN max(calc_type) = 'avg' THEN AVG(val)
       ELSE EXP(AVG(LN(CASE WHEN val > 0 THEN val END))) END as res
FROM tbl
GROUP BY calc_type

How does Oracle perform OR condition validation?

PL/SQL

In PL/SQL, Oracle OR is another example of short circuit evaluation. Oracle PL/SQL Language Fundamentals says (in part)

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.

SQL

However, in regular SQL, the OR might be evaluated in either order. As pointed out by @JonHeller in his comment the expressions in this question are safe, more caution would be required if dealing with potential division by 0.

---

Related Topics