Theme 2: Using NOCOPY and BULK COLLECT

Redneck

Dress Code

Oracle tuning

Oracle training

Oracle support

Remote Oracle

Oracle Tips by Burleson

Theme 2: Using NOCOPY and BULK COLLECT

This theme will cover an important technique that can dramatically improve the speed of PL/SQL code. The first area is the NOCOPY and BULK COLLECT operators that allow fast loading and retrieval of PL/SQL tables.

When PL/SQL parameters hold large data structures, all the copying (between SQL & PL/SQL) slows down execution and uses up memory. To prevent that, you can specify the NOCOPY operator, which allows the PL/SQL compiler to pass OUT and IN OUT parameters by reference. Let’s start with a simple example. To do this, we will create a PL/SQL code block as bulk_collect.sql.

Step 1 – define a PL/SQL table of employee numbers:

DECLARE
TYPE Numlist IS TABLE OF emp.empno%TYPE;
Id Numlist;

Step 2 – Next, we define a procedure called GetEmp that load the table with all of the employees for a department. Note that the procedure GetEmp has two arguments:

* Input – A department number

* Output – The list of employee numbers from the array. Note the NOCOPY parameters which calls the list by reference:

Inside the GetEmp procedure we use the bulk collect operator to load all matching employee numbers into the Numlist table:

DECLARE
PROCEDURE GetEmp (pDeptNo IN NUMBER, pId OUT NOCOPY Numlist) IS
BEGIN
    SELECT empno BULK COLLECT INTO pId
     FROM emp
    WHERE deptno = pDeptNo;
END;
BEGIN

Step 3 – Now we are ready to try calling our GetEmp procedure, passing the value of “10” for the department number, and Id to point the output into the Numlist array:

GetEmp(10,Id);

Step 4 – At this point, we should have loaded the NumList with a list of all employee numbers in department 10. We can use a simple FOR loop to display the values:

    FOR i IN Id.FIRST..Id.LAST LOOP
      dbms_output.put_line(Id(i));
    END LOOP;

Here is the completed script, and the resulting output:

DECLARE
TYPE Numlist IS TABLE OF emp.empno%TYPE;
Id Numlist;
PROCEDURE GetEmp (pDeptNo IN NUMBER, pId OUT NOCOPY Numlist) IS
BEGIN
    SELECT empno BULK COLLECT INTO pId
     FROM emp
    WHERE deptno = pDeptNo;
END;
BEGIN
    GetEmp(10,Id);
    FOR i IN Id.FIRST..Id.LAST LOOP
      dbms_output.put_line(Id(i));
    END LOOP;
END;
/

7782
7839
7934

In sum, using the bulk collect operator makes it super-fast to load PL/SQL tables. You should always consider using a bulk collect whenever you must load a large amount of table data into a PL/SQL array.

For more details, see the "Easy Oracle Series" a set of books especially designed by Oracle experts to get you started fast with Oracle database technology.

Oracle performance Tuning 10g reference poster

Oracle training & performance tuning books