Archive for category: SQL

Uso de cursores y bulk 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…