Bulk Collect
De Dos Ideas.
BULK COLLECT es una función de Oracle que facilita la selección masiva de información de la base a alta velocidad, permitiendo el fetch de múltiples filas en una o mas colecciones.
Adicionalmente, el uso del FORALL facilita la ejecución masiva de inserts, updates y deletes, permitiendo transferir la información de una o mas colecciones a la base. Ambas alternativas pueden ser utilizadas de manera conjunta o individual, y la principal implicancia es el incremento en la performance de las operaciones.
Contenido
Ejemplo
create table t1(cod_cliente number(10),
des_cliente varchar2(10),
fec_proceso date);
create table t2(num_abonado number(10),
des_abonado varchar2(10),
cod_cliente number(10));
create table t3(cod_cliente number(10),
des_cliente varchar2(10),
cnt_abonado number(10));
insert into t1 select rownum,'C' || lpad(rownum,9,0),null from dual connect by level <= 5000;
insert into t2 select rownum,'A' || lpad(rownum,9,0),mod(rownum-1,5000)+1 from dual connect by level <= 20000;
commit;
Caso 1: sin manejo de excepciones
declare
type tty_rowid is table of urowid index by binary_integer; type tty_cod_cliente is table of t3.cod_cliente%type index by binary_integer; type tty_des_cliente is table of t3.des_cliente%type index by binary_integer; type tty_cnt_abonado is table of t3.cnt_abonado%type index by binary_integer;
tab_rowid tty_rowid; tab_cod_cliente tty_cod_cliente; tab_des_cliente tty_des_cliente; tab_cnt_abonado tty_cnt_abonado;
i number := 0;
cursor cur_test is select t1.rowid,t1.cod_cliente,t1.des_cliente,count(*)
from t1,t2
where t1.cod_cliente = t2.cod_cliente
group by t1.rowid,t1.cod_cliente,t1.des_cliente;
begin
open cur_test;
loop
fetch cur_test bulk collect into tab_rowid,
tab_cod_cliente,
tab_des_cliente,
tab_cnt_abonado limit 100;
exit when tab_rowid.count=0;
forall i in 1..tab_rowid.count
insert into t3 values(tab_cod_cliente(i),
tab_des_cliente(i),
tab_cnt_abonado(i));
forall i in 1..tab_rowid.count
update t1 set fec_proceso = sysdate where rowid = tab_rowid(i);
commit;
end loop;
close cur_test;
exception
when others then
rollback;
end;
Caso 2: con manejo de excepciones global
declare
type tty_rowid is table of urowid index by binary_integer; type tty_cod_cliente is table of t1.cod_cliente%type index by binary_integer; type tty_des_cliente is table of t1.des_cliente%type index by binary_integer; type tty_cnt_abonado is table of t3.cnt_abonado%type index by binary_integer;
tab_rowid tty_rowid; tab_cod_cliente tty_cod_cliente; tab_des_cliente tty_des_cliente; tab_cnt_abonado tty_cnt_abonado;
v_rows_ok number := 0; v_rows_er number := 0; i number := 0;
cursor cur_test is select t1.rowid,t1.cod_cliente,t1.des_cliente,count(*)
from t1,t2
where t1.cod_cliente = t2.cod_cliente
group by t1.rowid,t1.cod_cliente,t1.des_cliente
order by t1.cod_cliente;
excp_bulk_error exception; pragma exception_init (excp_bulk_error, -24381 );
begin
open cur_test;
loop
fetch cur_test bulk collect into tab_rowid,
tab_cod_cliente,
tab_des_cliente,
tab_cnt_abonado limit 100;
exit when tab_rowid.count=0;
forall i in 1..tab_rowid.count save exceptions
insert into t3 values(tab_cod_cliente(i),
tab_des_cliente(i),
tab_cnt_abonado(i));
forall i in 1..tab_rowid.count
update t1 set fec_proceso = sysdate where rowid = tab_rowid(i);
commit;
end loop;
close cur_test;
exception
when excp_bulk_error then
p('# Handler global ');
v_rows_ok := sql%rowcount;
v_rows_er := sql%bulk_exceptions.count;
p('# Rows[OK] ',v_rows_ok);
p('# Rows[ER] ',v_rows_er);
for i in 1..v_rows_er loop
p('# Row ',sql%bulk_exceptions(i).error_index);
p('# Code ',sql%bulk_exceptions(i).error_code);
p('# Descripcion ',sqlerrm(-sql%bulk_exceptions(i).error_code));
end loop;
rollback;
when others then
rollback;
end;
Caso 3: con manejo de excepciones local
declare
type tty_rowid is table of urowid index by binary_integer; type tty_cod_cliente is table of t1.cod_cliente%type index by binary_integer; type tty_des_cliente is table of t1.des_cliente%type index by binary_integer; type tty_cnt_abonado is table of t3.cnt_abonado%type index by binary_integer;
tab_rowid tty_rowid; tab_cod_cliente tty_cod_cliente; tab_des_cliente tty_des_cliente; tab_cnt_abonado tty_cnt_abonado;
v_rows_ok number := 0; v_rows_er number := 0; i number := 0;
cursor cur_test is select t1.rowid,t1.cod_cliente,t1.des_cliente,count(*)
from t1,t2
where t1.cod_cliente = t2.cod_cliente
group by t1.rowid,t1.cod_cliente,t1.des_cliente
order by t1.cod_cliente;
excp_bulk_error exception; pragma exception_init (excp_bulk_error, -24381 );
begin
open cur_test;
loop
fetch cur_test bulk collect into tab_rowid,
tab_cod_cliente,
tab_des_cliente,
tab_cnt_abonado limit 100;
exit when tab_rowid.count=0;
begin
forall i in 1..tab_rowid.count save exceptions
insert into t3 values(tab_cod_cliente(i),
tab_des_cliente(i),
tab_cnt_abonado(i));
forall i in 1..tab_rowid.count
update t1 set fec_proceso = sysdate where rowid = tab_rowid(i);
exception
when excp_bulk_error then
p('# Handler local ');
v_rows_ok := sql%rowcount;
v_rows_er := sql%bulk_exceptions.count;
p('# Rows[OK] ',v_rows_ok);
p('# Rows[ER] ',v_rows_er);
for i in 1..v_rows_er loop
p('# Row ',sql%bulk_exceptions(i).error_index);
p('# Code ',sql%bulk_exceptions(i).error_code);
p('# Descripcion ',sqlerrm(-sql%bulk_exceptions(i).error_code));
end loop;
rollback;
end;
commit;
end loop;
close cur_test;
exception
when others then
rollback;
end;
Estos casos ejemplos y otros relacionados pueden verse en el package th_blkav_pkg
Sintaxis
SELECT C1,C2,….,CN BULK COLLECT INTO COL1,COL2,….COLN. SELECT C1,C2,….,CN BULK COLLECT INTO COLT. FETCH C BULK COLLECT INTO COL1,COL2,….COLN LIMIT MAXROWS FETCH C BULK COLLECT INTO COLT LIMIT MAXROWS.
Sugerencias de Uso
Utlizar un LIMIT adecuado para cada fetch, utilizar uno extremadamente grande puede generar problemas de memoria sobre el server.
Casos prácticos de uso
Procesamiento masivo de datos.
