Bulk collect and forall

Delapan tahun freelance ternyata seperti halnya katak dalam tempurung, karena penyelesaian maslah biasanya bersifat shortterm yang penting bisa solve dalam sebuah kasus, tanpa lebih lanjut inspeksi kedalam teknik yang lebih baik lagi.

Salah satunya adalah terjadi ketika sebuah teknik kursor yang melibatkan proses DML, biasanya saya memakai teknik kuno (old fashion way), yaitu memakai cursor dengan row-by-row proses. Ketika di teliti oleh teman seperjuangan di AUS. Dia dapat menunjukkan cara terbaik. Contoh berikut adalah proses sebanyak 10.000 row

• Cursor Teknik lama : Elapsed: 00:00:12.703

set timing on
DECLARE
 CURSOR a_cur IS 
 SELECT *
    from forall_test;
 l_tab  a_cur%ROWTYPE;
begin
  execute immediate 'TRUNCATE TABLE forall_test2';
  OPEN a_cur;
  loop
    fetch a_cur  into l_tab;
    exit when a_cur%notfound;  
      INSERT INTO forall_test2 (id, code, description)
      values (l_tab.id, l_tab.code, l_tab.description);         
  end loop;  
  close a_cur;
  commit;
end;
/

• Cursor bulk collect : Elapsed: 00:00:11.281

set timing on
DECLARE
 CURSOR a_cur IS 
 SELECT *
    from forall_test;
 TYPE myarray IS TABLE OF a_cur%ROWTYPE index BY PLS_INTEGER;
 l_tab myarray;
begin
  execute immediate 'TRUNCATE TABLE forall_test2';
  OPEN a_cur;
  loop
    fetch a_cur  bulk collect into l_tab limit 1000;
    exit when l_tab.count = 0;
    for i in l_tab.first .. l_tab.last loop
      INSERT INTO forall_test2 (id, code, description)
      values (l_tab(i).id, l_tab(i).code, l_tab(i).description);
      
    end loop;    
    
  END LOOP;
  close a_cur;

    commit;
END;
/

• Cursor bulk collect dan forall : Elapsed: 00:00:01.969

set timing on
DECLARE
 CURSOR a_cur IS 
 SELECT *
    from forall_test;

 TYPE myarray IS TABLE OF a_cur%ROWTYPE index BY PLS_INTEGER;
 l_tab myarray;
begin
  execute immediate 'TRUNCATE TABLE forall_test2';
  OPEN a_cur;
  loop
    fetch a_cur  bulk collect into l_tab limit 1000;
    exit when l_tab.count = 0;
    forall i in l_tab.first .. l_tab.last 
      INSERT INTO forall_test2 (id, code, description)
      values (l_tab(i).id, l_tab(i).code, l_tab(i).description);   
    
  END LOOP;
  close a_cur;

    commit;
end;
/

• Tanpa Cursor bulk collect dan forall : Elapsed: 00:00:01.797

set timing on
DECLARE
 TYPE myarray IS TABLE OF forall_test%ROWTYPE index BY PLS_INTEGER;
 l_tab myarray;
begin
  execute immediate 'TRUNCATE TABLE forall_test2';
  select *
  bulk collect into l_tab 
  from forall_test;
   
    forall i in l_tab.first .. l_tab.last 
      INSERT INTO forall_test2 (id, code, description)
      values (l_tab(i).id, l_tab(i).code, l_tab(i).description);
    commit;
END;
/

Special Thanks to Chris Finney for point this out.
Source :
1. Bulk Binds (BULK COLLECT & FORALL) and Record Processing in Oracle.
2. PL/SQL Collections and Records.
3. Tuning PL/SQL Applications for Performance.