Blog do Marcio Portes

insert into gtt select initcap(object_name) from t;

ops$marcio:LX10G> create table t as select * from all_objects;

Table created.

ops$marcio:LX10G> create global temporary table gtt on commit preserve rows
  2  as select * from t where 1=0;

Table created.

ops$marcio:LX10G>
ops$marcio:LX10G> create or replace procedure p
  2  is
  3          cursor c is select * from t;
  4          type rec is table of c%rowtype index by binary_integer;
  5          l_rec rec;
  6          l_time  number default dbms_utility.get_time;
  7          procedure show_time (p_message in varchar2) is
  8          begin
  9                  dbms_output.put_line(p_message ||
 10                     trunc((dbms_utility.get_time - l_time)/100,2) || ' segundos!');
 11                  l_time := dbms_utility.get_time;
 12          end;
 13  begin
 14          execute immediate ('truncate table gtt');
 15          for x in c
 16          loop
 17                  x.object_name := initcap(x.object_name);
 18                  insert into gtt values x;
 19          end loop;
 20          show_time('Tempo cursor normal ');
 21          execute immediate ('truncate table gtt');
 22          open c;
 23          loop
 24                  fetch c bulk collect into l_rec limit 100;
 25
 26                  for i in 1 .. l_rec.count
 27                  loop
 28                          l_rec(i).object_name := initcap(l_rec(i).object_name);
 29                  end loop;
 30
 31                  forall i in 1 .. l_rec.count
 32                          insert into gtt values l_rec(i);
 33
 34                  exit when c%notfound;
 35          end loop;
 36          close c;
 37          show_time('Tempo bulk collect ');
 38  end;
 39  /

Procedure created.

ops$marcio:LX10G> show error;
No errors.
ops$marcio:LX10G>
ops$marcio:LX10G> exec p;
Tempo cursor normal 3,05 segundos!
Tempo bulk collect ,45 segundos!

PL/SQL procedure successfully completed.

ops$marcio:LX10G>

Labels: Performance

# posted by Marcio : 5:04 PM