DBA Hub

📋Steps in this guide1/2

Short-Circuit Evaluation in PL/SQL

This article describes how short-circuit evaluation can be used to improve the performance of some boolean expressions.

oracle miscconfigurationintermediate
by OracleDba
33 views
1

Short-Circuit Evaluation of OR

If left side of an 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 procedure. Depending on the boolean expression used, we may be able to avoid calling the function altogether, giving out code a significant performance improvement. 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.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
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

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>
2

Short-Circuit Evaluation of AND

If the left side of an expression is FALSE, the whole expression is FALSE. We know this because, 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. 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. For more information see: - Short-Circuit Evaluation Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
FALSE AND FALSE = FALSE
FALSE AND TRUE = FALSE
FALSE AND NULL = FALSE

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>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!