lectura, actualización e inserción usado BULK COLLECT en PL/SQL

[gnzsoloyo]

Buenos dias.
El titulo es mas o menos claro: necesito crear un stored proceudre en Oracle tal que realice una lectura de una tabla con más de 1000 millones de registros de 290 bytes de ancho. Obviamente estoy hablando de un enorme caudal de datos.
El proceso que debo hacer es simple:
1) Leer todos los registros de cierto rango de fechas (un año entero).
2) Crear un registro nuevo con cada uno de los leídos, con la fecha de alta actualizada.
3) Actualizar cada uno de los del año leído poniendole en un campo la fecha de baja.

He pensado hacer esto por medio de un BULK COLLECT (actualmente un SP que se usa utiliza un cursor para eso), pero como no tengo práctica en el tema, sólo teoría, tengo un par de dudas:
1) ¿El array resultante de un BULK COLLECT se puede recorrer una sola vez, o se puede hacer mas de una vez sin necesidad de volver a leer?
2) ¿Es necesario ejecutar un UPDATE con los datos del registro leído en un momento dado, o el actualiza los campos del registro del array se encarga de eso?
3) ¿Es conveniente leer toda la tabla en una sola operación, o es más performantico hacerlo en bloques de N registros, usando LIMIT?

Creo que la mejor pregunta sería: ¿Cómo sería el esquema de un SP que cumpla con lo que quiero hacer?

En principio, he bosquejado esto (lamento tener que cambiar el nombre de las talas, pero hay compromisos firmados, lo juro):

Código SQL:

PROCEDURE APPAnual
 
    IS
    TYPE t_aplica IS TABLE OF APLICA%ROWTYPE;
    DECLARE v_aplica t_aplica;
    BEGIN
        -- Recupero todos los registros del año anterior
        SELECT  
            AP.id, AP.numero, AP.cont, AP.n_serial, AP.c_serv, AP.u_alta, AP.f_alta, AP.u_baja,AP.f_baja
        BULK COLLECT INTO v_aplica
        FROM APLICA AP
        WHERE 
            TO_CHAR(AP.F_ALTA,'YYYY') = TO_CHAR(SYSDATE,'YYYY')-1
            AND AP.U_BAJA IS NULL
            AND AP.F_BAJA IS NULL;
            
        -- INSERT    
        FORALL i IN v_aplica.FIRST .. v_aplica.LAST
        INSERT INTO APLICA(
            AP.id, AP.numero, AP.cont, AP.n_serial, AP.c_serv, AP.u_alta, AP.f_alta, AP.u_baja,AP.f_baja)
        VALUES(
            app_seq.NEXTVAL, v_aplica(i).numero, v_aplica(i).cnt, v_aplica(i).n_serial, 
            v_aplica(i).c_serv, v_aplica(i).u_alta, SYSDATE, NULL,NULL);
        
        -- UPDATE
           
        FORALL i IN v_aplica.FIRST .. v_aplica.LAST
        UPDATE aplica A
           SET
              A.u_baja = v_aplica(i).u_baja,
              A.f_baja = SYSDATE
        WHERE
            A.n_serial = v_aplica(i).n_serial
            AND A.f_alta = v_aplica(i).f_alta;
    END;
 

[huesos52]

Hola gnzsoloyo, me place saludarte

Mi humilde opinión.

1) ¿El array resultante de un BULK COLLECT se puede recorrer una sola vez, o se puede hacer mas de una vez sin necesidad de volver a leer?

Se puede usar los valores de la tabla en memoria veces que quieras mientras haya una sesión activa o no ejecutes la sentencia tabla.DELETE.
La ventaja de bulk collect con respecto a los cursores, radica en que son tablas en memoria que pueden ser consultadas y llenadas sin posiciones fijas.

2) ¿Es necesario ejecutar un UPDATE con los datos del registro leído en un momento dado, o el actualiza los campos del registro del array se encarga de eso?

Esta no la entendí

3) ¿Es conveniente leer toda la tabla en una sola operación, o es más performantico hacerlo en bloques de N registros, usando LIMIT?

Si la tabla es muy grande es recomendable partir la operación en varias aperturas. la razón de esto, es que por bulk collect envias a memoria una cantidad enorme de registros. Este proceso no genera bloqueos en los registros resultantes, lo que permite que otras sesiones modifiquen los datos que fueron cargados a partir de la consulta. Cuando esto pasa, al momento de llegar a las posiciones de la tabla en memoria, la información no va a ser igual a la contenida en la BD, por esta razón va a buscar a los tablespace UNDO si hay transacciones que involucren estos registros. Estos tablespace tienen un parametro de configuración llamado undo_retention (15 minutos por defecto) que solo retiene cambios por este lapso de tiempo. Al pasar este tiempo, la información contenida en la tabla en memoria y la base de datos ya no va a ser la misma y no existirá rastro de un dato consistente. Cuando pasa esto se genera el error ORA-1555. (Snapshot too old)

Espero haberte ayudado.

saludos

[gnzsoloyo]

Hola, Huesos52. La verdad es que estaba esperando que respondieras tu. :D

La info que me has dado es muy esclarecedora, y me servirá para hacer algunos ajustes.
Por otro lado, básicamente ya me has respondido la duda del segundo punto.
Lo que quería saber era en realidad si los registros de la tabla virtual eran registros en memoria, o punteros al registro base. Si eran copias de lo contenido en la tabla, implicaba que para que el dato quedara fijo había que proceder a realizar un UPDATE.

Es decir, que el código propuesto en el ejemplo debería servir. ¿No te parece?

Con respecto a los dirty readings que puedan darse, en realidad no habrá tal, porque la tabla sobre la que se realizará la operatoria se bloquea para toda transacción hasta que se completa el proceso. Es una operación totalmente cerrada.
Y de todos modos, todos los otros procesos que tocan la tabla sólo pueden dar de baja los registros si pertenecen al año en curso, mientras que los viejos son inaccesibles.

[huesos52]

Lo que quería saber era en realidad si los registros de la tabla virtual eran registros en memoria, o punteros al registro base. Si eran copias de lo contenido en la tabla, implicaba que para que el dato quedara fijo había que proceder a realizar un UPDATE.

Es decir, que el código propuesto en el ejemplo debería servir. ¿No te parece?

Creo que funcionaría de mil maravillas.
Lo unico que le añadiría de mas, sería el commit al final de la operación.
A diferencia de MySQL y PostgreSQL (Y muchos motores de BD) el commit en oracle es explicito por defecto.

Si levantas la operación después de ejecutado el proceso, todos los registros involucrados en la tabla estarán bloqueados por tu transacción. Las lecturas no se visualizarían en otras sesiones y tendrías bastantes problemas.


Saludos

[huesos52]

Hola gnzsoloyo

La verdad se me cruzaron un poco los cables y no quedé convencido con la respuesta que te dí.

Cuando haces uso de cursores, el cursor es un apuntador al registro. Si este cambia, es cuando se presentan los problemas relacionados con snapshot too old en caso que el cursor abierto sea muy grande. Esto aplica para toda mi respuesta del primer post.
Cuando haces uso de bulk collect, envías todo el resultado de la consulta a memoria RAM. Está operación es independiente del movimiento de la tabla, ya que por cuestiones de performance, toda la información ya se encuentra en memoria y no depende de las acciones que se presenten en la tabla.

Según esto, yo no consideraría necesario realizar UPDATE.

Quedaría unicamente así:

Código SQL:

FORALL i IN v_aplica.FIRST .. v_aplica.LAST
        INSERT INTO APLICA(
            AP.id, AP.numero, AP.cont, AP.n_serial, AP.c_serv, AP.u_alta, AP.f_alta, AP.u_baja,AP.f_baja)
        VALUES(
            app_seq.NEXTVAL, v_aplica(i).numero, v_aplica(i).cnt, v_aplica(i).n_serial, 
            v_aplica(i).c_serv, v_aplica(i).u_alta, SYSDATE, v_aplica(i).n_serial,SYSDATE);
 

Esto lo digo asumiendo que la tabla del INSERT y la tabla del UPDATE son la misma.

Saludos

[matanga]

Es cierto que el bulk es más eficiente que los cursores convencionales, pero también tiene sus desventajas, la principal es que sube los registros a un array de memoria en la PGA, y por esto pueden pasar dos cosas, que el tamaño de la PGA sea insuficiente y el proceso termine con error, o que el proceso consuma la mayoría de la PGA y no deje lugar para los demás usuarios, si bien esto se puede controlar con el LIMIT, la solución es relativa porque no todas las PGA's son iguales y un proceso puede funcionar bien en desarrollo y mal en producción.

Si entendí bien la lógica del proceso, puedes evitar el bulk con la consulta:

Código:

PROCEDURE APPAnual
 
INSERT INTO 
 APLICA( AP.id, AP.numero, AP.cont, AP.n_serial, AP.c_serv, AP.u_alta, AP.f_alta, AP.u_baja,AP.f_baja)
SELECT app_seq.NEXTVAL, AP.numero, AP.cont, AP.n_serial, AP.c_serv, AP.u_alta, sysdate, null
 FROM APLICA AP
    WHERE 
     TO_CHAR(AP.F_ALTA,'YYYY') = TO_CHAR(SYSDATE,'YYYY')-1
      AND AP.U_BAJA IS NULL
      AND AP.F_BAJA IS NULL;

        UPDATE aplica A
           SET
              A.u_baja = A.u_baja,
              A.f_baja = SYSDATE
        WHERE
            TO_CHAR(A.F_ALTA,'YYYY') = TO_CHAR(SYSDATE,'YYYY')-1
            AND AP.U_BAJA IS NULL
            AND AP.F_BAJA IS NULL;
 END;

Saludos