Aprendiendo a usar "online table redefinition"

Introducción

En cualquier base de datos, ocasionalmente puede ser necesario modificar la estructura lógica de las para mejorar el rendimiento de las consultas o DML, acomodar los cambios de la aplicación, administrar almacenamiento, etc. Oracle proporciona un nuevo feature llamado online table redefinition para realizar modificaciones en la estructura de la tabla sin afectar significativamente su disponibilidad. Este feature está disponible desde la versión 9i, pero con la versión 10g su uso se vuelve más facil para redefinir tablas online. Cuando una tabla es redefinida en línea, es accesible tanto para consultas como para DML durante gran parte del proceso de redefinición. Esta tabla redefinida requiere de un espacio aproximadamente equivalente al espacio utilizado por la tabla que se está redefiniendo. Sin embargo, se va a requerir más espacio si se agregan más columnas. La redefinición de tablas es posibles usando el  paquete DBMS_REDEFINITION. 

¿Cuáles son los métodos de redefinición?

Los métodos son 2:

• By key: Se puede usar la primary key o una pseudo-primary key para la redefinición. Ambas versiones de la tabla, antes y después de la redefinición, deben tener el mismo primary key. Este es el método de definición más usado y el que se usará en la implementación de este proyecto. Para usar este método se usa: dbms_redefinition.cons_use_pk
• By rowid: Se usa este método cuando no hay ninguna key disponible. Usando este método, una columna escondida conocidad como M_ROW$$ es añadida a la tabla post-redefinición. Se recomienda que esta columna sea eliminada o marcada como “unused” luego de que la redefinición se terminó dbms_redefinition.cons_use_rowid

Usando dbms_redefinition.cons_use_pk o dbms_redefinition.cons_use_rowid podremos saber si la tabla puede ser redefinida.

¿Qué no me permite hacer la redefinición de tablas online (online table redefinition)?

No se puede redefinir cuando: 

• La tabla a redefinir es parte de un clúster
• Las tablas pertenece a los esquemas SYS y SYSTEM
• Las tablas es de tipo temporal
• La tabla está organizada por índices
• La tabla usa tipos de usuario definidos (“user-defined types”) como los objetos, REF, colecciones, etc. 

Nota. Un cluster un objeto del schema que contiene data de una o más tablas, las cuales tienen una o más columnas en comun. Oracle BD almacena juntas todas las filas de todas las tablas que compartan el mismo cluster key.

Implementación 

Para este ejemplo, usaremos la primary key como el método de redefinición.

1. Se creó el usuario ULIMA2, se creó la tabla “empleado” y se insertó 4 filas

create user ulima2 identified by oracle
grant dba to ulima2;;        

Como se puede apreciar la tabla empleado  tiene el campo “empno” como primary key.

CREATE TABLE ulima2.emplead
        (empno      NUMBER(5) PRIMARY KEY,
         ename      VARCHAR2(20) NOT NULL,
         job        VARCHAR2(20),
         deptno     NUMBER(3) NOT NULL);


insert into ulima2.empleado values(1,'Pepe','Contador',1);
insert into ulima2.empleado values(2,'Kory','Ingeniero',2);
insert into ulima2.empleado values(3,'Raul','Administrativo',3);
insert into ulima2.empleado values(4,'Pablo','Secretario',3);

commit;o        

2. Se comprueba que los valores fueron insertados correctamente

select * from ulima2.empleado;        

3. Se comprueba que la tabla se pueda redefinir

BEGIN
  DBMS_REDEFINITION.CAN_REDEF_TABLE('ulima2','empleado',
      DBMS_REDEFINITION.CONS_USE_PK);
END;
/        

Recuerda que como estamos usando el método con primary key se usa dbms_redefinition.cons_use_pk

4. Se crea una segunda tabla “empleados_redef” la cual recibirá los datos de la tabla “empleados”. Esta tabla será provisonal y será usado para hacer todas las modificaciones.

CREATE TABLE ulima2.empleados_rede
        (empno      NUMBER(10) PRIMARY KEY,
         ename      VARCHAR2(20) NOT NULL,
         job        VARCHAR2(20),
         deptno     NUMBER(3) NOT NULL);
        

La estructura de esta tabla provisional puede ser diferentes a la de la orignal. Es posible adicionar, modificar y eliminar columnas. En este caso, se ha modificado la columna “empno”, incrementando su valor a 10 de 5.

5. Como se puede comprobar, la tabla “empleados_redef” está vacía

select * from ulima2.empleados_redef;        

6. Se comprueba si esta tabla provisional se puede particionar

BEGI
  DBMS_REDEFINITION.CAN_REDEF_TABLE('ulima2','empleados_redef',
      DBMS_REDEFINITION.CONS_USE_PK);
END;
/N        

7. Se empieza con la partición de tabla usando el procedure start_redef_table. Se agrega los parámetros tales como nombre de la tabla a ser redefinida, nombre de la tabla provisional, el mapeo de columnas y el método de redefinición. Opcionalmente, se puede especificar las columnas que se usarán para ordenar las filas usando orderby_cols.

BEGI
   DBMS_REDEFINITION.START_REDEF_TABLE('ulima2', 'empleado','empleados_redef',
  'empno empno, ename ename, job job, deptno deptno', dbms_redefinition.cons_use_pk);
END ;
/        

8. Se copia todos los objetos dependientes automáticamente usando el procedure copy_table_dependents. Estos objetos dependientes pueden ser triggers, indexes o privlegios de la tabla provisional

DECLAR
num_errors PLS_INTEGER;
BEGIN
  DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('ulima2', 'empleado','empleados_redef',
   DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, num_errors);
END;
/        

9. Se termina la redefinición usando el procedure finish_redef_table

exec DBMS_REDEFINITION.FINISH_REDEF_TABLE('ulima2', 'empleado','empleados_redef');        

10. Se comprueba que se copiaron los datos a la tabla provisonal “empleados_redef”

select * from ulima2.empleados_redef;        

11. Se hace drop a la tabla provisional ulima2.empleados_redef luego de asegurarse que se hicieron los cambios necesarios en la tabla ulima2.empleado. Se puede verificar estos cambios usando desc ulima2.empleado. Se va a visualizar el cambio en la columna empno

drop table ulima2.empleados_redef;
desc ulima2.empleado;        

Conclusiones

En conclusión, usar online redefine table nos puede ayudar a realizar cambios online en una tabla provsional sin dejar que la tabla esté disponible para el resto de usuarios. Esta característica nos permite ahorrar tiempo a comparación de antes que los cambios eran hechos de manera offline. Además, nos permite mejorar la performance de las queries o DML statements, probar cambios y/o minimizar errores. Todo ello con la finalidad de mejorar la administración y minimizar las tareas manuales mientra usamos la base de datos.