The BULK COLLECT INTO clause can improve the performance of queries that reference collections.
For example, the following PL/SQL block queries multiple values into PL/SQL tables, both with and without bulk binds:
-- Find all employees whose manager's ID number is 7698.
DECLARE
TYPE Var_tab IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER;
Empno VAR_TAB;
Ename VAR_TAB;
Counter NUMBER;
CURSOR C IS
SELECT Empno, Ename FROM Emp_tab WHERE Mgr = 7698;
BEGIN
-- Efficient method, using a bulk bind
SELECT Empno, Ename BULK COLLECT INTO Empno, Ename
FROM Emp_Tab WHERE Mgr = 7698;
-- Slower method, assigning each collection element within a loop.
counter := 1;
FOR rec IN C LOOP
Empno(Counter) := rec.Empno;
Ename(Counter) := rec.Ename;
Counter := Counter + 1;
END LOOP;
END;
You can use BULK COLLECT INTO with tables of scalar values, or tables of %TYPE values.
Without the bulk bind, PL/SQL sends a SQL statement to the SQL engine for each employee that is selected, leading to context switches that hurt performance.
Ref: Oracle Document (PL/SQL Procedures and Packages)
|