ABC-Groep

The importance of collections in Oracle PL/SQL

Introduction

Today I will be addressing some cool features in PL/SQL that are often not taken advantage of.
Even to this day I often see people write code without using collections, even though they exist already for a long time. Many people learned PL/SQL and moved on with their current knowledge. But time does not stand still and many things have been optimized and changed in the Oracle Database.

Cursor problem

One of the things I (unfortunately) come across too often is creating cursors, explicitly or implicitly. The main rule is to avoid keeping them opened for a long time. Opening and opened cursors when not necessary affects general performance, opened cursors use memory and fetching from them requires I/O as Oracle needs to read data blocks on disk. Nowadays, Oracle is pretty good at creating good optimization plans based on correct statistics, but it can still heavily impact your performance.

Using collections as an alternative

AsĀ stated in my previous blogpost, using collections is much faster than writing cursor for loops as it prevents context switching. It also reads more in-memory data instead of disk data. This means it reduces I/O.
Here are 2 examples. The first one uses a "classic" for loop, the second uses a bulk operation.

DECLARE
  CURSOR c_emps
  IS
    SELECT e.ename employee, e.job, m.ename manager, 
           d.dname department, d.loc location
    FROM emp e, emp m, dept d
    WHERE e.deptno = d.deptno
    AND m.mgr(+) = e.empno
    ORDER BY MANAGER NULLS FIRST
  ;
  
  PROCEDURE p(s IN VARCHAR2)
  IS
  BEGIN
    dbms_output.put_line(s);
  END; 
  
  PROCEDURE ph(s IN VARCHAR2) -- fancy heading
  IS
  BEGIN
    dbms_output.put_line(chr(13)||chr(10));
    dbms_output.put_line(rpad('-',length(s)+6,'-'));
    dbms_output.put_line('-- '||s||' --');
    dbms_output.put_line(rpad('-',length(s)+6,'-'));
    dbms_output.put_line(chr(13)||chr(10));
  END;     
BEGIN

  ph('Using "classic" cursor for loop');
  
  FOR r_emps IN c_emps 
  LOOP 
    p(r_emps.employee||'|'
    ||r_emps.job||'|'
    ||r_emps.manager||'|'
    ||r_emps.department||'|'
    ||r_emps.location);
  END LOOP; -- cursor gets closed after the loop  
END;
/
Example 2.1 - using a "classic" cursor for loop



DECLARE
  CURSOR c_emps
  IS
    SELECT e.ename employee, e.job, m.ename manager, 
           d.dname department, d.loc location
    FROM emp e, emp m, dept d
    WHERE e.deptno = d.deptno
    AND m.mgr(+) = e.empno
    ORDER BY MANAGER NULLS FIRST
  ;
  
  TYPE t_emps_tab IS TABLE OF c_emps%ROWTYPE 
                  INDEX BY PLS_INTEGER;  

  v_emps_tab t_emps_tab;  
  
  PROCEDURE p(s IN VARCHAR2)
  IS
  BEGIN
    dbms_output.put_line(s);
  END; 
  
  PROCEDURE ph(s IN VARCHAR2) -- fancy heading
  IS
  BEGIN
    dbms_output.put_line(chr(13)||chr(10));
    dbms_output.put_line(rpad('-',length(s)+6,'-'));
    dbms_output.put_line('-- '||s||' --');
    dbms_output.put_line(rpad('-',length(s)+6,'-'));
    dbms_output.put_line(chr(13)||chr(10));
  END;     
BEGIN

  ph('using bulk and collection');

  OPEN c_emps;
  FETCH c_emps BULK COLLECT INTO v_emps_tab;
  CLOSE c_emps; -- note that the cursor is closed before processing the data
      
  FOR i IN v_emps_tab.first .. v_emps_tab.last
  LOOP
    p(v_emps_tab(i).employee||'|'
    ||v_emps_tab(i).job||'|'
    ||v_emps_tab(i).manager||'|'
    ||v_emps_tab(i).department||'|'
    ||v_emps_tab(i).location||'|');
  END LOOP;
END;
/
Example 2.2 - Using bulk collect

In the simple example above it seems rather cumbersome, but imagine more complex problems, such as opening other cursors inside a cursor for loop, or calling stored procedures that take some time in the for-loop. In the second example your first cursor is already closed, so the golden rule is toclose the cursor as soon as possible.
Wij gebruiken cookies om ervoor te zorgen dat onze website voor de bezoeker beter werkt. Daarnaast gebruiken wij o.a. cookies voor onze webstatistieken. Meer informatie