www.eygle.com

SQL> drop table blktest;

Table dropped.

Elapsed: 00:00:00.13
SQL> 
SQL> CREATE TABLE blktest (num NUMBER(20), name varchar2(50));

Table created.

Elapsed: 00:00:00.08
SQL> 
SQL> CREATE OR REPLACE PROCEDURE bulktest
  2  IS
  3     TYPE numtab IS TABLE OF NUMBER (20)
  4        INDEX BY BINARY_INTEGER;
  5  
  6     TYPE nametab IS TABLE OF VARCHAR2 (50)
  7        INDEX BY BINARY_INTEGER;
  8  
  9     pnums    numtab;
 10     pnames   nametab;
 11     t1       NUMBER;
 12     t2       NUMBER;
 13     t3       NUMBER;
 14  BEGIN
 15     FOR j IN 1 .. 1000000
 16     LOOP
 17        pnums (j)         := j;
 18        pnames (j)        := 'Seq No. ' || TO_CHAR (j);
 19     END LOOP;
 20  
 21     SELECT DBMS_UTILITY.get_time
 22       INTO t1
 23       FROM DUAL;
 24  
 25     FOR i IN 1 .. 1000000
 26     LOOP
 27        INSERT INTO blktest
 28             VALUES (pnums (i), pnames (i));
 29     END LOOP;
 30  
 31     SELECT DBMS_UTILITY.get_time
 32       INTO t2
 33       FROM DUAL;
 34  
 35     FORALL i IN 1 .. 1000000
 36        INSERT INTO blktest
 37             VALUES (pnums (i), pnames (i));
 38  
 39     SELECT DBMS_UTILITY.get_time
 40       INTO t3
 41       FROM DUAL;
 42  
 43     DBMS_OUTPUT.put_line ('Execution Time (hsecs)');
 44     DBMS_OUTPUT.put_line ('---------------------');
 45     DBMS_OUTPUT.put_line ('FOR loop: ' || TO_CHAR (t2 - t1));
 46     DBMS_OUTPUT.put_line ('FORALL:   ' || TO_CHAR (t3 - t2));
 47  END;
 48  /

Procedure created.

Elapsed: 00:00:01.46
SQL> exec bulktest;
Execution Time (hsecs)
---------------------
FOR loop: 30361
FORALL:   4792

PL/SQL procedure successfully completed.

Elapsed: 00:06:32.92