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