Foros del Web » Programación para mayores de 30 ;) » Bases de Datos General » Oracle »

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

Estas en el tema de lectura, actualización e inserción usado BULK COLLECT en PL/SQL en el foro de Oracle en Foros del Web. 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 ...
  #1 (permalink)  
Antiguo 08/05/2012, 06:33
Avatar de gnzsoloyo
Moderador criollo
 
Fecha de Ingreso: noviembre-2007
Ubicación: Actualmente en Buenos Aires (el enemigo ancestral)
Mensajes: 23.318
Antigüedad: 11 años, 6 meses
Puntos: 2652
lectura, actualización e inserción usado BULK COLLECT en PL/SQL

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:
Ver original
  1. PROCEDURE APPAnual
  2.  
  3.     IS
  4.     TYPE t_aplica IS TABLE OF APLICA%ROWTYPE;
  5.     DECLARE v_aplica t_aplica;
  6.     BEGIN
  7.         -- Recupero todos los registros del año anterior
  8.         SELECT  
  9.             AP.id, AP.numero, AP.cont, AP.n_serial, AP.c_serv, AP.u_alta, AP.f_alta, AP.u_baja,AP.f_baja
  10.         BULK COLLECT INTO v_aplica
  11.         FROM APLICA AP
  12.         WHERE
  13.             TO_CHAR(AP.F_ALTA,'YYYY') = TO_CHAR(SYSDATE,'YYYY')-1
  14.             AND AP.U_BAJA IS NULL
  15.             AND AP.F_BAJA IS NULL;
  16.            
  17.         -- INSERT    
  18.         FORALL i IN v_aplica.FIRST .. v_aplica.LAST
  19.         INSERT INTO APLICA(
  20.             AP.id, AP.numero, AP.cont, AP.n_serial, AP.c_serv, AP.u_alta, AP.f_alta, AP.u_baja,AP.f_baja)
  21.         VALUES(
  22.             app_seq.NEXTVAL, v_aplica(i).numero, v_aplica(i).cnt, v_aplica(i).n_serial,
  23.             v_aplica(i).c_serv, v_aplica(i).u_alta, SYSDATE, NULL,NULL);
  24.        
  25.         -- UPDATE
  26.            
  27.         FORALL i IN v_aplica.FIRST .. v_aplica.LAST
  28.         UPDATE aplica A
  29.            SET
  30.               A.u_baja = v_aplica(i).u_baja,
  31.               A.f_baja = SYSDATE
  32.         WHERE
  33.             A.n_serial = v_aplica(i).n_serial
  34.             AND A.f_alta = v_aplica(i).f_alta;
  35.     END;
__________________
¿A quién le enseñan sus aciertos?, si yo aprendo de mis errores constantemente...
"El problema es la interfase silla-teclado." (Gillermo Luque)
  #2 (permalink)  
Antiguo 09/05/2012, 20:21
Avatar de huesos52
Colaborador
 
Fecha de Ingreso: febrero-2009
Ubicación: Manizales - Colombia
Mensajes: 5.980
Antigüedad: 10 años, 4 meses
Puntos: 360
Respuesta: lectura, actualización e inserción usado BULK COLLECT en PL/SQL

Hola gnzsoloyo, me place saludarte

Mi humilde opinión.

Cita:
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.

Cita:
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í

Cita:
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
__________________
Without data, You are another person with an opinion.
W. Edwads Deming
  #3 (permalink)  
Antiguo 10/05/2012, 11:05
Avatar de gnzsoloyo
Moderador criollo
 
Fecha de Ingreso: noviembre-2007
Ubicación: Actualmente en Buenos Aires (el enemigo ancestral)
Mensajes: 23.318
Antigüedad: 11 años, 6 meses
Puntos: 2652
Respuesta: lectura, actualización e inserción usado BULK COLLECT en PL/SQL

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.
__________________
¿A quién le enseñan sus aciertos?, si yo aprendo de mis errores constantemente...
"El problema es la interfase silla-teclado." (Gillermo Luque)
  #4 (permalink)  
Antiguo 10/05/2012, 14:55
Avatar de huesos52
Colaborador
 
Fecha de Ingreso: febrero-2009
Ubicación: Manizales - Colombia
Mensajes: 5.980
Antigüedad: 10 años, 4 meses
Puntos: 360
Respuesta: lectura, actualización e inserción usado BULK COLLECT en PL/SQL

Cita:
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
__________________
Without data, You are another person with an opinion.
W. Edwads Deming

Última edición por huesos52; 10/05/2012 a las 15:09
  #5 (permalink)  
Antiguo 10/05/2012, 15:31
Avatar de huesos52
Colaborador
 
Fecha de Ingreso: febrero-2009
Ubicación: Manizales - Colombia
Mensajes: 5.980
Antigüedad: 10 años, 4 meses
Puntos: 360
Respuesta: lectura, actualización e inserción usado BULK COLLECT en PL/SQL

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:
Ver original
  1. FORALL i IN v_aplica.FIRST .. v_aplica.LAST
  2.         INSERT INTO APLICA(
  3.             AP.id, AP.numero, AP.cont, AP.n_serial, AP.c_serv, AP.u_alta, AP.f_alta, AP.u_baja,AP.f_baja)
  4.         VALUES(
  5.             app_seq.NEXTVAL, v_aplica(i).numero, v_aplica(i).cnt, v_aplica(i).n_serial,
  6.             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
__________________
Without data, You are another person with an opinion.
W. Edwads Deming
  #6 (permalink)  
Antiguo 11/05/2012, 19:06
Avatar de matanga  
Fecha de Ingreso: octubre-2007
Ubicación: España
Mensajes: 1.091
Antigüedad: 11 años, 7 meses
Puntos: 85
Respuesta: lectura, actualización e inserción usado BULK COLLECT en PL/SQL

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

Etiquetas: bulk, inserción, lectura, pl-sql, select, tabla, formulario, campos
Atención: Estás leyendo un tema que no tiene actividad desde hace más de 6 MESES, te recomendamos abrir un Nuevo tema en lugar de responder al actual.
Respuesta




La zona horaria es GMT -6. Ahora son las 18:55.