msgbartop
News, views, tips and tricks on Oracle and other fun stuff
msgbarbottom

The order of conditions matters

Do you think that “IF condition1 AND condition2″ is the same as “IF condition2 AND condition1″? The answer is yes and no. Yes because both give the same Boolean result. No, because when evaluating a logical expression, Oracle PL/SQL uses short-circuit evaluation. That is, PL/SQL stops evaluating the expression as soon as the result can be determined, and this may impact the processing of your code as we will see next.

To see short-circuit evaluation in action, let’s take this example:

SQL> CREATE TABLE t (x NUMBER)
  2  /

Table created.

SQL> CREATE OR REPLACE FUNCTION IS_GREATER_FUNC
  2  (num1 NUMBER, num2 NUMBER)
  3  RETURN BOOLEAN AS
  4  BEGIN
  5    INSERT INTO t VALUES (1);
  6    COMMIT;
  7    IF num1 > num2 THEN RETURN TRUE;
  8    ELSE RETURN FALSE; END IF;
  9  END;
 10  /

Function created.

In addition to performing a simple comparison and returning true or false, the function inserts a row into the table t as an indication for me that the function executed.

SQL> set serveroutput on
SQL> BEGIN
  2  IF 2 = 1 AND IS_GREATER_FUNC (1,2)
  3  THEN DBMS_OUTPUT.PUT_LINE('TRUE');
  4  ELSE DBMS_OUTPUT.PUT_LINE('FALSE');
  5  END IF;
  6  END;
  7  /
FALSE

PL/SQL procedure successfully completed.

SQL> select * from t;

no rows selected

PL/SQL stops evaluating the second condition (the function) when it detects that 2 = 1 is FALSE, because the THEN branch is executed only when the result of the Boolean expression is TRUE, and that requires all conditions to be TRUE.

SQL> BEGIN
  2  IF 2 = 2 AND IS_GREATER_FUNC (1,2)
  3  THEN DBMS_OUTPUT.PUT_LINE('TRUE');
  4  ELSE DBMS_OUTPUT.PUT_LINE('FALSE');
  5  END IF;
  6  END;
  7  /
FALSE

PL/SQL procedure successfully completed.

SQL> select * from t;

         X
----------
         1

Now that 2 = 2 is true, PL/SQL continues with the evaluation of the second condition (the function), as proven by the new row inserted into t.

This short-circuit evaluation can be useful when writing expressions that might otherwise cause an error, for example:

SQL> BEGIN
  2  IF 0 = 0 OR (2/0 > 1)
  3  THEN DBMS_OUTPUT.PUT_LINE ('TRUE');
  4  END IF;
  5  END;
  6  /
TRUE

PL/SQL procedure successfully completed.

The division by zero did not return an error because it was not evaluated because the THEN branch is executed only when the result of the Boolean expression is TRUE, and that requires one of the conditions to be TRUE (0 = 0 is TRUE).

Changing the first condition to FALSE, will trigger an error as the second condition is evaluated:

SQL> BEGIN
  2  IF 0 = 1 OR (2/0 > 1)
  3  THEN DBMS_OUTPUT.PUT_LINE ('TRUE');
  4  END IF;
  5  END;
  6  /
BEGIN
*
ERROR at line 1:
ORA-01476: divisor is equal to zero
ORA-06512: at line 2

You can also take advantage of this short-circuit evaluation feature in PL/SQL when one of your conditions is CPU or memory expensive. In such case, place that condition last.

Finally, short-circuit evaluation not only applies to IF statements, but also to CASE statements, and CASE expressions in PL/SQL.


Filed in Oracle, Tips on 03 Mar 06 | Tags:


Reader's Comments

  1. |

    Interesting. But is this behaviour dependable? And, if so, will it remain like this in future versions? And, even if it is, is that necessarily the best coding practise? Especially since you can just do this:

    IF (2=1) THEN IF (IS_GREATER_FUNC(1,2)) THEN DBMS_OUTPUT.PUT_LINE(‘TRUE’); ELSE DBMS_OUTPUT.PUT_LINE(‘FALSE’); END IF; ELSE DBMS_OUTPUT.PUT_LINE(‘FALSE’); END IF;

    That way, your intention (to only check the second case if the first case is true) is clear, and dependable. Sure, it’s more lines of code, but easier to maintain.

    Rob

  2. |

    Good and valid points Rob. Thank you. Nevertheless, the short-circuit evaluation notion in PL/SQL is rather an interesting fact (as of 10gR2).

  3. |

    If (and only if) this behaviour is a standard in PLSQL, then one could argue that the more concise code that can be written using this IS more maintainable. More verbose code, especially if it requires the setting up and initialisation of more unnecessary variables, can reduce the maintainability of that code!

  4. |

    Its NOT ALWAYS guaranted the in

    select from my_table where cond1 and cond2;

    cond1 be first evaluated. For a query I was doing, a must use:

    select /*+ no_cpu_costing */ from my_table where cond1 and cond2;

    Look at

    http://www.dba-oracle.com/oracle_tips_optimizer_cost_model.htm

  5. |

    Quote iG “It is NOT ALWAYS guaranted…” then iG goes on to show an example in SQL. But remember that the order of evaluation in SQL is not as written.

    SQL> CREATE OR REPLACE FUNCTION this_is_y(x NUMBER) RETURN VARCHAR2 IS
      2  BEGIN
      3    dbms_output.put_line('Inside function this_is_Y(' || x || ')');
      4    RETURN 'Y';
      5  END this_is_Y;
      6  /
    
    Function created.
    
    SQL> CREATE OR REPLACE FUNCTION this_is_n(x NUMBER) RETURN VARCHAR2 IS
      2  BEGIN
      3    dbms_output.put_line('Inside function this_is_N(' || x || ')');
      4    RETURN 'N';
      5  END this_is_n;
      6  /
    
    Function created.
    
    SQL> SELECT *
      2  FROM   (SELECT object_id
      3          FROM   user_objects
      4          WHERE  9 > rownum)
      5  WHERE  this_is_y(object_id) = 'Y'
      6  AND    this_is_n(object_id) = 'Y';
    
    no rows selected
    
    SQL>
    

    And the output is:

    Inside function this_is_N(174702)
    Inside function this_is_N(592838)
    Inside function this_is_N(592839)
    Inside function this_is_N(592840)
    Inside function this_is_N(592841)
    Inside function this_is_N(592842)
    Inside function this_is_N(592843)
    Inside function this_is_N(592844)