CTA particionando la tabla - Oracle

En recientes días me ha tocado trabajar en eliminar unos datos de una tabla en una base de datos con algunos problemas de rendimiento. Como estrategia había planteado lo siguiente:

• Llevar los datos a una tabla nueva auxiliar particionada que me permita alojar los datos de manera que los pueda recuperar sin problemas.

CREATE TABLE TABLA_AUX (
    CAMPO1            VARCHAR2(4),
    CAMPO2            NUMBER DEFAULT 0,
    CAMPO3            NUMBER DEFAULT 0,
    CAMPO4            DATE,
    CAMPO5            VARCHAR2(80)
) PARTITION BY LIST(CAMPO1) (
    PARTITION PARTICION1 VALUES ('1') NOLOGGING TABLESPACE TABLESPACEXXX,
    PARTITION PARTICION2 VALUES ('2') NOLOGGING TABLESPACE TABLESPACEXXX,
    PARTITION PARTICION3 VALUES ('3') NOLOGGING TABLESPACE TABLESPACEXXX
);

/*Programa 1*/

DECLARE
TYPE SOURCE_TBL IS TABLE OF TABLA%ROWTYPE;
L_CONTAINER   SOURCE_TBL := SOURCE_TBL();

CURSOR C1 IS
  SELECT *
  FROM TABLA
  WHERE CAMPO1 = '1'
;

BEGIN
  OPEN C1;
  LOOP
  FETCH C1 BULK COLLECT INTO L_CONTAINER LIMIT 10000;
  FORALL I IN 1..L_CONTAINER.COUNT
  INSERT INTO TABLA_AUX VALUES L_CONTAINER(I);
  EXIT WHEN L_CONTAINER.COUNT = 0;
  END LOOP;
  COMMIT;
  CLOSE C1;
END;

/*Programa 2*/ 

DECLARE
TYPE SOURCE_TBL IS TABLE OF TABLA%ROWTYPE;
L_CONTAINER   SOURCE_TBL := SOURCE_TBL();

CURSOR C1 IS
  SELECT *
  FROM TABLA
  WHERE CAMPO1 = '2'
;

BEGIN
  OPEN C1;
  LOOP
  FETCH C1 BULK COLLECT INTO L_CONTAINER LIMIT 10000;
  FORALL I IN 1..L_CONTAINER.COUNT
  INSERT INTO TABLA_AUX VALUES L_CONTAINER(I);
  EXIT WHEN L_CONTAINER.COUNT = 0;
  END LOOP;
  COMMIT;
  CLOSE C1;
END;

/*Programa 3*/ 

DECLARE
TYPE SOURCE_TBL IS TABLE OF TABLA%ROWTYPE;
L_CONTAINER   SOURCE_TBL := SOURCE_TBL();

CURSOR C1 IS
  SELECT *
  FROM TABLA
  WHERE CAMPO1 = '3'
;

BEGIN
  OPEN C1;
  LOOP
  FETCH C1 BULK COLLECT INTO L_CONTAINER LIMIT 10000;
  FORALL I IN 1..L_CONTAINER.COUNT
  INSERT INTO TABLA_AUX VALUES L_CONTAINER(I);
  EXIT WHEN L_CONTAINER.COUNT = 0;
  END LOOP;
  COMMIT;
  CLOSE C1;
END;

• Luego de esto eliminar la tabla inicial y eliminar los índices.

DROP TABLE TABLA;

• Crear particiones a la tabla inicial. 

CREATE TABLE TABLA (
    CAMPO1            VARCHAR2(4),
    CAMPO2            NUMBER DEFAULT 0,
    CAMPO3            NUMBER DEFAULT 0,
    CAMPO4            DATE,
    CAMPO5            VARCHAR2(80)
) PARTITION BY LIST(CAMPO1) (
    PARTITION PARTICION1 VALUES ('1') NOLOGGING TABLESPACE TABLESPACEXXX,
    PARTITION PARTICION2 VALUES ('2') NOLOGGING TABLESPACE TABLESPACEXXX,
    PARTITION PARTICION3 VALUES ('3') NOLOGGING TABLESPACE TABLESPACEXXX
);

• Llevar los datos de la tabla auxiliar a la tabla.

/*Programa 1*/



DECLARE
TYPE SOURCE_TBL IS TABLE OF TABLA_AUX%ROWTYPE;
L_CONTAINER   SOURCE_TBL := SOURCE_TBL();

CURSOR C1 IS
  SELECT *
  FROM TABLA_AUX
  WHERE CAMPO1 = '1'
;

BEGIN
  OPEN C1;
  LOOP
  FETCH C1 BULK COLLECT INTO L_CONTAINER LIMIT 10000;
  FORALL I IN 1..L_CONTAINER.COUNT
  INSERT INTO TABLA VALUES L_CONTAINER(I);
  EXIT WHEN L_CONTAINER.COUNT = 0;
  END LOOP;
  COMMIT;
  CLOSE C1;
END;



/*Programa 2*/


DECLARE
TYPE SOURCE_TBL IS TABLE OF TABLA_AUX%ROWTYPE;
L_CONTAINER   SOURCE_TBL := SOURCE_TBL();


CURSOR C1 IS
  SELECT *
  FROM TABLA_AUX
  WHERE CAMPO1 = '2'
;

BEGIN
  OPEN C1;
  LOOP
  FETCH C1 BULK COLLECT INTO L_CONTAINER LIMIT 10000;
  FORALL I IN 1..L_CONTAINER.COUNT
  INSERT INTO TABLA VALUES L_CONTAINER(I);
  EXIT WHEN L_CONTAINER.COUNT = 0;
  END LOOP;
  COMMIT;
  CLOSE C1;
END;



/*Programa 3*/


DECLARE
TYPE SOURCE_TBL IS TABLE OF TABLA_AUX%ROWTYPE;
L_CONTAINER   SOURCE_TBL := SOURCE_TBL();


CURSOR C1 IS
  SELECT *
  FROM TABLA_AUX
  WHERE CAMPO1 = '3'
;

BEGIN
  OPEN C1;
  LOOP
  FETCH C1 BULK COLLECT INTO L_CONTAINER LIMIT 10000;
  FORALL I IN 1..L_CONTAINER.COUNT
  INSERT INTO TABLA VALUES L_CONTAINER(I);
  EXIT WHEN L_CONTAINER.COUNT = 0;
  END LOOP;
  COMMIT;
  CLOSE C1;
END;

• Crear los índices.

Este proceso si bien funciona bien, hay dos pasos que son repetitivos y se podría optimizar con un simple rename de la tabla auxiliar y con esto solo se traslada la información una vez, quiere decir que en el punto en el que se borra la tabla inicial {TABLA}, lo que podría hacer es borrar y renombrar la tabla inicial.

Antes de mandar este proceso a ejecutarse le consulte a una persona si veia bien el proceso y lo que me dijo es que se podía mejorar aún más el proceso, realizando un CTA (Create Table as Select). Al principio el problema ocn esta solución era la partición de la tabla, sin embargo es factible realizar el particionamiento de la siguiente manera:

• Crear la tabla como un CTA y creando la partición (Resaltado en amarillo y con letras rojas la partición).

CREATE TABLE TABLA_AUX
PARTITION BY LIST(CAMPO1) (
    PARTITION PARTICION_1 VALUES ('1') NOLOGGING TABLESPACE TABLESPACEXXX,
    PARTITION PARTICION_2 VALUES ('2') NOLOGGING TABLESPACE TABLESPACEXXX,
    PARTITION PARTICION_3 VALUES ('3') NOLOGGING TABLESPACE TABLESPACEXXX
) AS
SELECT CAMPO1,CAMPO2,CAMPO3,CAMPO4,CAMPO5
FROM TABLA
WHERE CAMPO1 IN ('1','2','3')
;

• Renombrar la tabla.

RENAME TALBE TABLA_AUX TO TABLA;

Luego se crean los índices y se dan los privilegios.

Lo interesante del cambio es el resultado en tiempos de ejecución, mientras la forma en la que se usaba el bulk colect se demoró 12:38 minutos moviendo 50 millones de registro, la última opción utilizada se demoro 3:30 minutos. Una reducción de tres veces y un poco más.

La verdad es un truco al cual todavía le tengo un poco de desconfianza pero ante la evaluación de tiempos con una cantidad de registros considerable, hay que decir que es una opción para mover datos que se tiene que tener en cuenta.

Espero que les sirva!

:D