The importance of collections

Youri Vanderleyden 19/03/2018

The importance of collections

Door Dennis

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;
/

The importance of collections