Particionar tabla existente (DBMS_REDEFINITION vs. EXCHANGE PARTITION) | ExpoDBA

Particionar tabla existente (DBMS_REDEFINITION vs. EXCHANGE PARTITION)

Particionar tabla existente (DBMS_REDEFINITION vs. EXCHANGE PARTITION)

Para poder elegir el mejor método para particionar una tabla existente, es necesario saber con que técnicas, y procedimientos se cuentan y con las ventajas y desventajas de cada uno.

 

Al hacer el primer paneo de las alternativas surgen:

  1. Exchange partition en una tabla particionada y luego split patition
  2. Insert as select en una nueva tabla particionada
  3. Utilizar el package DBMS_REDEFINITION

 

Exchange partition en una tabla particionada y luego split patition:

Este procedimiento lleva los siguientes pasos:

  1. Crear una nueva tabla idem a la tabla origen pero particionada con una única partición.
  2. Realizar un exchange partition entre la tabla origen y la partición destino
  3. Realizar todos los split partition que sean necesarios sobre la tabla particionada para crear todas las paticiones.
  4. Reasignar los indices, constraints, triggers a la nueva tabla
  5. Borrar tabla Origen
  6. Renombrar destino

 

Este procedimiento funciona, pero es engañoso.

El exchange partition no tarda nada y podemos mover una tabla de 10Gb casi instantaneamente, a un esquema particionado, ahora solo quedan los splits.

Aquí es donde empieza el problema, en el funcionamiento del split partition que hace Oracle.

Para poder hacer un split de una tabla de 10 Gb, Oracle lee los 10Gb y realiza inserts en 2 segmentos temporales, que luego se convierten en las particiones definitivas.

Por lo tanto que si tenemos que hacer 10 particiones de 1Gb se hacen 9 Full scan sobre la tabla.

 

Ejemplo:

Primer Split: Lee 10Gb, crea un segmento de 9 y otro de 1Gb

Segundo Split: Lee 9Gb, crea un segmento de 8 y otro de 1Gb.

....

Noveno Split: lee 2Gb, crea 2 segmentos de 1Gb.

 

Al final del procedimiento, Oracle leyó 54Gb y escribió 54Gb.

Por lo tanto, si bien funciona, es de muy mal rendimiento y genera indisponibilidad del recurso mientras se realiza el mantenimiento.

 

Insert as select en una nueva tabla particionada:

Este procedimiento lleva los siguientes pasos:

  1. Crear una nueva tabla idem a la tabla origen pero particionada con todas las particiones.
  2. Impedir el acceso de todos los usuarios a la tabla hasta terminar el procedimiento.
  3. Realizar un insert as select de la tabla origen a destino
  4. Reasignar los indices, constraints, triggers a la nueva tabla
  5. Borrar tabla Origen
  6. Renombrar destino

 

Este procedimiento realiza un único full Scan de la tabla origen y las inserciones debidas dentro de las correspondientes particiones de la tabla destino.

Genera indisponibilidad del recurso para DML (Necesario para que queden iguales), y puede tomar bastante tiempo si no se toman las medidas necesarias como Utilizar el hint de APPEND, la clausula NOLOGGING y las modificaciones de sesion previas a la insersión como

  • alter session force parallel dml parallel degree-of-parallelism;
  • alter session force parallel query parallel degree-of-parallelism;

 

Nota: Tener en cuenta que la clausula nologging puede comprometer el recovery de los datos en caso de hacer un restore a un momento previo.  A continuación les dejo un link a la nota NOLOGGING & APPEND (Direct-path INSERT) , donde explico como impactan en la base estas clausulas y que medidas tener en cuenta.

 

De no ser por la indisponibilidad este sería el método óptimo para el particionado, pero por suerte, desde 9iR2 tenemos la posibilidad de utilizar el package DBMS_REDEFINITION

 

Utilizar el package DBMS_REDEFINITION:

Este package utiliza el método de insert as select para realizar el particionado pero se encarga automáticamente de los pasos de renombrado de tablas y tiene un mecanismo muy similar a los snapshots (con una MLOG Table) que permite que se sigan realizando operaciones DML sobre la tabla mientras se realiza el mantenimiento y al finalizar aplica todos los cambios realizados a la tabla final.

 

Este procedimiento lleva los siguientes pasos:

  1. Crear una nueva tabla idem a la tabla origen pero particionada con todas las particiones.
  2. Verificar si la tabla puede ser redefinida con este package (procedure DBMS_REDEFINITION.CAN_REDEF_TABLE)
  3. Mover los datos de Origen a destino (procedure DBMS_REDEFINITION.START_REDEF_TABLE)
  4. Sincronizar tabla y crear indices y constraints en tabla destino (procedure DBMS_REDEFINITION.SYNC_INTERIM_TABLE + creaciones...)
  5. Cambiar nombre de tablas y aplicar cambios realiados durante mantenimiento (procedure DBMS_REDEFINITION.FINISH_REDEF_TABLE)
  6. Operaciones de limpieza (Borrado de tabla Origen +  renombrado de indices y constraints)

 

Nota: Es conveniente realizar la tarea en parallel, para esto, antes de utilizar el package conviene forzar al parallel la sesion.

  • alter session force parallel dml parallel degree-of-parallelism;
  • alter session force parallel query parallel degree-of-parallelism;

 

La utilización de DBMS_REDEFINITION Siempre genera redo por más que las tablas estén en NOLOGGING.

 

Conclusión:

El mejor método para realizar este procedimiento, tanto en performance, espacio y disponibilidad es utilizar el package DBMS_REDEFINITION.

En breve voy a escribir un procedimiento para explicar paso a paso como realizar esta operación.