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.

◉ Short-Circuit Evaluation of OR

◉ Short-Circuit Evaluation of AND

Short-Circuit Evaluation of OR

If left side of an OR expression is TRUE, the whole expression is TRUE. We know this because,

◉ TRUE OR FALSE = TRUE

◉ TRUE OR TRUE = TRUE

◉ TRUE OR NULL = TRUE

So placing the least expensive tests to the left of boolean expressions can potentially improve performance as the right hand side of the expression may not need to be evaluated.

Imagine we have a function that returns a boolean value. The amount of processing in the function is significant, making it take a long time to complete. The following function fakes this by calling the DBMS_LOCK.SLEEP procedure.

CONN / AS SYSDBA

GRANT EXECUTE ON DBMS_LOCK TO test;

CONN test/test

CREATE OR REPLACE FUNCTION slow_function (p_number  IN  NUMBER)

  RETURN BOOLEAN AS

BEGIN

  -- Mimic a slow function.

  DBMS_LOCK.sleep(0.5);

  RETURN TRUE;

END;

/

SHOW ERRORS

Depending on the boolean expression used, we may be able to avoid calling the function altogether, giving out code a significant performance improvement.

SET SERVEROUTPUT ON

DECLARE

  l_loops    NUMBER := 10;

  l_start    NUMBER;

  l_boolean  BOOLEAN := TRUE;

BEGIN

  -- Time normal OR.

  l_start := DBMS_UTILITY.get_time;

  FOR i IN 1 .. l_loops LOOP

    IF slow_function(i) OR l_boolean THEN

      -- Do nothing.

      NULL;

    END IF;

  END LOOP;

  DBMS_OUTPUT.put_line('Normal OR        : ' ||

                       (DBMS_UTILITY.get_time - l_start));

  -- Time short-circuit OR.

  l_start := DBMS_UTILITY.get_time;

  FOR i IN 1 .. l_loops LOOP

    IF l_boolean OR slow_function(i) THEN

      -- Do nothing.

      NULL;

    END IF;

  END LOOP;

  DBMS_OUTPUT.put_line('Short circuit OR : ' ||

                       (DBMS_UTILITY.get_time - l_start));

END;

/

Normal OR        : 498

Short circuit OR : 0

PL/SQL procedure successfully completed.

SQL>

As expected, if the call to the slow function is placed on the right-hand side of the expression, it is not executed, so the code is much quicker.

Short-Circuit Evaluation of AND

If the left side of an AND expression is FALSE, the whole expression is FALSE. We know this because,

FALSE AND FALSE = FALSE

FALSE AND TRUE = FALSE

FALSE AND NULL = FALSE

Once again, placing the least expensive tests to the left of boolean expressions can potentially improve performance as the right hand side of the expression may not need to be evaluated. We can demonstrate this using the slow function again.

SET SERVEROUTPUT ON

DECLARE

  l_loops    NUMBER := 10;

  l_start    NUMBER;

  l_boolean  BOOLEAN := FALSE;

BEGIN

  -- Time normal AND.

  l_start := DBMS_UTILITY.get_time;

  FOR i IN 1 .. l_loops LOOP

    IF slow_function(i) AND l_boolean THEN

      -- Do nothing.

      NULL;

    END IF;

  END LOOP;

  DBMS_OUTPUT.put_line('Normal AND       : ' ||

                       (DBMS_UTILITY.get_time - l_start));

  -- Time short-circuit AND.

  l_start := DBMS_UTILITY.get_time;

  FOR i IN 1 .. l_loops LOOP

    IF l_boolean AND slow_function(i) THEN

      -- Do nothing.

      NULL;

    END IF;

  END LOOP;

  DBMS_OUTPUT.put_line('Short circuit AND: ' ||

                       (DBMS_UTILITY.get_time - l_start));

END;

/

Normal AND       : 499

Short circuit AND: 0

PL/SQL procedure successfully completed.

SQL>

As expected, if the call to the slow function is placed on the right-hand side of the expression, it is not executed, so the code is much quicker.

Source: oracle-base.com