Bulk collect: is a CLAUSE. is used to fetch the records from the cursor.
Forall: is a STATEMENT. is used to do dml operation of fetched records.
The body of the FORALL statement is a single DML statement -- an INSERT, UPDATE, or DELETE.
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
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."
So there you go. Collections, BULK COLLECT and FORALL are the new features in Oracle 8i, 9i and 10g PL/SQL that can really make a different to you PL/SQL performance.
Hopefully, if you've not come across these areas before.
----------------------------------------------------------------------------
BULK COLLECT Syntax & Example:
FETCH BULK COLLECTBULK COLLECT INTO
LIMIT;
set timing on
DECLARE
CURSOR a_cur IS
SELECT program_id
FROM airplanes;
BEGIN
FOR cur_rec IN a_cur LOOP
NULL;
END LOOP;
END;
/
DECLARE
CURSOR a_cur IS
SELECT program_id
FROM airplanes;
TYPE myarray IS TABLE OF a_cur%ROWTYPE;
cur_array myarray;
BEGIN
OPEN a_cur;
LOOP
FETCH a_cur BULK COLLECT INTO cur_array LIMIT 100;
EXIT WHEN a_cur%NOTFOUND;
END LOOP;
CLOSE a_cur;
END;
/
DECLARE
CURSOR a_cur IS
SELECT program_id
FROM airplanes;
TYPE myarray IS TABLE OF a_cur%ROWTYPE;
cur_array myarray;
BEGIN
OPEN a_cur;
LOOP
FETCH a_cur BULK COLLECT INTO cur_array LIMIT 500;
EXIT WHEN a_cur%NOTFOUND;
END LOOP;
CLOSE a_cur;
END;
/
DECLARE
CURSOR a_cur IS
SELECT program_id
FROM airplanes;
TYPE myarray IS TABLE OF a_cur%ROWTYPE;
cur_array myarray;
BEGIN
OPEN a_cur;
LOOP
FETCH a_cur BULK COLLECT INTO cur_array LIMIT 1000;
EXIT WHEN a_cur%NOTFOUND;
END LOOP;
CLOSE a_cur;
END;
/
-- try with a LIMIT clause of 2500, 5000, and 10000. What do you see?
----------------------------------------------------------------------------
FORALL Syntax & Example:
FORALLIN ..
SAVE EXCEPTIONS;
FORALLIN INDICES OF
[BETWEENAND ]
SAVE EXCEPTIONS;
FORALLIN INDICES OF
VALUES OF
SAVE EXCEPTIONS;
FOR INSERT
===========
CREATE TABLE servers2 AS
SELECT *
FROM servers
WHERE 1=2;
DECLARE
CURSOR s_cur IS
SELECT *
FROM servers;
TYPE fetch_array IS TABLE OF s_cur%ROWTYPE;
s_array fetch_array;
BEGIN
OPEN s_cur;
LOOP
FETCH s_cur BULK COLLECT INTO s_array LIMIT 1000;
FORALL i IN 1..s_array.COUNT
INSERT INTO servers2 VALUES s_array(i);
EXIT WHEN s_cur%NOTFOUND;
END LOOP;
CLOSE s_cur;
COMMIT;
END;
/
FOR UPDATE
===========
SELECTDISTINCT srvr_id
FROM servers2
ORDER BY 1;
DECLARE
TYPE myarray IS TABLE OF servers2.srvr_id%TYPE
INDEX BY BINARY_INTEGER;
d_array myarray;
BEGIN
d_array(1) := 608;
d_array(2) := 610;
d_array(3) := 612;
FORALL i IN d_array.FIRST .. d_array.LAST
UPDATE servers2
SET srvr_id = 0
WHERE srvr_id = d_array(i);
COMMIT;
END;
/
SELECT srvr_id
FROM servers2
WHERE srvr_id = 0;
FOR DELETE
============
set serveroutput on
DECLARE
TYPE myarray IS TABLE OF servers2.srvr_id%TYPE
INDEX BY BINARY_INTEGER;
d_array myarray;
BEGIN
d_array(1) := 614;
d_array(2) := 615;
d_array(3) := 616;
FORALL i IN d_array.FIRST .. d_array.LAST
DELETE servers2
WHERE srvr_id = d_array(i);
COMMIT;
FOR i IN d_array.FIRST .. d_array.LAST LOOP
dbms_output.put_line('Iteration #' || i || ' deleted ' ||
SQL%BULK_ROWCOUNT(i) || ' rows.');
END LOOP;
END;
/
SELECT srvr_id
FROM servers2
WHERE srvr_id IN (614, 615, 616);
====================================================
Forall: is a STATEMENT. is used to do dml operation of fetched records.
The body of the FORALL statement is a single DML statement -- an INSERT, UPDATE, or DELETE.
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
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."
So there you go. Collections, BULK COLLECT and FORALL are the new features in Oracle 8i, 9i and 10g PL/SQL that can really make a different to you PL/SQL performance.
Hopefully, if you've not come across these areas before.
----------------------------------------------------------------------------
BULK COLLECT Syntax & Example:
FETCH BULK COLLECT
LIMIT
set timing on
DECLARE
CURSOR a_cur IS
SELECT program_id
FROM airplanes;
BEGIN
FOR cur_rec IN a_cur LOOP
NULL;
END LOOP;
END;
/
DECLARE
CURSOR a_cur IS
SELECT program_id
FROM airplanes;
TYPE myarray IS TABLE OF a_cur%ROWTYPE;
cur_array myarray;
BEGIN
OPEN a_cur;
LOOP
FETCH a_cur BULK COLLECT INTO cur_array LIMIT 100;
EXIT WHEN a_cur%NOTFOUND;
END LOOP;
CLOSE a_cur;
END;
/
DECLARE
CURSOR a_cur IS
SELECT program_id
FROM airplanes;
TYPE myarray IS TABLE OF a_cur%ROWTYPE;
cur_array myarray;
BEGIN
OPEN a_cur;
LOOP
FETCH a_cur BULK COLLECT INTO cur_array LIMIT 500;
EXIT WHEN a_cur%NOTFOUND;
END LOOP;
CLOSE a_cur;
END;
/
DECLARE
CURSOR a_cur IS
SELECT program_id
FROM airplanes;
TYPE myarray IS TABLE OF a_cur%ROWTYPE;
cur_array myarray;
BEGIN
OPEN a_cur;
LOOP
FETCH a_cur BULK COLLECT INTO cur_array LIMIT 1000;
EXIT WHEN a_cur%NOTFOUND;
END LOOP;
CLOSE a_cur;
END;
/
-- try with a LIMIT clause of 2500, 5000, and 10000. What do you see?
----------------------------------------------------------------------------
FORALL Syntax & Example:
FORALL
SAVE EXCEPTIONS;
FORALL
[BETWEEN
SAVE EXCEPTIONS;
FORALL
VALUES OF
SAVE EXCEPTIONS;
FOR INSERT
===========
CREATE TABLE servers2 AS
SELECT *
FROM servers
WHERE 1=2;
DECLARE
CURSOR s_cur IS
SELECT *
FROM servers;
TYPE fetch_array IS TABLE OF s_cur%ROWTYPE;
s_array fetch_array;
BEGIN
OPEN s_cur;
LOOP
FETCH s_cur BULK COLLECT INTO s_array LIMIT 1000;
FORALL i IN 1..s_array.COUNT
INSERT INTO servers2 VALUES s_array(i);
EXIT WHEN s_cur%NOTFOUND;
END LOOP;
CLOSE s_cur;
COMMIT;
END;
/
FOR UPDATE
===========
SELECTDISTINCT srvr_id
FROM servers2
ORDER BY 1;
DECLARE
TYPE myarray IS TABLE OF servers2.srvr_id%TYPE
INDEX BY BINARY_INTEGER;
d_array myarray;
BEGIN
d_array(1) := 608;
d_array(2) := 610;
d_array(3) := 612;
FORALL i IN d_array.FIRST .. d_array.LAST
UPDATE servers2
SET srvr_id = 0
WHERE srvr_id = d_array(i);
COMMIT;
END;
/
SELECT srvr_id
FROM servers2
WHERE srvr_id = 0;
FOR DELETE
============
set serveroutput on
DECLARE
TYPE myarray IS TABLE OF servers2.srvr_id%TYPE
INDEX BY BINARY_INTEGER;
d_array myarray;
BEGIN
d_array(1) := 614;
d_array(2) := 615;
d_array(3) := 616;
FORALL i IN d_array.FIRST .. d_array.LAST
DELETE servers2
WHERE srvr_id = d_array(i);
COMMIT;
FOR i IN d_array.FIRST .. d_array.LAST LOOP
dbms_output.put_line('Iteration #' || i || ' deleted ' ||
SQL%BULK_ROWCOUNT(i) || ' rows.');
END LOOP;
END;
/
SELECT srvr_id
FROM servers2
WHERE srvr_id IN (614, 615, 616);
====================================================
In this article, I will cover the two most important of these features: BULK COLLECT and FORALL.
- BULK COLLECT: SELECT statements that retrieve multiple rows with a single fetch, improving the speed of data retrieval
- FORALL: INSERTs, UPDATEs, and DELETEs that use collections to change multiple rows of data very quickly
You may be wondering what very quickly might mean—how much impact do these features really have? Actual results will vary, depending on the version of Oracle Database you are running and the specifics of your application logic. You can download and run the script to compare the performance of row-by-row inserting with FORALL inserting. On my laptop running Oracle Database 11g Release 2, it took 4.94 seconds to insert 100,000 rows, one at a time. With FORALL, those 100,000 were inserted in 0.12 seconds. Wow!
=============================================================
BULK COLLECT & FORALL vs. CURSOR & FOR-LOOP
After more and more reads about BULK COLLECT and FORALL and their performance improvements I decided to have a closer look on it by myself to see how powerful they really are. So I built a little test-case which inserts all entries from the all_object view into another table. The inserts happens on three different ways:
First way is a simple cursor over the view and a insert in a loop with FETCH into local variables. This way also shows how slow the opening of the cursor itself is.
The second way is a simple FOR – IN LOOP with the insert of the cursor variables.
And, of course, the third way is the way with bulking the rows and inserting them with FORALL so lets see.
First way is a simple cursor over the view and a insert in a loop with FETCH into local variables. This way also shows how slow the opening of the cursor itself is.
The second way is a simple FOR – IN LOOP with the insert of the cursor variables.
And, of course, the third way is the way with bulking the rows and inserting them with FORALL so lets see.
So the other table looks like this (three columns are enough for this tests)
SQL> create table temp (owner varchar2(30), name varchar2(30), type varchar2(19));
Table created.
And the three diffrent procedures looks like this
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
| CREATE OR REPLACE PROCEDURE CURSOR_FOR_OPEN_QUERY IS l_sOwner VARCHAR2(30); l_sName VARCHAR2(30); l_sType VARCHAR2(19); CURSOR cur IS SELECT owner, object_name name, object_type type FROM all_objects; BEGIN dbms_output.put_line('Before CURSOR OPEN: ' || systimestamp); OPEN cur; dbms_output.put_line('Before LOOP: ' || systimestamp); LOOP FETCH cur INTO l_sOwner, l_sName, l_sType; IF cur%NOTFOUND THEN EXIT; END IF; INSERT INTO temp values (l_sOwner, l_sName, l_sType); END LOOP; CLOSE cur; dbms_output.put_line('After CURSOR CLOSE: ' || systimestamp); COMMIT; END; /CREATE OR REPLACE PROCEDURE CURSOR_FOR_QUERY IS BEGIN dbms_output.put_line('Before CURSOR: ' || systimestamp); FOR cur IN (SELECT owner, object_name name, object_type type FROM all_objects) LOOP INSERT INTO temp values (cur.owner, cur.name, cur.type); END LOOP; dbms_output.put_line('After CURSOR: ' || systimestamp); COMMIT; END; /CREATE OR REPLACE PROCEDURE BULK_COLLECT_QUERY IS TYPE sOwner IS TABLE OF VARCHAR2(30); TYPE sName IS TABLE OF VARCHAR2(30); TYPE sType IS TABLE OF VARCHAR2(19); l_sOwner sOwner; l_sName sName; l_sType sType; BEGIN dbms_output.put_line('Before Bulk Collect: ' || systimestamp); SELECT owner, object_name, object_type BULK COLLECT INTO l_sOwner, l_sName, l_sType FROM all_objects; dbms_output.put_line('After Bulk Collect: ' || systimestamp); -- FORALL indx IN l_sName.FIRST..l_sName.LAST INSERT INTO temp values (l_sOwner(indx), l_sName(indx), l_sType(indx)); -- dbms_output.put_line('After FORALL: ' || systimestamp); COMMIT; END; / |
Ok, then I bounced the database to get no buffers, caching, etc. on it.
So the first execute
SQL> exec cursor_for_open_query
Before CURSOR OPEN: 27-SEP-07 10.56.30.699401000 AM +02:00
Before LOOP: 27-SEP-07 10.56.30.922366000 AM +02:00
After CURSOR CLOSE: 27-SEP-07 10.57.07.699791000 AM +02:00
Before CURSOR OPEN: 27-SEP-07 10.56.30.699401000 AM +02:00
Before LOOP: 27-SEP-07 10.56.30.922366000 AM +02:00
After CURSOR CLOSE: 27-SEP-07 10.57.07.699791000 AM +02:00
Only look at the seconds it took 37 seconds and nearly nothing for opening the cursor! But how much rows were inserted?
SQL> select count(*) from temp;
COUNT(*)
———-
49424
———-
49424
Truncate the table (truncate to free the extends!) and bounce the database again and now the second run
SQL> exec cursor_for_query
Before CURSOR: 27-SEP-07 10.59.47.848249000 AM +02:00
After CURSOR: 27-SEP-07 11.00.09.072525000 AM +02:00
Before CURSOR: 27-SEP-07 10.59.47.848249000 AM +02:00
After CURSOR: 27-SEP-07 11.00.09.072525000 AM +02:00
The whole loop took 22 seconds, well this looks already better. Well, also all rows inserted?
SQL> select count(*) from temp;
COUNT(*)
———-
49424
———-
49424
But now (after truncate and bouncing) the bulk collect run
SQL> exec bulk_collect_query
Before Bulk Collect: 27-SEP-07 11.01.33.553224000 AM +02:00
After Bulk Collect: 27-SEP-07 11.01.41.874054000 AM +02:00
After FORALL: 27-SEP-07 11.01.42.065753000 AM +02:00
Before Bulk Collect: 27-SEP-07 11.01.33.553224000 AM +02:00
After Bulk Collect: 27-SEP-07 11.01.41.874054000 AM +02:00
After FORALL: 27-SEP-07 11.01.42.065753000 AM +02:00
Look at this, for bulking all the lines into the collection took just 8 seconds (for 49 424 rows) and the inserts just 1 second! Unbelievable, together we did everything in 9 seconds where the other ways took over 20 seconds!
Well now lets try to first execute the bulk load then truncate the table again but not bouncing the database so that the buffers and caches a still filled
SQL> exec bulk_collect_query
Before Bulk Collect: 27-SEP-07 11.02.31.257498000 AM +02:00
After Bulk Collect: 27-SEP-07 11.02.41.614205000 AM +02:00
After FORALL: 27-SEP-07 11.02.41.818092000 AM +02:00
Before Bulk Collect: 27-SEP-07 11.02.31.257498000 AM +02:00
After Bulk Collect: 27-SEP-07 11.02.41.614205000 AM +02:00
After FORALL: 27-SEP-07 11.02.41.818092000 AM +02:00
PL/SQL procedure successfully completed.
SQL> select count(*) from temp;
COUNT(*)
———-
49423
———-
49423
SQL> truncate table temp;
Table truncated.
SQL> exec cursor_for_query
Before CURSOR: 27-SEP-07 11.04.04.960254000 AM +02:00
After CURSOR: 27-SEP-07 11.04.25.749038000 AM +02:00
Before CURSOR: 27-SEP-07 11.04.04.960254000 AM +02:00
After CURSOR: 27-SEP-07 11.04.25.749038000 AM +02:00
Ok so now we need 10 seconds for the run with the bulk but we sill need 21 seconds for the cursor! So not really a improvement with the cache and so on. Ok final test on a big system with over 268 thousand rows
Before Bulk Collect: 27-SEP-07 11.24.17.034732000 AM +02:00
After Bulk Collect: 27-SEP-07 11.24.25.111020000 AM +02:00
After FORALL: 27-SEP-07 11.24.26.129826000 AM +02:00
PL/SQL procedure successfully completed.
After Bulk Collect: 27-SEP-07 11.24.25.111020000 AM +02:00
After FORALL: 27-SEP-07 11.24.26.129826000 AM +02:00
PL/SQL procedure successfully completed.
COUNT(*)
———-
267985
———-
267985
Table truncated.
Before CURSOR: 27-SEP-07 11.24.29.629354000 AM +02:00
After CURSOR: 27-SEP-07 11.25.02.244549000 AM +02:00
PL/SQL procedure successfully completed.
After CURSOR: 27-SEP-07 11.25.02.244549000 AM +02:00
PL/SQL procedure successfully completed.
COUNT(*)
———-
268056
And again, bulking took 8 seconds and the inserts just 1 second! But the run with the cursor took 33 seconds!
———-
268056
And again, bulking took 8 seconds and the inserts just 1 second! But the run with the cursor took 33 seconds!
So this was just a short test but it definitely shows that BULK COLLECT and FORALL are much faster than cursors within the FOR loop! Only disadvantage of FORALL as you maybe already guess if you looked at the code: You can just perform one DML statement, there is no “FORALL END” clause! But anyway also bulking is a very high-performance functionality of Oracle! So if you have to run throw data collections then use BULK COLLECT!
