bulk insert
Uno de los muchos mitos en oracle es que el uso de cursores perjudica seriamente el rendimiento.
El impacto sobre el rendimiento es cierto en parte, ya que el servidor trata individualmente cada registro y, por tanto, esto debería implementarse únicamente cuando fuera estrictamente necesario (sql dinámico, iteraciones dentro del fetch, etc.) utilizando, siempre que sea posible, SQL estándar.
No obstante, hay situaciones en las que no es posible el uso de SQL convencional. Por ejemplo, cuando en una inserción es preciso controlar los errores e insertarlos en otra tabla. Ante este caso, los procedimientos ETL filtran previamente las filas, las mueven a otra tabla, y cuando las filas ya están totalmente “limpias” de errores, entonces realizan
INSERT INTO tabla SELECT * …
Oracle, a partir de la versión 9i, dispone de la funcionalidad de BULK sql, es decir, tratamiento de conjuntos de filas “a montón”. Realizar bulk inserts puede resultar muy práctico en el caso anterior, ya que las filas en el cursor no se ejecutan individualmente y no es preciso el filtrado previo de las filas.
Así pues, ante la necesidad de insertar en una tabla un volúmen de filas, omitir los errores e insertar las filas erróneas en una tabla, el siguiente código PL/SQL puede ser de gran utilidad, ya que su coste de ejecución resulta idéntico al del INSERT.
– Código PL/SQL para insertar en una tabla, manejando errores en filas, con BULK INSERT
————————————————————————————–
DECLARE
type clientes_array is table of BK_CLIENTE_BDM_03%rowtype index by binary_integer;
registros clientes_array;
errores NUMBER;
dml_errores EXCEPTION;
contador_errores number := 0;
PRAGMA exception_init(dml_errores, -24381);
cursor c is select * from BK_CLIENTE_BDM_03;
BEGIN
open c;
loop
fetch c BULK COLLECT INTO registros LIMIT 1000;
begin
FORALL i IN 1 .. registros.count SAVE EXCEPTIONS
insert into TRASPASO_BD_CLIENTE values registros(i);
EXCEPTION
WHEN dml_errores THEN
errores := SQL%BULK_EXCEPTIONS.COUNT;
contador_errores := contador_errores + errores;
FOR i IN 1..errores LOOP
dbms_output.put_line (’Se encontro el error ‘||;SQL%BULK_EXCEPTIONS(i).ERROR_INDEX ||
’:'||SQL%BULK_EXCEPTIONS(i).ERROR_CODE);
end loop;
end;
exit when c%notfound;
END LOOP;
close c;
dbms_output.put_line( contador_errores );
end;
Su coste de ejecución es idéntico al de ejecutar:
insert into TABLA_DESTINO select * from TABLA_ORIGEN;
Comparativa de costes de ejecución.
******************************
insert into TRASPASO_BD_CLIENTE select * from BK_CLIENTE_BDM_03
call count cpu elapsed disk query current rows
——- —— ——– ———- ———- ———- ———- ———-
Parse 1 0.00 0.00 0 1 0 0
Execute 1 4.91 102.61 15713 31424 88189 600000
Fetch 0 0.00 0.00 0 0 0 0
——- —— ——– ———- ———- ———- ———- ———-
total 2 4.91 102.62 15713 31425 88189 600000
********************************************************************************
DECLARE
type clientes_array is table of BK_CLIENTE_BDM_03%rowtype index by binary_integer;
registros clientes_array;
errores NUMBER;
dml_errores EXCEPTION;
contador_errores number := 0;
PRAGMA exception_init(dml_errores, -24381);
cursor c is select * from BK_CLIENTE_BDM_03;
BEGIN
open c;
loop
fetch c BULK COLLECT INTO registros LIMIT 1000;
begin
FORALL i IN 1 .. registros.count SAVE EXCEPTIONS
insert into TRASPASO_BD_CLIENTE values registros(i);
EXCEPTION
WHEN dml_errores THEN
errores := SQL%BULK_EXCEPTIONS.COUNT;
contador_errores := contador_errores + errores;
FOR i IN 1..errores LOOP
dbms_output.put_line
(’Se encontrs el error ‘
SQL%BULK_EXCEPTIONS(i).ERROR_INDEX
‘: ‘
SQL%BULK_EXCEPTIONS(i).ERROR_CODE );
end loop;
end;
exit when c%notfound;
END LOOP;
close c;
dbms_output.put_line( contador_errores
end;
call count cpu elapsed disk query current rows
——- —— ——– ———- ———- ———- ———- ———-
Parse 1 0.00 0.01 0 0 0 0
Execute 1 2.40 2.46 0 0 0 1
Fetch 0 0.00 0.00 0 0 0 0
——- —— ——– ———- ———- ———- ———- ———-
total 2 2.40 2.47 0 0 0 1
SELECT * FROM BK_CLIENTE_BDM_03
call count cpu elapsed disk query current rows
——- —— ——– ———- ———- ———- ———- ———-
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 601 20.90 65.99 15712 16373 0 600000
——- —— ——– ———- ———- ———- ———- ———-
total 603 20.90 65.99 15712 16373 0 600000
INSERT INTO TRASPASO_BD_CLIENTE
VALUES
(:B1 ,:B2 ,:B3 ,:B4 ,:B5 ,:B6 ,:B7 ,:B8 ,:B9 ,:B10 ,:B11 ,:B12 ,:B13 ,:B14 ,
:B15 ,:B16 ,:B17 ,:B18 ,:B19 ,:B20 ,:B21 ,:B22 ,:B23 ,:B24 ,:B25 ,:B26 ,
:B27 ,:B28 ,:B29 ,:B30 ,:B31 ,:B32 ,:B33 ,:B34 ,:B35 ,:B36 ,:B37 ,:B38 ,
:B39 ,:B40 ,:B41 ,:B42 ,:B43 ,:B44 ,:B45 ,:B46 ,:B47 )
call count cpu elapsed disk query current rows
——- —— ——– ———- ———- ———- ———- ———-
Parse 1 0.00 0.00 0 0 0 0
Execute 600 17.40 26.39 0 15835 87647 600000
Fetch 0 0.00 0.00 0 0 0 0
——- —— ——– ———- ———- ———- ———- ———-
total 601 17.40 26.39 0 15835 87647 600000
********************************************************************************
RESUMEN:
Insert Select –> Execute 1 4.91 102.61 15713 31424 88189 600000
BULK INSERT –> Execute 1 2.40 2.46 0 0 0 1
+ Fetch 601 20.90 65.99 15712 16373 0 600000
+ Execute 600 17.40 26.39 0 15835 87647 600000
Tiempo bulk insert: 1′33′’ (mismos bloques físicos y en caché)
Comentarios