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
-----------
          0

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
  2    for c in (select * from object) loop
  3
  4     insert into object2 values c;
  5     commit;
  6
  7    end loop;
  8  exception
  9    when others then
 10      dbms_output.put_line (sqlerrm);
 11  end;
 12  /
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 la object2. 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
  2    for c in (select * from object) loop
  3
  4     insert into object2 values c;
  5
  6    end loop;
  7    commit;
  8  exception
  9     when others then
 10       dbms_output.put_line (sqlerrm);
 11  end;
 12  /
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
  2    type array_object is table of object%rowtype
  3                        index by binary_integer;
  4
  5    cursor c is select * from object;
  6
  7    lr_datos array_object;
  8  begin
  9    open c;
 10    loop
 11      fetch c bulk collect into lr_datos limit 1000;
 12
 13     begin
 14       forall i in 1..lr_datos.count save exceptions
 15         insert into object2 values lr_datos (i);
 16     exception
 17       when others then
 18         dbms_output.put_line (sqlerrm);
 19     end;
 20
 21     exit when c%notfound;
 22    end loop;
 23    commit;
 24    close c;
 25  exception
 26    when others then
 27      dbms_output.put_line (sqlerrm);
 28  end;
 29  /
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
  2    type array_object is table of object%rowtype
  3                        index by binary_integer;
  4
  5    cursor c is select * from object;
  6
  7    lr_datos array_object;
  8  begin
  9    open c;
 10    loop
 11      fetch c bulk collect into lr_datos limit 2000;
 12
 13     begin
 14       forall i in 1..lr_datos.count save exceptions
 15         insert into object2 values lr_datos (i);
 16     exception
 17       when others then
 18         dbms_output.put_line (sqlerrm);
 19     end;
 20
 21     exit when c%notfound;
 22    end loop;
 23    commit;
 24    close c;
 25  exception
 26    when others then
 27      dbms_output.put_line (sqlerrm);
 28  end;
 29  /
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
  2    type array_object is table of object%rowtype
  3                        index by binary_integer;
  4
  5    cursor c is select * from object;
  6
  7    lr_datos array_object;
  8  begin
  9    open c;
 10    loop
 11      fetch c bulk collect into lr_datos limit 4000;
 12
 13     begin
 14       forall i in 1..lr_datos.count save exceptions
 15         insert into object2 values lr_datos (i);
 16     exception
 17       when others then
 18         dbms_output.put_line (sqlerrm);
 19     end;
 20
 21     exit when c%notfound;
 22    end loop;
 23    commit;
 24    close c;
 25  exception
 26    when others then
 27      dbms_output.put_line (sqlerrm);
 28  end;
 29  /
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.

Si la información de este post te ha sido de utilidad o quieres que agregue algo más, deja por favor un comentario, contestaré a la brevedad.

Anuncios

38 Responses to Bulk: Collect e Insert

  1. henry morales vasquez says:

    mi estimado amigo, me sacaste de una situación en mi trabajo que no tienes idea, debia transferir de 9 tablas a una sola, y cada tabla rondaba cerca de los 5 millones de registros, con tu ejemplo me quedo super eficiente y controlado. te agradezco tu excelente explicación.
    Henry Morales
    Ciudad de Guatemala.

  2. orlandoolguin says:

    Hola Henry,

    Qué bueno que te haya sido de utilidad, gracias por tus comentarios, espero me sigas visitando.

    Orlando.
    Ciudad de Querétaro (jaja)

  3. Luciano Valenzuela says:

    Orlando, muy útil tu información, me gustaría saber además si este método es útil para procesos ETL en que el cursor seleccionará solo algunas columnas, realizará filtros y transformaciones de los datos con respecto a la fuente o solamente sirve para casos en que el cursor es select(*) from tabla???

  4. orlandoolguin says:

    Hola Luciano,

    Si puedes hacer movimientos y trabajar con la información en memoria, en el arreglo donde se guarda la información.

    Sin embargo, el arreglo mismo será tu limitante, dado que por ejemplo, si quisieras hacer una sumatoria de todos los registros, sólo lo podrás hacer sobre los datos que residen en el arreglo en memoria.

    Orlando.

  5. Fede says:

    Muy buena informacion y muy util!!
    Yo pensaba que limit solo me traía solo ese número de regsitros, por ejemplo: limit 500 solo me traía 500 de el total de registros, pero con lo que escribes significa, que si yo tengo 1325 registros y pongo un limit de 500 me traerá los 1325 pero en 3 bloques de 500, estoy en lo correcto?

  6. orlandoolguin says:

    Hola Fede,

    Muchas gracias por tu comentario.

    En el último fetch, se intentará traer 500 registros, pero sólo serán 325.

    Orlando.

  7. Kily says:

    Me es muy util tu informacion. Sin embargo me sucede un problema… del entero a insertar quedan fuera 35000 registros .y no se insertan.. reviso log y nada ningun error si vuelvo a lanzar los 35000 restantes pasan de una…Porque se dara esta situacion?.. muchas gracias

  8. orlandoolguin says:

    Hola,

    La verdad no sé por qué te esté pasando esto ya que dices que no tienes errores reportados.

    ¿Tienes excepciones en tu código para ver si ocurre un error y se está cortando el programa?
    ¿Le pusiste la cláusula ‘save exceptions’?

    Orlando.

  9. Kily says:

    Muchas gracias Orlando por contestar.. Si tengo excepciones para el bulk collect pero no he colocado esa clausula… la colocare y te comento..Muchas gracias

  10. orlandoolguin says:

    Hola Kily,

    Es probable que sea eso si es que no tienes errores capturados en la Exception. Espero se solucione el problema.

    Orlando.

  11. carlos says:

    Hola Orlando, espero puedas ayudarme con esta tarea.

    A partir de este select:

    SELECT a.utctimemax hora_max_rtc, a.VALUE valor_max_rtc,
    b.utctime hora_ave, b.VALUE valor_ave, c.utctime max_hora_rtc,
    c.VALUE max_value_ave
    FROM rtc_estaciones a, noa_ave b, noa_ave c
    WHERE a.VALUE IN (SELECT MAX (VALUE)
    FROM rtc_estaciones)
    AND c.VALUE IN (SELECT MAX (VALUE)
    FROM noa_ave
    WHERE utctime BETWEEN SYSDATE – 2 AND SYSDATE – 1)
    AND a.utctimemax BETWEEN SYSDATE – 2 AND SYSDATE – 1
    AND b.utctime BETWEEN SYSDATE – 2 AND SYSDATE – 1
    AND c.utctime BETWEEN SYSDATE – 2 AND SYSDATE – 1
    AND a.utctimemax = b.utctime;

    Obtengo un rango de datos. Estos como ves estan obtenidos desde rtc_estaciones y noa_ave.

    En lugar de obtenerlo de noa_ave, necesito que se obtengan los datos desde un rango de tablas del tipo ‘NOA%’

    Este listado de tablas la obtengo con:

    SELECT *
    FROM USER_TABLES
    WHERE TABLE_NAME LIKE ‘NOA%’
    AND TABLE_NAME NOT LIKE ‘%MAX%’
    AND TABLE_NAME NOT LIKE ‘%MIN%’;

    una vez que tengo estos datos en cursores, necesito hacer un insert a una tercer tabla, noa_ave_max_pot.

    O se la insercion de datos quedaria asi:

    INSERT INTO noa_ave_max_pot d (d.Pointnumber,
    d.hora_max_rtc, d.valor_max_rtc,
    d.hora_noa, d.valor_noa,
    d.hora_max_noa, d.valor_max_noa )
    /*VALUES*/ (SELECT a.pointnumber pointnumber,
    a.utctimemax hora_max_rtc, a.value valor_max_rtc ,
    b.utctime hora_ave, b.VALUE valor_ave,
    c.utctime hora_max_ave, c.VALUE value_max_ave
    FROM rtc_estaciones a, noa_ave b, noa_ave c
    WHERE A.value IN (SELECT MAX (VALUE)
    FROM rtc_estaciones)
    AND C.VALUE IN (SELECT MAX (VALUE)
    FROM noa_ave
    WHERE utctime BETWEEN SYSDATE – 3 AND SYSDATE – 2)
    AND A.utctimemax BETWEEN SYSDATE – 3 AND SYSDATE – 2
    AND B.utctime BETWEEN SYSDATE – 3 AND SYSDATE – 2
    AND C.utctime BETWEEN SYSDATE – 3 AND SYSDATE – 2
    AND a.utctimemax = b.utctime);

    Mi consulta es:

    Puedo utilizar bulk_collect / bulk insert para armar este escenario?, lo ves necesario? he creado algunas lineas, pero tengo problemas al ejecutarlo

    Si ves alguna otra forma de realizarlo, por favor indicame que herramientas puedo usar.

    He intentado de varias formas pero aún no consigo hacerlo andar.

    Desde ya muchas gracias!

    carlos

  12. orlandoolguin says:

    Hola Carlos,

    El Bulk Collect e Insert sirve para insertar cantidades masivas de información en una tabla.

    Si es lo que requieres entonces sólo tendrás que poner el query que leerá dicha información en el cursor correspondiente y de ahí, insertarlo a la tabla que llenarás.

    De acuerdo a los queries que enviaste, tengo algunas dudas y/o puntos a considerar:

    – En el primer query estás haciendo un producto cartesiano porque el alias C no está ligado con otras de las tablas,

    – dices que se debe insertar información en la tabla noa_ave_max_pot, pero no dices qué información en especfíco,

    – comentas que la información será leída de distintas tablas, esto tal vez lo puedas armar con un query con union y disitntos accesos a las distintas tablas NOA%, a menos que estas cambien, entonces tendrás que armar un query dinámico para insertar la información de cada una las tablas donde le corresponde.

    Espero esto te sea de utilidad de inicio, si pones algo más, te respondo por la noche, ya que regreso a trabajar en este momento.

    Orlando.

  13. carlos says:

    Gracias por tu respuesta Orlando, realmente estoy necesitando de ayuda con este tema. Estoy empezando a programar con PL-SQL y no logro cerrar muchas dudas que tengo.
    te aclaro algunas dudas:

    ” En el primer query estás haciendo un producto cartesiano porque el alias C no está ligado con otras de las tablas ”

    C es un alias que corresponde a noa_ave, al igual que B. En este caso podría haberlo hecho diferente, pero puede cambiar.

    Te resumo lo que busco en el select:

    A. max value / utctimemax max de la tabla rtc_estaciones
    B. value / utctime de la tabla noa_ave(esto para la prueba, luego debo obtenerlo de un rango de tablas)
    C. max value / utctme max de la tabla noa_ave (idem anterior) que coincida con el momento utctimemax de rtc_estaciones

    Solo datos del ultimo dia.

    El select devuelve algo asi:

    POINTNUMBER, HORA_MAX_RTC, VALOR_MAX_RTC, HORA_AVE, VALOR_AVE, HORA_MAX_AVE,VALUE_MAX_AVE
    118000, 17/03/2013 0:35:00, 3000, 17/03/2013 0:35:00, 1,
    17/03/2013 0:30:00,200,

    “dices que se debe insertar información en la tabla noa_ave_max_pot, pero no dices qué información en especfíco”

    debo insertar lo obtenido en el select, para un rango de tablas del tipo NOA%

    ” comentas que la información será leída de distintas tablas, esto tal vez lo puedas armar con un query con union y disitntos accesos a las distintas tablas NOA%, a menos que estas cambien, entonces tendrás que armar un query dinámico para insertar la información de cada una las tablas donde le corresponde.”

    Con union he prebado, pero me trae la informacion en distintos rows. Debo insertar un solo regsitro.

    En realida la idea de usar bul_collect / insert, nace luego de probar otra alternativas.

    El código inicial usaba el paquete dbms_sql (que no funcionó) era asi:

    create or replace procedure update_potencia
    IS CURSOR from_table
    IS SELECT table_name
    FROM user_tables
    WHERE table_name LIKE ‘%AVE’;

    v_from_table from_table%ROWTYPE;

    source_cursor INTEGER;

    ignore INTEGER;

    BEGIN
    OPEN from_table;
    LOOP
    FETCH from_table INTO v_from_table;
    EXIT WHEN from_table%NOTFOUND;
    source_cursor := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(source_cursor, ‘INSERT INTO noa_ave_max_pot d (d.Pointnumber,
    d.hora_max_rtc, d.valor_max_rtc,
    d.hora_noa, d.valor_noa,
    d.hora_max_noa, d.valor_max_noa )
    (SELECT a.pointnumber pointnumber,
    a.utctimemax hora_max_rtc, valor_max_rtc a.value,
    b.utctime hora_ave, b.VALUE valor_ave,
    c.utctime hora_max_ave, c.VALUE value_max_ave
    FROM rtc_estaciones a, ‘ || v_from_table.table_name ||’ b,
    ‘ || v_from_table.table_name ||’ c
    WHERE A.value IN (SELECT MAX (VALUE)
    FROM rtc_estaciones)
    AND C.VALUE IN (SELECT MAX (VALUE)
    FROM ‘ || v_from_table.table_name ||’
    WHERE utctime BETWEEN SYSDATE – 3 AND SYSDATE – 2)
    AND A.utctimemax BETWEEN SYSDATE – 3 AND SYSDATE – 2
    AND B.utctime BETWEEN SYSDATE – 3 AND SYSDATE – 2
    AND C.utctime BETWEEN SYSDATE – 3 AND SYSDATE – 2
    AND a.utctimemax = b.utctime)’,DBMS_SQL.NATIVE);
    DBMS_OUTPUT.put_line (source_cursor);
    ignore := DBMS_SQL.EXECUTE(source_cursor);
    DBMS_SQL.CLOSE_CURSOR(source_cursor);
    END LOOP;

    CLOSE from_table;
    DBMS_OUTPUT.put_line (‘Processing completed Successfully’);
    EXCEPTION
    WHEN OTHERS
    THEN
    DBMS_OUTPUT.put_line (‘Error when reading cursor’);
    ROLLBACK;
    END;

    Muchas gracias nuevamente, espero puedas darme una mano con esto.

    Saludos!

  14. Orlando Olguín Olvera says:

    Hola Carlos,

    Estuve publicando algunas cosas y atendiendo cuestiones personales y por eso he demorado en que apareciera tu comentario, sé que ya ha pasado tiempo y espero que hayas resuelto tu problemas.

    De cualquier manera te comento que sólo hay que tener cuidado en el caso de los productos cartesianos uniendo la información de dos o más tablas que están haciendo join a través de una condición de igualdad entre ellas:

    select campos….
    from tabla1 a,
    tabla2 b
    where a.campo = b.campo <— condición de unión.

    Si no existe esa unión, se causa un producto cartesiano que es muy malo.

    Para le ejecución de queries como los INSERT que estás haciendo, te recomiendo la instrucción:

    execute immediate 'comando_sin_punto_y_coma';

    por ejemplo:

    execute immediate 'insert into tabla values (''val_string'', 9, sysdate)';

    Espero te sirva.

    Orlando.

  15. carlos says:

    Hola Orlando, gracias por tu respuesta. He podido resolver mi problema (soy nuevo en la programacion PL), pero ahora me gustaria que me ayudes con una búsqueda que estoy realizando.
    Trato de encontrar algún ejemplo base, en donde a partir de un rango dinámico de tablas, pueda realizar INSERT en otro rango de tablas.
    El problema que no logro resolver es como establecer una igualdad en los nombres de la tablas que obtengo con un cursor, y a partir de este resultado, realizar cada INSERT en la tabla correspondiente.
    Por ej:
    Si del primer cursor obtengo una tabla a_5min_100, el insert debo hacerlo en a_hora_100,
    Espero ser claro.
    Agradezco tu preocupación y me apoyo en tu experiencia (y en otros foros) para avanzar en la resolucion de mis casos,
    Muchas gracias
    Un abrazo

    Carlos

  16. Orlando Olguín Olvera says:

    Hola Carlos,

    Pienso que debes usar también el ‘execute immediate’ para realizar esto. Por ejemplo:

    for c in (select table_name from dba_tables where table_name like ‘A_%’) loop

    execute immediate ‘insert into ‘ || substr (c.table_name, 1, 2) || to_char (sysdate, ‘hh24’) ||
    ‘_100 values ‘….;

    end loop;

    Con el ciclo recorres todas las tablas que tengan el formato que tú le proporciones. Una vez que tengas el listado, entonces con el execute immediate creas el insert a la tabla nueva.

    Restará saber qué insertarás, si son los datos de la tabla origen, entonces hay que construir un cursor dinámico para esto:

    type cursor_dinamico is ref cursor;

    ls_cursor cursor_dinamico;
    lr_registro nombre_tabla%rowtype;

    begin
    open ls_cursor for ‘select * from ‘ || nombre_tabla;
    loop
    fetch ls_cursor into lr_registro;
    exit when ls_cursor%notfound;

    — usas los campos como lr_registro.campo1, lr_registro.campo2….

    end loop;
    close ls_cursor;
    end;

    Espero te sirva, pienso que primero pruebes en crear tu cursor dinámico y ya que lo tengas dominado, entonces lo alimentas con el otro cursor principal y lo tendrás listo.

    Orlando.

  17. carlos says:

    Hola Orlando, te molesto una vez mas.

    Necesito hacer un update de una a_5min a otra tabla a_hora_100, pero solo sobre algunos campos.

    El tema es que necesito que se haga el update teniendo en cuenta el valor maximo y minimo de uno de ellos, valor_max y valor_min

    desc a_5min_100

    A_5MIN_100
    (
    UTCTIME DATE NOT NULL,
    POINTNUMBER INTEGER NOT NULL,
    VALOR_INST FLOAT(126) DEFAULT (0.0),
    TLQ_INST INTEGER DEFAULT (32),
    VALOR_PROM FLOAT(126) DEFAULT (0.0),
    TLQ_PROM INTEGER DEFAULT (32),
    VALOR_MAX FLOAT(126) DEFAULT (0.0),
    TLQ_MAX INTEGER DEFAULT (32),
    UTCTIME_MAX DATE,
    VALOR_MIN FLOAT(126) DEFAULT (0.0),
    TLQ_MIN INTEGER DEFAULT (32),
    )

    desc a_hora_100

    (
    UTCTIME DATE NOT NULL,
    POINTNUMBER INTEGER NOT NULL,
    VALOR_INST FLOAT(126) DEFAULT (0.0),
    TLQ_INST INTEGER DEFAULT (32),
    VALOR_PROM FLOAT(126) DEFAULT (0.0),
    TLQ_PROM INTEGER DEFAULT (32),
    VALOR_MAX FLOAT(126) DEFAULT (0.0),
    TLQ_MAX INTEGER DEFAULT (32),
    UTCTIME_MAX DATE,
    VALOR_MIN FLOAT(126) DEFAULT (0.0),
    TLQ_MIN INTEGER DEFAULT (32),
    UTCTIME_MIN DATE
    )

    Tendras algun procedure de ejemplo para basarme en el?

    Muchas gracias!

  18. Orlando Olguín Olvera says:

    Hola Carlos,

    Creo que otra vez me tardé, ojalá que hayas resuelto tu problema, yo podría pensar en algo así:

    update tabla
    set campo = (select max(campo) from tablaorigen)
    where campo = valor:

    De esta forma no requerirías un procedimiento como tal.

    Orlando.

  19. darkone2k4 says:

    Hola Orlando.
    Te escribo para ver si me puedes orientar con lo que debo hacer:
    1.- Usando un programa en Delphi leo un archivo excel que puede contener desde 2 filas hasta 5000.
    2.- Una vez leído el archivo, se guarda en una tabla de paso para ser validada la información, validaciones como que las celdas que corresponden a valores, efectivamente sean números, que cumplan los largos correctos, etc.
    3.- Esa información corresponde a comprobantes contables, por lo que al leer el archivo excel con el programa, junto con guardar los datos en la tabla de paso, también guardo en una columna un número que me identifica cuantos registros pertenecen a un comprobante, por ejemplo:
    SEDE AÑO MES CUENTA MONTO_DEBITO MONTO_CREDITO GLOSA……. NUMERO
    10 2013 5 101010 10000 0 glosa1 1
    10 2013 5 101020 0 10000 glosa1 1
    10 2013 5 101010 20000 0 glosa1 1
    10 2013 5 101020 0 20000 glosa1 1
    20 2013 5 101010 10000 0 glosa2 2
    20 2013 5 101020 0 10000 glosa2 2
    21 2013 5 101010 35000 0 glosa3 3
    21 2013 5 101020 0 35000 glosa3 3

    4.- La información de esa tabla de paso, una vez validada que no tenga ningún tipo de error, debe ser insertada en la tabla de COMPROBANTES.
    5.- El “problema” es que para cada grupo de registros que tengan el mismo NUMERO, debo crear un número de FOLIO en esa tabla de comprobantes, es decir para todos los registros que tienen el numero 1, debo crear un folio, otro para los que tengan el numero 2 y así sucesivamente para cada grupo de registros, dependiendo del número que tengan asignado en la columna NUMERO. Por si acaso, el FOLIO lo creo de acuerdo a la sede,año,mes y tipo de documento que estoy creando, para lo que tengo un procedimiento donde envío esos datos y me retorna el número de folio.
    6.- Actualmente lo estoy haciendo con un cursor que va leyendo registro a registro, pero dependiendo de la cantidad de registros, se puede demorar hasta 20 o 30 minutos :(

    Será posible usar el bulk/collect en el escenario que tengo?

    Espero puedas ayudarme.

    salu2.

  20. Orlando Olguín Olvera says:

    Hola DarkOne,

    Según lo que estoy viendo, hay un número indeterminado de registros por cada NUMERO, ¿verdad?.

    Si es así, entonces lo que yo haría para probar el beneficio en velocidad, sería armar primero un cursor que agrupe los distintos NUMERO y lo vaya recorriendo. Por ejemplo, si los disitntos NUMERO son 1, 3, 5, 7.

    Una vez que tengas ese cursor, será cuestión de recorrerlo y por cada NUMERO, armar otro cursor que reciba como parámetro dicho NUMERO y entonces los tome todos con un Bulk Collect y luego, haga el Bulk Insert.

    Esto agilizará mucho a que tomes por montón de registros basado en NUMERO haciendo más rápida su inserción. Entonces basados en los números que puse, en la 1a pasada, tomará en una sola lectura los registros cuyo NUMERO sea igual a 1, en la 2a, aquellos iguales a 3 y así sucesivamente. Como será por montón la lectura e inserción, es muy probable que agilices el proceso.

    Pruébalo por favor e indícame si se mejora tu programa.

    Orlando.

  21. darkone2k4 says:

    Hola Orlando, muchas gracias por responder.

    Efectivamente Orlando, hay un número indeterminado de registros por cada NUMERO( estos números parten del 1 a N), por ejemplo, en este momento esa tabla de paso tiene 4600 registros, y hay 61 números que identifican la cantidad de comprobantes a los que equivalen esos registros.

    Voy a probar lo que me comentas y ver cual es el resultado.

    Muchas gracias.

  22. Orlando Olguín Olvera says:

    Hola DarkOne,

    Ojalá que todo salga muy bien.

    Orlando.

  23. Buenas, necesito tu ayuda Orlando, soy un técnico de BBDD en un datawarehouse, bueno estoy acabando las prácticas de 2 años en la empresa, y me han pedido que realize la estructura de un proceso ETL, para cargar unos 100.000.000 de registros, tanto la extracción de los datos, como los updates de los campos antes de insertarlos en nuestra BBDD de oracle se hacen bastante rápidos, el problema lo tengo en el insert, cuyo coste se estima en 15h, voy a proceder a usar tu sistema de insert, pero antes, me gustaría que me dieras tu opinión ya que es imprescindible no saturar la BBDD, te copio el resultado del script adjuntando este sistema:

    set space 0
    set head off
    set ver off
    set pagesize 0
    set serveroutput on size 1000000

    DECLARE
    V_DATE_MIN DATE;
    V_ID_MIN NUMBER(10);
    type array_object is table of LNDW_LOAD.&&1._H_MO_PARTICIPACION%rowtype index by binary_integer;
    cursor c is select * from LNDW_LOAD.&&1._H_MO_PARTICIPACION ;
    lr_datos array_object;

    BEGIN

    DELETE FROM LNDW_LOAD.&&1._H_MO_PARTICIPACION
    WHERE HMPN_ID IN (SELECT DISTINCT(HMPN_ID) FROM LNDW_&&1..H_MO_PARTICIPACION WHERE HMPN_FECHA_MO_D>=’14/03/2013′ AND HMPN_FECHA_MO_D<'22/03/2013')
    ;

    dbms_output.put_line(SQL%RowCount||' rows deleted.');
    COMMIT;

    open c;
    loop
    fetch c bulk collect into lr_datos limit 10000;
    begin
    forall i in 1..lr_datos.count save exceptions
    insert into LNDW_&&1..H_MO_PARTICIPACION 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);
    dbms_output.put_line(SQL%RowCount||' ROWS INSERTED IN FINAL TABLE.');
    END;
    /
    EXIT;

    El delete tampoco tarda mucho ya que filtro por fecha, son unos 6.000.000 de registros a insertar desde la tabla temporal a la tabla final que ya acumula unos 26.000.000.
    En caso de que esto funcionara, que limit me recomendarías? en caso de que no, podrías darme algún consejo? un saludo.

  24. Orlando Olguín Olvera says:

    Hola Eloy,

    En mi computadora (una hp viejita con 2gb de memoria) levanto una máquina virtual con una base de datos en Windows y un insert de una tabla de 3 millones de registros me lleva con el bulk collect 22 segundos, por lo que te debe funcionar bastante bien el bulk collect.

    Tu limit está bien.

    Si tienes el espacio suficiente en el UNDO tablespace para alojar todo el insert, te recomiendo poner el commit hasta el final del proceso.

    Si no quieres gastar mucho UNDO, entonces deberás poner un commit para cada carga de los 10,000 de tu limit.

    Orlando.

  25. Muchas gracias Orlando, realizando alguna modificación, conseguí cargar los registros de una forma adecuada .
    Un saludo.

  26. Orlando Olguín Olvera says:

    Hola Eloy.

    Qué bueno que te haya funcionado el procedimiento.

    Orlando.

  27. Carlos Zúñiga N. says:

    Estimado Orlando, un saludo y para variar te planteo una inquietud con el famoso limit del Bulk.

    Resulta que realizando prueba me di cuenta que si el limit no es un multiplo exacto de la cantidad de registros de la tabla que se levanta como input entonces se produce una inconsistencia en los datos de salida.

    Por ejemplo si tengo una tabla con 99 registros y mi limit es 10 entonces la salida sera de 90 registros y no los 99 del input.

    Si modifico la cantidad del limit aumentandola a 11 entonces si generara los 99 registros en la salida.

    A que se debe esto?, por las primeras explicaciones que das en tu comentario inicial esto no debiera ocurrir!.

    –// Para validar el caso que te planteo habria que partir creando las siguientes tablas :

    CREATE TABLE TMP_RID ( RID ROWID ) ;

    CREATE TABLE TMP_RID2 ( RID ROWID );

    —// Luego el script con el que hago el TEST del BULK es el siguiente

    DECLARE

    TYPE RIDLIST IS TABLE OF ROWID INDEX BY BINARY_INTEGER;

    CURSOR C1 IS
    SELECT RID
    FROM TMP_RID;

    ROWS RIDLIST;
    NUMROWS NATURAL := 10; –< al aumentar el rango a 11 graba los 99
    J NUMBER := 0;

    BEGIN

    execute immediate 'truncate table tmp_rid';
    execute immediate 'truncate table tmp_rid2';

    INSERT INTO TMP_RID
    SELECT ROWID
    FROM DBA_ALL_TABLES
    WHERE rownum < 100; — para que solo lea 99 registros, si se coloca 101 y se mantiene el limit graba los 100

    OPEN C1;

    LOOP
    FETCH C1
    BULK COLLECT INTO ROWS LIMIT NUMROWS;

    EXIT WHEN C1%NOTFOUND;
    J := J + 1;
    FORALL I IN ROWS.FIRST .. ROWS.LAST
    insert into tmp_rid2 values(rows (I) );

    END LOOP;

    CLOSE C1;
    commit;

    END;

    –// Habria que ejecutarlo y revisar las tablas TMP_RID Y TMP_RID2

    Select count(*) tmp_rid;

    99 ROWS;

    Select count(*) tmp_rid2;

    90 ROWS??

    –// Si luego se repite la ejecucion, cambiando el limite a 99 entonces la salida es igual al imput.

    Dado que en tus comentarios note que no le diste mayor importancia al valor del limit creo que con este ejemplo queda claro que si lo tiene e impacta en lo que queremos hacer.

    Gracias por la aclaración !

    Carlos

  28. Orlando Olguín Olvera says:

    Hola Carlos.

    Muy buen apunte, voy a corroborarlo y dejo aquí la prueba.

    Orlando.

  29. Jorge Aparicio says:

    Hola Orlando,

    Excelente material!!!
    Me ha servido para entender el concepto.

    Saludos.

  30. Orlando Olguín Olvera says:

    Hola Jorge.

    Qué bueno que te ha servido.

    Orlando.

  31. Ilse Romero says:

    !!Hola Orlando!!

    Tu código funciona excelente, y tu explicación lo es mucho más.

    Te agradezco mucho, me ha sido de mucha mucha ayuda.

    Saludos y ojalá sigas ayudando a mucha gente más, así como lo has hecho conmigo.

  32. Orlando Olguín Olvera says:

    Hola Ilse.

    Muchas gracias por tus comentarios y qué bueno que te haya funcionado la cosa.

    Orlando.

  33. Aurelio JS says:

    Que buena explicación, sé que ya llevas tiempo con este Post, pero que detallista por todos los comentarios, me quedó muy claro.

    Felicidades

  34. Orlando Olguín Olvera says:

    Hola Aurelio.

    Muchas gracias por tus comentarios.

    Orlando.

  35. Ernesto NR. says:

    Gracias Orlando!

    Esta información es muy buena.

    Yo tengo un caso de apenas unos 2 millones de recs a procesar de un inventario en un proceso de cierre mensual.

    Mi pregunta seria si el valor en el LIMIT depende más del tamaña de a tabla a procesar o tenga que ver con la capacidad de procesamiento que tenga nuestra configuración de Oracle?

    En tu ejemplo parecería que 1000 son los óptimos, vdd? Pero cuantos recs en memoria son los optimos a procesar en una configuracion oracle promedio?

    Saludos cordiales,
    Ernesto NR.

  36. Orlando Olguín Olvera says:

    Hola Ernesto.

    Más bien depende de la memoria y procesador.

    El LIMIT es sólo el tamaño de rebanada que se estará trayendo cada vez y subiendo a memoria en el arreglo. Por lo que con una buena cantidad de memoria y un buen procesador, podrías tener mejores resultados con un valor mayor para este parámetro.

    Prueba con otros valores para ver qué resultado obtienes.

    Orlando.

  37. Cesar Portillo says:

    Hola Orlando.

    Muchas gracias por tu post, está explicado al detalle.

    Sobre todo el solventar algunas dudas en comentarios.

    Sigue así y de nuevo muchas gracias.

  38. Orlando Olguín Olvera says:

    Hola César.

    Muchas gracias por tus comentarios y qué bueno que te fue de ayuda.

    Orlando.

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

A %d blogueros les gusta esto: