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 insert select: 1’42” (mismos bloques físicos y en caché)
Tiempo bulk insert: 1’33” (mismos bloques físicos y en caché)
No hay diferencia…
Intente hacer lo indicado, pero cuando trato de insertar filas duplicadas, se gatilla DUP_VAL_ON_INDEX y no BULK_ERROR, luego aborta la ejecución de la instrucción completa. ¿A que se debe esto?, ¿es posible forzar que ignore DUP_VAL_ON_INDEX y seguir procesando?
De antemano gracias,
Kr!
Hola, soy nuevo lector del Blog y de primera impresión me parece muy interesante, tanto que vista la gran ayuda que estas aportando me voy a atrever a hacer una consulta que tiene que ver con las insert, y es que tengo una sentencia que ejecutandola desde el TOAD (je,je..) funciona de maravilla, pero en cuanto la lanzo desde un programa en VB6.0 el tiempo de respuesta es tal que se sale por timeout, ¿ alguna sugerencia ?
Un saludo y gracias de antemano
Saludos, mas que nada me gustaria realizar un comentario hacerca de esto, pues bueno hace tiempo tube un problema paracido pues en mi batch emigraba gran cantidad de datos de una db hacia la mia y pues hacia algo con cursores pero realizando 1 a 1 las transacciones y pues el tiempo era exageradamente grande, al utilizar estos bulk insert mejoro demaciado el tiempo de ejecusion y por ahi me entere de que lo que hace oracle es por cada registro o transaccion realizada crea un log en tablas propias de oracle (para el caso del rollback) y pues al realizar transaccion de 1 por 1 esto hace demaciado lento el proceso, lo que hace el bulk insert es mover cantidades de datos almacenando solo en alguna clase de mem virtual o algo asi por lo que no crea mas que solo un log haciendo mas ajil el movimiento masivo de datos, bueno soy nuevo en oracle pero lo poco que se fue eso, solo queria compartirlo y pues si estoy en lo correcto o no hagan lo saber o publiquen su comentario 😀 grac. a y con lo del comentario de krimarck pues tu mensaje es normal porque esta tratando de insertar en tu tabla un registro duplicado, verifica tus indices y termina el proceso porque no puede hacer el proceso y claro que no te insertara nada porque el commit lo realiza al hacer toda la transaccion (intento hacer el insert y no hubo errores) y pues si quieres puedes definir tus propias exceptions (RAISE) aunque el error es por lo que te mencione.
hola, tengo el mismo problema que alex, yo utilizo PL/ sql developer pero da igual. El caso es que el bach se lanza desde VB.6.0 pero se me queda colgado despues de conectar con la BBDD.
mientras que ejecutandolo directamente no da problemas.
Estare pendiente de alguna idea.
Un saludo.