Checking The performance & Advantage of using BULK COLLECT

SET SERVEROUTPUT ON
DECLARE
TYPE t_bulk_collect_test_tab IS TABLE OF bulk_collect_test%ROWTYPE;

l_tab t_bulk_collect_test_tab;

CURSOR c_data IS
SELECT *
FROM bulk_collect_test;

l_start NUMBER;
BEGIN
— Time a regular cursor for loop.
l_start := DBMS_UTILITY.get_time;

FOR cur_rec IN (SELECT *
FROM bulk_collect_test)
LOOP
NULL;
END LOOP;

DBMS_OUTPUT.put_line(‘Regular : ‘ ||
(DBMS_UTILITY.get_time – l_start));

— Time bulk with LIMIT 10.
l_start := DBMS_UTILITY.get_time;

OPEN c_data;
LOOP
FETCH c_data
BULK COLLECT INTO l_tab LIMIT 10;
EXIT WHEN l_tab.count = 0;
END LOOP;
CLOSE c_data;

DBMS_OUTPUT.put_line(‘LIMIT 10 : ‘ ||
(DBMS_UTILITY.get_time – l_start));

— Time bulk with LIMIT 100.
l_start := DBMS_UTILITY.get_time;

OPEN c_data;
LOOP
FETCH c_data
BULK COLLECT INTO l_tab LIMIT 100;
EXIT WHEN l_tab.count = 0;
END LOOP;
CLOSE c_data;

DBMS_OUTPUT.put_line(‘LIMIT 100: ‘ ||
(DBMS_UTILITY.get_time – l_start));

— Time bulk with LIMIT 1000.
l_start := DBMS_UTILITY.get_time;

OPEN c_data;
LOOP
FETCH c_data
BULK COLLECT INTO l_tab LIMIT 1000;
EXIT WHEN l_tab.count = 0;
END LOOP;
CLOSE c_data;

DBMS_OUTPUT.put_line(‘LIMIT 1000: ‘ ||
(DBMS_UTILITY.get_time – l_start));
END;
/
Regular : 18
LIMIT 10 : 80
LIMIT 100: 15
LIMIT 1000: 10

PL/SQL procedure successfully completed

 

The following test compares the time taken to insert 10,000 rows using regular FOR..LOOP and a bulk bind.

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 := 10000;
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;
/
Normal Inserts: 305
Bulk Inserts : 14

PL/SQL procedure successfully completed.