Oracle - Optimizar inserciones y borrados

   
Vista:
Imágen de perfil de kaly

Optimizar inserciones y borrados

Publicado por kaly (4 intervenciones) el 17/02/2014 17:43:20
Hola soy Kaly. Antes de nada un saludo a todos los foreros.

A ver si me pueden echar una mano pq estoy un poco perdida con un procedimiento que tengo que optimizar. La version de oracle sobre la que va a correr el procedimiento es la 11. El procedimiento originalmente tenia declarado un cursor que se recorria registro a registro para realizar inserciones y borrados a partir de los valores obtenidos, el caso es que esto se prolongaba mucho en el tiempo pq la consulta obtenia muchos registros.

Corregirme si me equivoco, pero por lo que he leido es mejor recuperar los resultados de la consulta con la clausula bulk collect junto con un limit de 100 registros, ademas de desactivar disparadores, restricciones e indices de las tablas implicadas. Lo he cambiado y me ha quedado tal que asi:

DECLARE
c_datos IS(
SELECT campo1, campo2, campo3 FROM tabla WHERE fecha < ADD_MONTHS(sysdate, -12));

TYPE datos_tt IS TABLE OF tabla%ROWTYPE INDEX BY PLS_INTEGER;
var_datos datos_tt;
limit_in PLS_INTEGER DEFAULT 100;
contador INTEGER := 0;
BEGIN
--SE DESACTIVA TRIGGERS
ALTER TRIGGER TGR_TABLA_ELIM DISABLE;
--SE DESACTIVA RESTRICCIONES FOREING KEY
ALTER TABLE tabla DISABLE CONSTRAINT FK_TABLA;
ALTER TABLE tabla2 DISABLE CONSTRAINT FK_TABLA2;
--SE DESACTIVA RESTRICCIONES PRIMARY KEY
ALTER TABLE tabla DISABLE CONSTRAINT PK_TABLA2;
ALTER TABLE tabla2 DISABLE CONSTRAINT PK_TABLA;
--SE DESACTIVA INDICES
ALTER INDEX IDX_TABLA ON TABLA DISABLE;
ALTER INDEX IDX_TABLA2 ON TABLA2 DISABLE;
-- Se inicia proceso de historizacion
OPEN c_datos;
LOOP
FETCH c_datos BULK COLLECT INTO var_datos LIMIT limit_in;
EXIT WHEN var_datos.COUNT = 0;
--Se inserta en el historico los registros obtenidos
FORALL idx IN 1 .. var_datos.COUNT
INSERT INTO tabla_historico
(TBL_CLAVE, CAMPO1, CAMPO2, CAMPO3, CAMPO4, FECHA)
VALUES
(var_datos(idx).TBL_CLAVE, var_datos(idx).CAMPO1, var_datos(idx).CAMPO2, var_datos(idx).CAMPO3,
var_datos(idx).CAMPO4, var_datos(idx).FECHA);
--Se inserta en el historico 2 los registros de la tabla2 que coincidan con los obtenidos
FORALL idx IN 1 .. var_datos.COUNT
INSERT INTO tabla2_historico
(TBL_CLAVE, CAMPO7, CAMPO8, FECHA)
SELECT TBL_CLAVE, CAMPO7, CAMPO8, FECHA FROM tabla2 WHERE TBL_CLAVE = var_datos(idx).TBL_CLAVE;
--Se eliminan los registros de tabla2 insertados en tabla2_historico
FORALL idx IN 1 .. var_datos.COUNT
DELETE FROM tabla2 WHERE TBL_CLAVE = var_datos(idx).TBL_CLAVE;
--El trigger desactivado insertaba los registros eliminados en esta tabla, se hace aqui:
FORALL idx IN 1 .. var_datos.COUNT
INSERT INTO TABLA_ELIM (TBL_CLAVE, FECHA) VALUES (var_datos(idx).TBL_CLAVE, SYSDATE);
--Se eliminan registros obtenidos
FORALL idx IN 1 .. var_datos.COUNT
DELETE FROM SGCA2.TABLA WHERE TBL_CLAVE = var_datos(idx).TBL_CLAVE;
--Se confirma transaccion cada 3000 registros
contador := contador + var_datos.COUNT;
IF MOD(contador, 3000) THEN
COMMIT;
END IF;
END LOOP;
COMMIT;
CLOSE c_datos;
--Se elimina registros de la tabla5 anteriores a 24 meses
contador:= 0;
BEGIN
LOOP
DELETE FROM tabla5
WHERE FECHA < ADD_MONTHS(sysdate, -24)
AND ROWNUM < 3000;
contador:= contador+ SQL%ROWCOUNT;
EXIT WHEN SQL%ROWCOUNT < 2999;
COMMIT;
END LOOP;
COMMIT;
END;
--SE ACTIVA TRIGGERS
ALTER TRIGGER TGR_TABLA_ELIM ENABLE;
--SE ACTIVAN RESTRICCIONES
ALTER TABLE tabla ENABLE NOVALIDATE CONSTRAINT PK_TABLA2;
ALTER TABLE tabla2 ENABLE NOVALIDATE CONSTRAINT PK_TABLA;
ALTER TABLE tabla ENABLE NOVALIDATE CONSTRAINT FK_TABLA;
ALTER TABLE tabla2 ENABLE NOVALIDATE CONSTRAINT FK_TABLA2;
--SE ACTIVA INDICES
ALTER INDEX IDX_TABLA ON TABLA REBUILD;
ALTER INDEX IDX_TABLA2 ON TABLA2 REBUILD;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE || ' - ' || SQLERRM);
ROLLBACK;
END;

Las dudas que tengo son las siguientes:

1. Que es mejor utilizar en el FORALL: var_datos.COUNT o var_datos.LAST?

2. Las tablas donde se insertan valores no tienen triggers, restricciones ni indices. Las tablas donde se realizan los borrados si. La pregunta es para el borrado tambien es recomendable desactivar indices, triggers y restricciones. Yo los he desactivado y debido a eso he tenido que añadir un ForALL a mayores que me realice lo que hacia el trigger que se activaba en la eliminacion de registros de "tabla".

3. Al final de la coleccion, despues de cerrarla he añadido un loop que elimina de otra tabla que no tiene nada que ver con el cursor registros anteriores a 24 meses, seria mas apropiado realizar con ella un bulk collect??

4. Existe otra forma de realizar este tipo de procedimiento mas eficaz que un cursor o un bulk collect??

Muchas gracias y espero que me puedan dar algo de luz o corregir mi planteamiento
Valora esta pregunta
Me gusta: Está pregunta es útil y esta claraNo me gusta: Está pregunta no esta clara o no es útil
0
Responder

Optimizar inserciones y borrados

Publicado por Rafael (178 intervenciones) el 03/03/2014 11:13:43
Respuesta a tus preguntas:

1. Da lo mismo, no hay difetencia en cuanto a mejoria o no al usar uno u el otro.
2. El desactivar triggers e indices ayudara a agilizar el proceso pero al final tendras que reactivarlos y reindexar, ya que quieres mantener tu integridad referencial.
3. Es mas rapido un direct path y sobre todo si tuvieses un indice por la fecha sin embargo hay opciones mas eficientes dependiendo de la cantidad de registros.
4. La forma mas sencilla y rapida de realizar las historificaciones es si tu tabla cuenta con particiones por periodo, ya que si haces particiones por fecha puedes mover la particion deseada entre dos tablas...

Leete esta documentación:
http://docs.oracle.com/cd/A64702_01/doc/server.805/a58397/ch11.htm

Como veras es mas eficiente y rapido.

Saludos
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar
Imágen de perfil de kaly

Optimizar inserciones y borrados

Publicado por kaly (4 intervenciones) el 31/03/2014 09:41:35
Gracias por la respuesta
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar