FORALL & BULK COLLECT

The FORALL syntax allows us to bind the contents of a collection to a single DML statement, allowing the DML to be run for each row in the collection without requiring a context switch each time. To test bulk binds using records we first create a test table.

SET SERVEROUTPUT ON
DECLARE
TYPE t_forall_test_tab IS TABLE OF forall_test%ROWTYPE;

l_tab t_forall_test_tab := t_forall_test_tab();
l_start NUMBER;
l_size NUMBER := 2000;
BEGIN
-- Populate collection.
FOR i IN 1 .. l_size LOOP
l_tab.extend;

l_tab(l_tab.last).id := i;
l_tab(l_tab.last).code := TO_CHAR(i);
l_tab(l_tab.last).description := 'Description: ' || TO_CHAR(i);
END LOOP;

EXECUTE IMMEDIATE 'TRUNCATE TABLE forall_test';

-- Time regular inserts.
l_start := DBMS_UTILITY.get_time;

FOR i IN l_tab.first .. l_tab.last LOOP
INSERT INTO forall_test (id, code, description)
VALUES (l_tab(i).id, l_tab(i).code, l_tab(i).description);
END LOOP;

DBMS_OUTPUT.put_line('Normal Inserts: ' ||
(DBMS_UTILITY.get_time - l_start));

EXECUTE IMMEDIATE 'TRUNCATE TABLE forall_test';

-- Time bulk inserts.
l_start := DBMS_UTILITY.get_time;

FORALL i IN l_tab.first .. l_tab.last
INSERT INTO forall_test VALUES l_tab(i);

DBMS_OUTPUT.put_line('Bulk Inserts : ' ||
(DBMS_UTILITY.get_time - l_start));

COMMIT;
END;
/
SET SERVEROUTPUT ON
DECLARE
TYPE t_id_tab IS TABLE OF forall_test.id%TYPE;
TYPE t_forall_test_tab IS TABLE OF forall_test%ROWTYPE;

l_id_tab t_id_tab := t_id_tab();
l_tab t_forall_test_tab := t_forall_test_tab ();
l_start NUMBER;
l_size NUMBER := 2000;
BEGIN
-- Populate collections.
FOR i IN 1 .. l_size LOOP
l_id_tab.extend;
l_tab.extend;

l_id_tab(l_id_tab.last) := i;
l_tab(l_tab.last).id := i;
l_tab(l_tab.last).code := TO_CHAR(i);
l_tab(l_tab.last).description := 'Description: ' || TO_CHAR(i);
END LOOP;

-- Time regular updates.
l_start := DBMS_UTILITY.get_time;

FOR i IN l_tab.first .. l_tab.last LOOP
UPDATE forall_test
SET ROW = l_tab(i)
WHERE id = l_tab(i).id;
END LOOP;

DBMS_OUTPUT.put_line('Normal Updates : ' ||
(DBMS_UTILITY.get_time - l_start));

l_start := DBMS_UTILITY.get_time;

-- Time bulk updates.
FORALL i IN l_tab.first .. l_tab.last
UPDATE forall_test
SET ROW = l_tab(i)
WHERE id = l_id_tab(i);

DBMS_OUTPUT.put_line('Bulk Updates : ' ||
(DBMS_UTILITY.get_time - l_start));

COMMIT;
END;
/
SET SERVEROUTPUT ON
DECLARE
TYPE t_id_tab IS TABLE OF forall_test.id%TYPE;
TYPE t_forall_test_tab IS TABLE OF forall_test%ROWTYPE;

l_id_tab t_id_tab := t_id_tab();
l_tab t_forall_test_tab := t_forall_test_tab ();
l_start NUMBER;
l_size NUMBER := 2000;
BEGIN
-- Populate collections.
FOR i IN 1 .. l_size LOOP
l_id_tab.extend;
l_tab.extend;

l_id_tab(l_id_tab.last) := i;
l_tab(l_tab.last).id := i;
l_tab(l_tab.last).code := TO_CHAR(i);
l_tab(l_tab.last).description := 'Description: ' || TO_CHAR(i);
END LOOP;

-- Time regular updates.
l_start := DBMS_UTILITY.get_time;

FOR i IN l_tab.first .. l_tab.last LOOP
DELETE forall_test
WHERE id = l_tab(i).id;
END LOOP;

DBMS_OUTPUT.put_line('Normal Delete : ' ||
(DBMS_UTILITY.get_time - l_start));

l_start := DBMS_UTILITY.get_time;

-- Time bulk updates.
FORALL i IN l_tab.first .. l_tab.last
DELETE forall_test
WHERE id = l_id_tab(i);

DBMS_OUTPUT.put_line('Bulk Deletes : ' ||
(DBMS_UTILITY.get_time - l_start));

COMMIT;
END;
/

From Oracle 10g onward, the optimizing PL/SQL compiler converts cursor FOR LOOPs into BULK COLLECTs with an array size of 100. The following example compares the speed of a regular cursor FOR LOOP with BULK COLLECTs using varying array sizes.

http://www.oracle-base.com/articles/9i/bulk-binds-and-record-processing-9i.php
http://www.oracle-base.com/articles/11g/plsql-new-features-and-enhancements-11gr1.php#forall_restriction_removed
http://www.oracle.com/technetwork/issue-archive/2012/12-sep/o52plsql-1709862.html
http://www.oracle.com/technetwork/issue-archive/2008/08-mar/o28plsql-095155.html

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s