PL/SQL Normal Insert and Bulk Insert

PL/SQL NORMAL INSERT AND BULK INSERT

create table forall_test (
  id           number(10),
  code         varchar2(10),
  description  varchar2(50));

alter table forall_test add (
  constraint forall_test_pk primary key (id));

alter table forall_test add (
  constraint forall_test_uk unique (code));

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;

/