plsql forall vs. for

1.

insert into test_objects
select object_name, object_type from dba_objects;

result : 91271 rows inserted.

2

/* Formatted on 1/7/2018 12:15:41 AM (QP5 v5.287) */
DECLARE
TYPE myType IS TABLE OF test_objects%ROWTYPE;

myObjects myType := myType ();
start_time NUMBER;
end_time NUMBER;
BEGIN

SELECT *
BULK COLLECT INTO myObjects
FROM test_objects;

-- Bulk Insert

EXECUTE IMMEDIATE 'TRUNCATE TABLE test_objects';

Start_time := DBMS_UTILITY.get_time;

FORALL i IN myObjects.FIRST .. myObjects.LAST
INSERT INTO test_objects
VALUES myObjects (i);

end_time := DBMS_UTILITY.get_time;
DBMS_OUTPUT.PUT_LINE (
'Bulk Insert: ' || TO_CHAR ( (end_time - Start_time) / 100));

-- without bulk insert

EXECUTE IMMEDIATE 'TRUNCATE TABLE test_objects';

Start_time := DBMS_UTILITY.get_time;

FOR i IN myObjects.FIRST .. myObjects.LAST
LOOP
INSERT INTO test_objects (object_name, object_type)
VALUES (myObjects (i).object_name, myObjects (i).object_type);
END LOOP;

end_time := DBMS_UTILITY.get_time;
DBMS_OUTPUT.PUT_LINE (
'Conventional Insert: ' || TO_CHAR ( (end_time - start_time) / 100));
COMMIT;
END;

 

result :

Bulk Insert: 0.06
Conventional Insert: 1.45