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.

Related articles:

Tagged | Comments Closed | Trackbacks Closed