Oracle 8i | Oracle 9i | Oracle 10g | Oracle 11g | Oracle 12c | Miscellaneous | PL/SQL | SQL | Oracle RAC | Oracle Apps | Linux

Logic/Branch Ordering in PL/SQL

PL/SQL evaluates CASE and ELSIF statements from the top down. As a result, placing the most likely outcomes at the top reduces the amount of evaluations that must be done, improving the performance of your code. This article demonstrates the affect of branch ordering on the performance of CASE and ELSIF statements.

CASE

The following example compares the performance of two CASE statements. The first will always complete after the first evaluation, while the second will complete at the final evaluation.

SET SERVEROUTPUT ON
DECLARE
  l_loops  NUMBER := 1000000;
  l_value  VARCHAR2(1) := 'A';
  l_start  NUMBER;
BEGIN
  -- Time CASE first.
  l_start := DBMS_UTILITY.get_time;

  FOR i IN 1 .. l_loops LOOP
    CASE l_value
      WHEN 'A' THEN
        NULL;
      WHEN 'B' THEN
        NULL;
      WHEN 'C' THEN
        NULL;
      WHEN 'D' THEN
        NULL;
      WHEN 'E' THEN
        NULL;
    END CASE;
  END LOOP;

  DBMS_OUTPUT.put_line('CASE first : ' ||
                       (DBMS_UTILITY.get_time - l_start));

  -- Time CASE first.
  l_start := DBMS_UTILITY.get_time;

  FOR i IN 1 .. l_loops LOOP
    CASE l_value
      WHEN 'B' THEN
        NULL;
      WHEN 'C' THEN
        NULL;
      WHEN 'D' THEN
        NULL;
      WHEN 'E' THEN
        NULL;
      WHEN 'A' THEN
        NULL;
    END CASE;
  END LOOP;

  DBMS_OUTPUT.put_line('CASE last  : ' ||
                       (DBMS_UTILITY.get_time - l_start));
END;
/
CASE first : 5
CASE last  : 20

PL/SQL procedure successfully completed.

SQL>

As expected, the code is measurably faster if the most likely outcomes are placed at the top of the list.

ELSIF

The following example is similar to that shown previously, but the CASE statements have been replaced by ELSIF lists. The first will always complete after the first evaluation, while the second will complete at the final evaluation.

SET SERVEROUTPUT ON
DECLARE
  l_loops  NUMBER := 1000000;
  l_value  VARCHAR2(1) := 'A';
  l_start  NUMBER;
BEGIN
  -- Time ELSIF first.
  l_start := DBMS_UTILITY.get_time;

  FOR i IN 1 .. l_loops LOOP
    IF l_value = 'A' THEN
      NULL;
    ELSIF l_value = 'B' THEN
      NULL;
    ELSIF l_value = 'C' THEN
      NULL;
    ELSIF l_value = 'D' THEN
      NULL;
    ELSIF l_value = 'E' THEN
      NULL;
    END IF;
  END LOOP;

  DBMS_OUTPUT.put_line('ELSIF first: ' ||
                       (DBMS_UTILITY.get_time - l_start));

  -- Time ELSIF last.
  l_start := DBMS_UTILITY.get_time;

  FOR i IN 1 .. l_loops LOOP
    IF l_value = 'B' THEN
      NULL;
    ELSIF l_value = 'C' THEN
      NULL;
    ELSIF l_value = 'D' THEN
      NULL;
    ELSIF l_value = 'E' THEN
      NULL;
    ELSIF l_value = 'A' THEN
      NULL;
    END IF;
  END LOOP;

  DBMS_OUTPUT.put_line('ELSIF last : ' ||
                       (DBMS_UTILITY.get_time - l_start));
END;
/
ELSIF first: 5
ELSIF last : 18

PL/SQL procedure successfully completed.

SQL>

Once again, the code is measurably faster if the most likely outcomes are placed at the top of the list.

Hope this helps. Regards Tim...

Back to the Top.