bulk collect update limit

[klarcParticipante]

Senhores Segue resolução.

*Mostra também os intervalos entre os commits.
*Nesse exemplo o commit é efetuado a cada 500 mil linhas atualizadas(limit 500000;).
*Como a tabela é grande fiz teste apenas para 10 milhões de linhas (rownum < 10000000;)


set timing on
set autotrace on
set serveroutput on
declare
cursor C1 is select rowid as IDLINHA from .

where flg_ori is null and rownum < 10000000;
TYPE NumList IS TABLE OF rowid;
v_arr NumList ;
--type v_typ is table of C1%rowtype;
--v_arr v_typ;
errors NUMBER;
dml_errors EXCEPTION;
v_datahora varchar2(300);
--PRAGMA exception_init(dml_errors, -24381);
begin
dbms_output.enable(2000000);
v_datahora:=to_char(sysdate,'dd/mm/yy hh24:mi:ss');
dbms_output.put_line('Inicio : '||v_datahora);
open C1;
loop
fetch C1 bulk collect into v_arr limit 500000;
forall i in v_arr.first .. v_arr.last
update . set flg_ori=' ' where rowid = v_arr(i);
commit;
v_datahora:=to_char(sysdate,'dd/mm/yy hh24:mi:ss');
dbms_output.put_line('500K : '||v_datahora);
exit when C1%notfound;
end loop;
close C1;
v_datahora:=to_char(sysdate,'dd/mm/yy hh24:mi:ss');
dbms_output.put_line('Fim : '||v_datahora);
EXCEPTION
WHEN dml_errors THEN
errors := SQL%BULK_EXCEPTIONS.COUNT;
dbms_output.put_line('Number of errors is ' || errors);
FOR i IN 1..errors LOOP
dbms_output.put_line('Error ' || i || ' occurred during '||
'iteration ' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);
dbms_output.put_line('Oracle error is ' ||
SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
END LOOP;
end;
/


exemplo da saida:

Execution Plan
———————————————————-
0 UPDATE STATEMENT Optimizer Mode=CHOOSE
1 0 UPDATE TESTE.TABELA_BK
2 1 TABLE ACCESS FULL TESTE.TABELA_BK

Statistics
———————————————————-
392 recursive calls
1102779 db block gets
2797 consistent gets
2072 physical reads
241807224 redo size
0 Parallel operations downgraded 50 to 75 pct
0 Parallel operations downgraded 25 to 50 pct
0 Parallel operations downgraded 1 to 25 pct
0 PX remote messages recv’d
0 buffer is pinned count
999999 rows processed

Inicio : 18/03/10 18:07:19
500K : 18/03/10 18:08:19
500K : 18/03/10 18:09:09
500K : 18/03/10 18:09:40
500K : 18/03/10 18:10:45
500K : 18/03/10 18:11:34
500K : 18/03/10 18:12:15
500K : 18/03/10 18:13:04
500K : 18/03/10 18:13:35
500K : 18/03/10 18:14:07
500K : 18/03/10 18:14:49
500K : 18/03/10 18:15:20
500K : 18/03/10 18:16:01
500K : 18/03/10 18:16:39
500K : 18/03/10 18:17:32
500K : 18/03/10 18:18:07
500K : 18/03/10 18:18:39
500K : 18/03/10 18:19:14
500K : 18/03/10 18:19:45
500K : 18/03/10 18:20:18
500K : 18/03/10 18:20:45
Fim : 18/03/10 18:20:45
PL/SQL procedure successfully completed.
Elapsed: 00:13:30.80 Para 10 Milhoes de rows

Assim é muito mais rápido. Testem e vejam o quanto eficiente é usar BULK Collect.

Com isso evita-se alto uso de UNDO, pode-se, nesse caso, fazer aos poucos – como no exemplo, fiz para 10 milhões de linhas – para gerenciar a quantidade de archives gerados.

Agradeço a todos que participaram e que direto ou indiretamente ajudaram.