create table test as
Select * From all_objects
Where rownum <=10000
/
create table test_insert as
Select * From all_objects
Where 1 = 2
/
create table test_l as
Select * From all_objects
Where 1 = 2
/
create table test_bk as
Select * From all_objects
Where 1 = 2
/
truncate table test_insert
/
truncate table test_l
/
truncate table test_bk
/
variable n number
set serveroutput on
alter session set sql_trace = true
/
begin :n := dbms_utility.get_time; end;
/
insert into test_insert
select * from test
/
begin dbms_output.put_line('insert = '||to_char(dbms_utility.get_time - :n)); end;
/
begin :n := dbms_utility.get_time; end;
/
begin
  For crs In (Select t.* 
                From test t
              )
  Loop
    Insert Into test_l 
           Values (crs.owner, crs.object_name, crs.subobject_name, crs.object_id, 
                   crs.data_object_id, crs.object_type, crs.created, crs.last_ddl_time, 
                   crs.timestamp, crs.status, crs.temporary, crs.generated, 
                   crs.secondary);
  End Loop;            
  commit;
end;
/
begin dbms_output.put_line('insert row by row = '||to_char(dbms_utility.get_time - :n)); end;
/
begin :n := dbms_utility.get_time; end;
/
declare
  TYPE test_tp Is Table Of test%RowType INDEX BY PLS_INTEGER;
  l_test test_tp;
  --
  cursor crs Is 
    Select t1.* 
      From test t1;
begin
  Open crs;
  --
  Loop
    Fetch crs Bulk Collect Into l_test Limit 1000;
  --
    ForAll i in 1..l_test.count
      Insert Into test_bk 
             values l_test(i);
  --
    Exit When crs%NOTFOUND;           
  End Loop;
  commit;  
end;
/
begin dbms_output.put_line('insert bulk = '||to_char(dbms_utility.get_time - :n)); end;
/
alter session set sql_trace = false
/
