Bulk: Collect e Insert
Muchas veces, nos topamos con situaciones en las cuales, tenemos prácticamente qué vaciar el contenido de
una tabla a otra. Sin embargo, esto de acuerdo al tamaño de registro y principalmente a la forma en cómo se
haga, puede repercutir en una mayor o menor cantidad de tiempo. Aquí comento una forma bastante útil para
poder realizar este tipo de tareas de manera bastante eficiente y rápida: El bulk collect y el bulk insert. Para
hacer este post, me inspiré en una pregunta que alguien hizo en el sitio de Tom Kyte.
Muy bien, para comenzar vamos a plantear un escenario en el que tengo dos tablas, una se llama object con
poco más de un millón de registros, y la otra se llama object2 y está vacía. Nuestro objetivo, es transferir los
registros de la primera a la segunda de la manera más rápida:
SQL> select count(*) cant
2 from object;
CANT
-----------
1,019,280
SQL> select count(*) cant
2 from object2;
CANT
-----------
Para realizar esta transferencia, iremos desde lo más simple, hasta llegar a nuestra mejor forma de hacerlo,
mostrando el tiempo que se lleva cada caso.
Caso 1: Inserción y commit por registro
Primero, vamos a realizar una transferencia registro a registro con algo no eficiente; un commit después de
cada inserción:
SQL> begin
for c in (select * from object) loop
insert into object2 values c;
commit;
end loop;
exception
when others then
dbms_output.put_line (sqlerrm);
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:03:24.62
Como se puede observar, le toma casi 3 minutos y medio a Oracle el transferir los registros de la tabla object a
laobject2. En este caso, le toma mucho tiempo por hacer commit por cada transacción que se está haciendo.
Esto es muy seguro porque no se pierden las transacciones, pero es muy lento. Pasemos a nuestro siguiente
caso:
Caso 2: Inserción por registro y commit al final
Ahora, sacamos el commit del ciclo de inserción para tratar de que sea más rápido y por tanto, más eficiente:
SQL> begin
for c in (select * from object) loop
insert into object2 values c;
end loop;
commit;
exception
when others then
dbms_output.put_line (sqlerrm);
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:01:09.57
Como se puede ver, si hay una mejora sustancial por poner el commit fuera del ciclo donde se insertan los
registros. Uno podría pensar que esta es la mejor opción; sin embargo, veamos el siguiente caso:
Caso 3: Bulk collect y bulk insert
Ahora, veremos un ejemplo de cómo nos ayuda el hacer un bulk collect para después hacer un bulk insert. La
palabra bulk significa montón y collect recolectar, así que bulk collect es como una recolección masiva o por
montón. Bulk insert por el contrario es aplicado a una inserción masiva de registros.
Para realizar el proceso de bulk collect, requerimos varias cosas:
Arreglo para información
Este se define con una instrucción como la que sigue:
type tipo_arreglo is table of nombre_tabla%rowtype index by binary_integer;
Donde:
nombre_tabla%type es la definición de un registro con una serie de campos y tipos de datos iguales al de la
tabla nombre_tabla. Entonces, suponiendo que nombre_tabla es:
El registro queda como:
is table of es para definir que será propiamente una estructura de diversos registros donde cada registro, será
como el descrito en el punto previo creando propiamente una tabla:
index by binary_integer significa que este arreglo estará indexado o referido por enteros, creando así una
especie de arreglo basado en un tipo de registro de la tabla mencionada:
Cursor con información
Lo siguiente que requerimos, es un cursor que nos traiga la información que queremos transferir de una tabla a
la otra:
cursor c is select * from tabla_origen;
Con este podremos leer la información de la tabla tabla_origen para tratarla de depositar en la tabla destino.
Variable de tipo arreglo
Lo único que nos resta en las definiciones, es el tener una variable del arreglo definido previamente, ésta será
nuestro receptáculo para la información leida por bloque:
var_arreglo tipo_arreglo;
Bulk collect
Muy bien, ahora vemos cómo es el proceso del bulk collect. Básicamente se abre el cursor de manera normal
con su ciclo y su exit when cursor%notfound. Sin embargo, hay una diferencia en el fetch:
fetch cursor bulk collect into var_arreglo limit num_registros;
Como podemos ver, después del fetch al cursor, se incluyen las palabras bulk collect indicando que será una
recolección en montón, y se asigna un límite de número de registros a traer.
Al ejecutar esta instrucción, automáticamente se leerán num_registros cantidad de registros y se depositarán
en memoria en nuestra variable var_arreglo para poder manipularlos. Si estamos ya al final en la última
extracción y hay menos registros que num_registros, se lee sin problemas el resto de registros.
Bulk insert
Una vez que ya se tienen registros en nuestra variable, hay que leer ese arreglo y depositar la información en
la tabla destino:
forall i in 1..var_arreglo.count save exceptions
insert into tabla_destino values var_arreglo (i);
Para eso, usamos esta opción del ciclo for, para ir desde el registro número 1 hasta el total que tenga el arreglo.
La instrucción save exceptions nos permite que si leímos menos registros que num_registros en nuestra última
pasada, de cualquier manera se procesen.
Ya con dicho ciclo, lo único que resta, es insertar en tabla_destino la información registro por registro.
El proceso
Muy bien, ahora veamos nuestro proceso ejecutándose, ¿cómo se refleja en el tiempo que tarda en ejecutarse?
SQL> declare
type array_object is table of object%rowtype
index by binary_integer;
cursor c is select * from object;
lr_datos array_object;
begin
open c;
loop
fetch c bulk collect into lr_datos limit 1000;
begin
forall i in 1..lr_datos.count save exceptions
insert into object2 values lr_datos (i);
exception
when others then
dbms_output.put_line (sqlerrm);
end;
exit when c%notfound;
end loop;
commit;
close c;
exception
when others then
dbms_output.put_line (sqlerrm);
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:20.10
Como se puede observar disminuyó significativamente desde nuestra primera ejecución de 3 minutos y 24
segundos. Ahora nada más le tomó 20 segundos. Pero, ¿qué pasa si aumentamos el límite de registros a traer
al doble de esta prueba que acabamos de realizar?:
SQL> declare
type array_object is table of object%rowtype
index by binary_integer;
cursor c is select * from object;
lr_datos array_object;
begin
open c;
loop
fetch c bulk collect into lr_datos limit 2000;
begin
forall i in 1..lr_datos.count save exceptions
insert into object2 values lr_datos (i);
exception
when others then
dbms_output.put_line (sqlerrm);
end;
exit when c%notfound;
end loop;
commit;
close c;
exception
when others then
dbms_output.put_line (sqlerrm);
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:19.34
Como se puede ver, ya no hubo gran ventaja, apenas un segundo menos para traer la misma cantidad de
registros. ¿Y si duplicamos nuevamente la cantidad de registros a traer de manera simultánea?:
SQL> declare
type array_object is table of object%rowtype
index by binary_integer;
cursor c is select * from object;
lr_datos array_object;
begin
open c;
loop
fetch c bulk collect into lr_datos limit 4000;
begin
forall i in 1..lr_datos.count save exceptions
insert into object2 values lr_datos (i);
exception
when others then
dbms_output.put_line (sqlerrm);
end;
exit when c%notfound;
end loop;
commit;
close c;
exception
when others then
dbms_output.put_line (sqlerrm);
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:18.17
Nuevamente, la ventaja no es tan marcada, apenas 1 segundo menos otra vez. ¿Por qué está pasando esto?
Básicamente, la lectura de una mayor cantidad de información la está haciendo a una excelente velocidad; pero
en estos momentos, ya la velocidad del proceso general, la marca el mini-proceso “más lento” es decir, el insert.
De esta manera, se cumple lo que comenta Eliyahu Goldratt en su obra La Meta, donde la velocidad de todo
un sistema la marca el proceso más lento, ya que éste se convierte en el cuello de botella por el cual, debe
pasar todo.
Conclusiones
Como se pudo observar, el trabajar con el Bulk Collect e Insert, puede mejorar sustancialmente la velocidad de
vaciado de información de una tabla a otra. Es una excelente opción sin duda.
Sin embargo, debemos tener cuidado con la cantidad de registros alojada en memoria a través de nuestro
arreglo, ya que una cantidad excesiva de uso de ésta, podrá afectar a otros procesos que se están ejecutando
en la base de datos.