Oracle PL/SQL Best Practices

Name

SQL-14: Use BULK COLLECT to improve performance of multi-row queries (Oracle8i).

Synopsis

Recognizing that you often need to return large numbers of rows from the database, Oracle8i offers a new BULK COLLECT clause for queries. When you use BULK COLLECT, you retrieve multiple rows of data in a single request to the RDBMS. The data is then deposited into a series of collections.

Example

To use BULK COLLECT, you need to declare collections to hold all the retrieved data. Then, preface your INTO clause with the BULK COLLECT keywords, and you are done:

CREATE OR REPLACE PROCEDURE process_employees 
   (deptno_in IN dept.deptno%TYPE)
RETURN emplist_t
IS
   TYPE numTab IS TABLE OF emp.empno%TYPE;
   TYPE charTab IS TABLE OF emp.ename%TYPE;
   TYPE dateTab IS TABLE OF emp.hiredate%TYPE;
   enos numTab;
   names charTab;
   hdates dateTab;
BEGIN 
   SELECT empno, ename, hiredate
      BULK COLLECT INTO enos, names, hdates
      FROM emp
     WHERE deptno = deptno_in;
   ...
END process_employees;

Or, if you are using an explicit cursor:

BEGIN 
   OPEN emp_cur INTO emp_rec;
   FETCH emp_cur BULK COLLECT INTO enos, names, hdates;

Benefits

You will see an improvement (in some cases, a dramatic improvement) in query performance.

Challenges

You must declare a separate collection for each element in the SELECT list.

You must be careful when the SELECT returns many thousands of rows. There could be many users running the same program in a session, which can lead to memory problems. Try to restrict the bulk collection by using ROWNUM, for instance. ...