bulk collect and forall in oracle pl/sql

Using of BULK COLLECT and FORALL for better performance
PL/SQL engine sends SQL statements to the SQL engine, which returns results to the PL/SQL engine.
The communication between the PL/SQL and SQL engines is called a context switch. A certain amount of performance overhead is associated with these context switches. However, the PL/SQL language has a number of features that can minimize the performance overhead known as bulk SQL. Generally, if a SQL
statement affects four or more rows, bulk SQL may improve performance significantly. Bulk SQL supports batch processing of SQL statements and their results. It consists of two features—the FORALL statement and the BULK COLLECT clause.


FORALL is:

"The keyword FORALL instructs the PL/SQL engine to bulk-bind input collections before sending them to the SQL engine. Although the FORALL statement contains an  iteration scheme, it is not a FOR loop.

Its syntax follows:

FORALL index IN lower_bound..upper_bound
   sql_statement;

The index can be referenced only within the FORALL statement and only as a collection subscript. The SQL statement must be an INSERT, UPDATE, or DELETE statement that
references collection elements. And, the bounds must specify a valid range of consecutive index numbers. The SQL engine executes the SQL statement once for each index
number in the range."

DECLARE
 TYPE NumList IS VARRAY(20) OF NUMBER;
 Depts   NumList := NumList(10, 30, 70); -- department numbers
 BEGIN
 FORALL i IN depts.FIRST..depts.LAST
 DELETE FROM emp WHERE deptno = depts(i);
 END;

BULK COLLECT is:

"The keywords BULK COLLECT tell the SQL engine to bulk-bind output collections before returning them to the PL/SQL engine. You can use these keywords in the SELECT  INTO, FETCH INTO, and RETURNING INTO clauses.

Here is the syntax:

... BULK COLLECT INTO collection_name[, collection_name] ..."
and FORALL is defined as





DECLARE
TYPE NumTab IS TABLE OF emp.empno%TYPE;
TYPE NameTab IS TABLE OF emp.ename%TYPE;
enums NumTab; -- no need to initialize
names NameTab;
BEGIN
SELECT empno, ename BULK COLLECT INTO enums, names FROM emp;

END;
…….
DECLARE
TYPE SalList IS TABLE OF emp.sal%TYPE;
sals SalList;
BEGIN
SELECT sal BULK COLLECT INTO sals FROM emp
WHERE ROWNUM <= 100;
...
END;
………
Example of a Bulk Fetch from a Cursor
The following example shows that you can bulk-fetch from a cursor into one or
more collections:
DECLARE
TYPE NameList IS TABLE OF emp.ename%TYPE;
TYPE SalList IS TABLE OF emp.sal%TYPE;
CURSOR c1 IS SELECT ename, sal FROM emp WHERE sal > 1000;
names NameList;
sals SalList;
BEGIN
OPEN c1;
FETCH c1 BULK COLLECT INTO names, sals;

END;
…………………
Retrieving DML Results into a Collection with the RETURNING INTO Clause
You can use the BULK COLLECT clause in the RETURNING INTO clause of an
INSERT, UPDATE, or DELETE statement, as the following example shows:
DECLARE
TYPE NumList IS TABLE OF emp.empno%TYPE;
enums NumList;

BEGIN
DELETE FROM emp WHERE deptno = 20
RETURNING empno BULK COLLECT INTO enums;
-- if there were five employees in department 20,
-- then enums contains five employee numbers
END;

Using FORALL and BULK COLLECT Together

FORALL j IN depts.FIRST..depts.LAST
DELETE FROM emp WHERE empno = depts(j)
RETURNING empno BULK COLLECT INTO enums;

You cannot use the SELECT ... BULK COLLECT statement in a FORALL statement.
Otherwise, you get the error implementation restriction: cannot use FORALL and BULK
COLLECT INTO together in SELECT statements.




SQL> create table t_all_objects as select * from all_objects;

Table created.

SQL> insert into t_all_objects select * from t_all_objects;

3332 rows created.

SQL> r
1* insert into t_all_objects select * from t_all_objects

6664 rows created.

---replicated a couple of times

SQL> select count(*) from t_all_objects;

COUNT(*)
----------
213248

SQL> declare
cursor c1 is select object_name from t_all_objects;
2    3  rec1 c1%rowtype;
4  begin
5  open c1;
6  loop
7     fetch c1 into rec1;
8     exit when c1%notfound;
9
10     null;
11
12  end loop;
13  end;
14  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:44.75

SQL> declare
2  cursor c1 is select object_name from t_all_objects;
3  type c1_type is table of c1%rowtype;
4  rec1 c1_type;
5  begin
6  open c1;
7
8     fetch c1 bulk collect into rec1;
9
10
11  end;
12  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:05.32
As can be clearly seen, bulk collecting the rows shows a huge performance improvement over fetching row by row.

The above method (which fetched all the rows) may not be applicable to all cases. When there are many rows to process, we can limit the number of rows to bulk collect, process those rows and fetch again. Otherwise process memory gets bigger and bigger as you fetch the rows.
SQL> declare
2  cursor c1 is select object_name from t_all_objects;
3  type c1_type is table of c1%rowtype;
4  rec1 c1_type;
5  begin
6  open c1;
7  loop
8     fetch c1 bulk collect into rec1 limit 200;
9     for i in 1..rec1.count loop
10             null;
11     end loop;
12     exit when c1%notfound;
13  end loop;
14
15
16  end;
17  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:04.07