Optimizar inserciones y borrados

[kaly_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:

Código SQL:

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
  num := 0;
  BEGIN
    LOOP
      DELETE FROM tabla5
       WHERE FECHA < ADD_MONTHS(sysdate, -24)
         AND ROWNUM < 3000;
      num := num + 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.