Performance Tests On Iterating Collections In Oracle

There are some ways to iterate collections on Oracle. One is simple for loop, the other is FIRST-NEXT method. On performance metrics it is better to use for loop, it gains time. FIRST-NEXT method is simply linked list. You take first index and then you take the next index.

SQL>
SQL> DECLARE
  2    TYPE occupation_table IS TABLE OF VARCHAR2(16);
  3    occupations occupation_table;
  4  BEGIN
  5    occupations := occupation_table('Salesman', 'Student', 'Engineer', 'Teacher', 'Architect');
  6    FOR j IN 1 .. 1000000 LOOP
  7      FOR i IN occupations.FIRST .. occupations.LAST LOOP
  8        NULL;
  9      END LOOP;
 10    END LOOP;
 11  END;
 12  /

PL/SQL procedure successfully completed

Executed in 0,172 seconds

SQL>
SQL> DECLARE
  2    TYPE occupation_table IS TABLE OF VARCHAR2(16);
  3    occupations occupation_table;
  4    k           NUMBER;
  5  BEGIN
  6    occupations := occupation_table('Salesman', 'Student', 'Engineer', 'Teacher', 'Architect');
  7    FOR j IN 1 .. 1000000 LOOP
  8      k := occupations.FIRST;
  9      WHILE k IS NOT NULL LOOP
 10        k := occupations.NEXT(k);
 11      END LOOP;
 12    END LOOP;
 13  END;
 14  /

PL/SQL procedure successfully completed

Executed in 1,359 seconds

There are some situations you can not use simple for loop. When you delete in collection or collection subscripts are not incremented sequentially by one you have to use second way. And if your subscripts are not number, you again use second way.


SQL> DECLARE
  2    TYPE occupation_table IS TABLE OF VARCHAR2(16);
  3    occupations occupation_table;
  4  BEGIN
  5    occupations := occupation_table('Salesman', 'Student', 'Engineer', 'Teacher', 'Architect');
  6    occupations.DELETE(2);
  7    FOR i IN occupations.FIRST .. occupations.LAST LOOP
  8      dbms_output.put_line('occupations(' || i || ') is ' || occupations(i));
  9    END LOOP;
 10  END;
 11  /

occupations(1) is Salesman

DECLARE
  TYPE occupation_table IS TABLE OF VARCHAR2(16);
  occupations occupation_table;
BEGIN
  occupations := occupation_table('Salesman', 'Student', 'Engineer', 'Teacher', 'Architect');
  occupations.DELETE(2);
  FOR i IN occupations.FIRST .. occupations.LAST LOOP
    dbms_output.put_line('occupations(' || i || ') is ' || occupations(i));
  END LOOP;
END;
ORA-01403: no data found
ORA-06512: at line 8

SQL> DECLARE
  2    TYPE occupation_table IS TABLE OF VARCHAR2(16);
  3    occupations occupation_table;
  4    k           NUMBER;
  5  BEGIN
  6    occupations := occupation_table('Salesman', 'Student', 'Engineer', 'Teacher', 'Architect');
  7    occupations.DELETE(2);
  8    k := occupations.FIRST;
  9    WHILE k IS NOT NULL LOOP
 10      dbms_output.put_line('occupations(' || k || ') is ' || occupations(k));
 11      k := occupations.NEXT(k);
 12    END LOOP;
 13  END;
 14  /

occupations(1) is Salesman
occupations(3) is Engineer
occupations(4) is Teacher
occupations(5) is Architect

PL/SQL procedure successfully completed

Executed in 0,015 seconds

SQL>