BULK COLLECT & FORALL vs. CURSOR & FOR LOOP in oracle

SQL> create table sarlakg (owner varchar2(30), name varchar2(30), type varchar2(19));

Table created.

 Example CURSOR_FOR_OPEN_QUERY

SQL> set timing on;
SQL> CREATE OR REPLACE PROCEDURE CURSOR_FOR_OPEN_QUERY
  2   IS
  3   l_sOwner VARCHAR2(30);
  4   l_sName VARCHAR2(30);
  5   l_sType VARCHAR2(19);
  6   CURSOR cur IS SELECT owner, object_name name, object_type type FROM all_objects;
  7   BEGIN
  8   dbms_output.put_line('Before CURSOR OPEN: ' || systimestamp);
  9   OPEN cur;
 10   dbms_output.put_line('Before LOOP: ' || systimestamp);
 11   LOOP
 12   FETCH cur INTO l_sOwner, l_sName, l_sType;
 13   IF cur%NOTFOUND THEN
 14   EXIT;
 15   END IF;
 16   INSERT INTO sarlakg values (l_sOwner, l_sName, l_sType);
 17   END LOOP;
 18   CLOSE cur;
 19   dbms_output.put_line('After CURSOR CLOSE: ' || systimestamp);
 20   COMMIT;
 21   END;
 22  /

Procedure created.

Elapsed: 00:00:00.47
SQL> exec CURSOR_FOR_OPEN_QUERY();

PL/SQL procedure successfully completed.

Elapsed: 00:00:05.80
SQL> select count(*) from sarlakg;

  COUNT(*)
----------
     55731

Elapsed: 00:00:00.00
SQL>


SQL> truncate table sarlakg;

Table truncated.

Elapsed: 00:00:00.15

 Example FOR_QUERY

SQL> CREATE OR REPLACE PROCEDURE FOR_QUERY
  2   IS
  3   BEGIN
  4   dbms_output.put_line('Before CURSOR: ' || systimestamp);
  5   FOR cur IN (SELECT owner, object_name name, object_type type FROM all_objects) LOOP
  6   INSERT INTO sarlakg values (cur.owner, cur.name, cur.type);
  7   END LOOP;
  8   dbms_output.put_line('After CURSOR: ' || systimestamp);
  9   COMMIT;
 10   END;
 11  /

Procedure created.

Elapsed: 00:00:00.13
SQL> exec FOR_QUERY();

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.58
SQL> select count(*) from sarlakg;

  COUNT(*)
----------
     55732

Elapsed: 00:00:00.01
 /


 SQL> truncate table sarlakg;

Table truncated.

Elapsed: 00:00:00.15

 Example BULK_COLLECT_QUERY


 SQL> CREATE OR REPLACE PROCEDURE BULK_COLLECT_QUERY
  2   IS
  3   TYPE sOwner IS TABLE OF VARCHAR2(30);
  4   TYPE sName IS TABLE OF VARCHAR2(30);
  5   TYPE sType IS TABLE OF VARCHAR2(19);
  6   l_sOwner sOwner;
  7   l_sName sName;
  8   l_sType sType;
  9   BEGIN
 10   dbms_output.put_line('Before Bulk Collect: ' || systimestamp);
 11   SELECT owner, object_name, object_type
 12   BULK COLLECT INTO l_sOwner, l_sName, l_sType
 13   FROM all_objects;
 14   dbms_output.put_line('After Bulk Collect: ' || systimestamp);
 15   --
 16   FORALL indx IN l_sName.FIRST..l_sName.LAST
 17   INSERT INTO sarlakg values (l_sOwner(indx), l_sName(indx), l_sType(indx));
 18   --
 19   dbms_output.put_line('After FORALL: ' || systimestamp);
 20   COMMIT;
 21   END;
 22  /

Procedure created.

Elapsed: 00:00:00.04
SQL> exec BULK_COLLECT_QUERY();

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.68
SQL>


SQL> select count(*) from sarlakg;

  COUNT(*)
----------
     55732

Elapsed: 00:00:00.01