Blog do Marcio Portes

Recentemente, outra dúvida sobre qual método seria mais eficiente na deleção de linhas de uma tabela surgiu na usuarios_oracle. Em minha opinião, por mais apelativo que seja o estouro da área de UNDO, eu ainda continuo apostando em SQL Puro. Eu aumentaria a área de UNDO para suportar a operação e faria em uma sentença. Vamos ao exemplo:

- Tabela com 2 milhões de registros, quero deletar 100 mil.
- Faço os 3 métodos, cursor normal, bulk collect e sql puro.
- O Resultado está no final.

ops$marcio:LX10G> drop table t purge;

Table dropped.

ops$marcio:LX10G>
ops$marcio:LX10G> create table t as
 2  select level n,
 3         rpad('x', 50, 'x') vc
 4    from dual connect by level <= 2000000
 5  /

Table created.

ops$marcio:LX10G>
ops$marcio:LX10G> create index t_idx on t ( n );

Index created.

ops$marcio:LX10G>
ops$marcio:LX10G> begin
 2          dbms_stats.gather_table_stats ( user, 'T', cascade => true );
 3  end;
 4  /

PL/SQL procedure successfully completed.

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

Procedure created.

ops$marcio:LX10G> show error;
No errors.
ops$marcio:LX10G>
ops$marcio:LX10G> exec p
Tempo cursor normal 23,38 segundos!
Tempo Bulk Collect 19,96 segundos!
Tempo SQL Puro 4,77 segundos!

PL/SQL procedure successfully completed.

Labels: Performance