Using for loop,FORALL loop

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.

CREATE TABLE ukaccount.forall_test(
  id           NUMBER(10),
  name         VARCHAR2(100)
  );

ALTER TABLE ukaccount.forall_test ADD(
  CONSTRAINT forall_test_pk PRIMARY KEY (id));

ALTER TABLE ukaccount.forall_test ADD(
  CONSTRAINT forall_test_uk UNIQUE (name));

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

DECLARE
  TYPE t_forall_test IS TABLE OF UKACCOUNT.forall_test%ROWTYPE;
  l_tab    t_forall_test := t_forall_test();
  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).name        := 'value-' || TO_CHAR(i);
  END LOOP;

  EXECUTE IMMEDIATE 'TRUNCATE TABLE UKACCOUNT.forall_test';

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

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

  DBMS_OUTPUT.put_line('Normal Inserts: ' || (DBMS_UTILITY.get_time - l_start)); 
  EXECUTE IMMEDIATE 'TRUNCATE TABLE UKACCOUNT.forall_test';
  
  
  
  -- Time bulk inserts.  
   l_start := DBMS_UTILITY.get_time;

  FORALL i IN l_tab.first .. l_tab.last
    INSERT INTO UKACCOUNT.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