SQL & PL/SQL

Hi All,

This is purely for a self study excercise about using INSERT ALL inside BulK Collect FORALL statement.

Before executing the block, I was in an assumption that INSERT ALL inside FORALL will run faster when compared to using 2 seperate FORALL for each insert statement.

But after the block execution is shows INSERT ALL inside FORALL is executing 10 times slower than the other.

Can you please clarify why INSERT ALL is taking more time.

Please find the below code:

Table creation scripts:

create table TEST_FORALL_INSERT_ALL as

select level as empno, dbms_random.string('L',10) as empname, round(dbms_random.value(20000,25000)) as empsal,

round(dbms_random.value(1,100)) as empdept

from dual

connect by level <= 1000000;

create table TEST_FORALL_INSERT_ALL_1 as select * from TEST_FORALL_INSERT_ALL where 1<>1;

create table TEST_FORALL_INSERT_ALL_2 as select * from TEST_FORALL_INSERT_ALL where 1<>1;

set serveroutput on;

declare

  type ass_aryy_dec is table of TEST_FORALL_INSERT_ALL%rowtype index by pls_integer;

  ass_aryy_assg ass_aryy_dec;

  n_num1 number;

  n_num2 number;

begin

  execute immediate ('Truncate table TEST_FORALL_INSERT_ALL_1');

  execute immediate ('Truncate table TEST_FORALL_INSERT_ALL_2');

  select * bulk collect into ass_aryy_assg from TEST_FORALL_INSERT_ALL;

  n_num1 := dbms_utility.get_time();

  forall x in 1..ass_aryy_assg.count

    insert into TEST_FORALL_INSERT_ALL_1 values ass_aryy_assg(x);  

  forall x in 1..ass_aryy_assg.count

    insert into TEST_FORALL_INSERT_ALL_2 values ass_aryy_assg(x);  

  n_num2 := dbms_utility.get_time();

  dbms_output.put_line('Time taken for FORALL and normal INSERT ~ '||(n_num2-n_num1));

  execute immediate ('Truncate table TEST_FORALL_INSERT_ALL_1');

  execute immediate ('Truncate table TEST_FORALL_INSERT_ALL_2');

  n_num1 := dbms_utility.get_time();

  forall x in 1..ass_aryy_assg.count

    insert all

      into TEST_FORALL_INSERT_ALL_1 values (ass_aryy_assg(x).empno, ass_aryy_assg(x).empname ,ass_aryy_assg(x).empsal ,ass_aryy_assg(x).empdept)

      into TEST_FORALL_INSERT_ALL_2 values (ass_aryy_assg(x).empno, ass_aryy_assg(x).empname ,ass_aryy_assg(x).empsal ,ass_aryy_assg(x).empdept)

    select 1 from dual;

  n_num2 := dbms_utility.get_time();

  dbms_output.put_line('Time taken for FORALL and INSERT ALL ~ '||(n_num2-n_num1));

end;

Output:

Time taken for FORALL and normal INSERT ~ 262

Time taken for FORALL and INSERT ALL ~ 2966

Oracle Version: 

Thanks & Regards

Jayam