Tablas de staging (I) - Introducción

Una tabla de staging consiste en una tabla intermedia en una materialized query table(MQT), donde se almacenan los cambios que se han realizado en la tabla origen. Con esta tabla intermedia se consigue poder hacer refrescos de MQT incrementales, de forma que sólo se refrescan los datos nuevos o que hayan sido modificados.

La ventaja que aporta sobre las MQT gestionadas por el sistema es que permite realizar operaciones en la MQT sin producir bloqueos sobre la tabla origen, y sobre las tablas gestionadas por usuario que se puede hacer el refresco de únicamente los datos modificados.

Esto es ideal para logs e historificaciones, ya que permite hacer consultas sobre los datos sin afectar al proceso de escritura.

Este artículo se dividirá en tres partes:

Esta primera parte donde explico el funcionamiento básico de las tablas de staging, su creación y su operativa ; una segunda parte donde se analiza el impacto que tiene sobre la escritura con respecto a una tabla convencional ; y una parte final donde se analiza el impacto del refresco, tanto en la tabla origen como en la destino.


CREACIÓN DE TABLAS DE STAGING

Supongamos una tabla T1, que se quiere historificar en la tabla T1_HIS.

CREATE TABLE T1(
FECHA TIMESTAMP NOT NULL PRIMARY KEY,
DATO1 INTEGER,
DATO2 INTEGER
);

La tabla de histórico se creará de la siguiente forma:

CREATE TABLE T1_HIS AS (SELECT * FROM T1) DATA INITIALLY DEFERRED REFRESH DEFERRED;

De esta forma se historificarán todos los registros de la tabla T1.

Ahora se crearía la tabla de staging:

CREATE TABLE T1_STG FOR T1_HIS PROPAGATE IMMEDIATE;

SET INTEGRITY FOR T1_STG STAGING IMMEDIATE UNCKECHED;

Y se refrescarían los datos:

REFRESH TABLE T1_HIS NOT INCREMENTAL;

Desde este momento los datos quedarían sincronizados, y cualquier cambio en T1 quedaría registrado en T1_STG.

Si insertamos un registro en T1:

INSERT INTO T1 VALUES(CURRENT TIMESTAMP, 100,10000);

Podemos ver como queda guardado en T1_STG:

SELECT * FROM T1_STG;


FECHA DATO1 DATO2 GLOBALTRANSID GLOBALTRANSTIME OPERATIONTYPE
-------------------------- ----------- ----------- ----------------------------- --------------------------------- -------------
2010-11-23-09.47.19.742507 100 10000 x'00000000002015B9' x'20101123084719742595000000' 1

1 record(s) selected.


Ahora podemos hacer un refresco incremental:

REFRESH TABLE T1_HIS INCREMENTAL ALLOW WRITE ACCESS;

El comando REFRESH TABLE permite realizar el refresco permitiendo accesos en la MQT de escritura, de lectura o no permitiendo accesos.

Una vez ejecutado podemos ver que la tabla T1_STG está vacía, y que la tabla T1_HIS contiene el registro que se insertó en T1.



CONSIDERACIONES A TENER EN CUENTA

Haciendo pruebas he podido comprobar que para la actualización de la tabla MQT se realizan búsquedas por la clave primaria de la tabla origen, por lo que es importante añadirle a posteriori la misma clave primaria que a T1, ya que esta no se crea de forma automática. Las tablas MQT no permiten índices únicos, por lo que el índice debe ser regular.

CREATE INDEX T1_HIS_IDX ON T1_HIS(FECHA);

Sin este índice, el rendimiento se degrada considerablemente a medida que las tablas crecen.



OPERACIONES SOBRE TABLAS

Con el procedimiento arriba indicado se consigue mantener una copia de la tabla sobre la que se pueden hacer operaciones mientras en la tabla original se insertan registros, pero llegará un momento que se deberán vaciar las tablas, bien porque sólo se mantiene un determinado periodo de retención para estas tablas, o porque los datos ya se han historificado desde la MQT.

Para esto se pueden vaciar las tablas origen y destino con un LOAD en vacío normal:

LOAD FROM /dev/null OF DEL REPLACE INTO T1 NONRECOVERABLE;
LOAD FROM /dev/null OF DEL REPLACE INTO T1_HIS NONRECOVERABLE;

Para vaciar la tabla de stating se hará con:

SET INTEGRITY FOR T1_STG PRUNE;

Después de esto las tablas quedarán en CHECK PENDING STATE. Para volver a su estado normal:

SET INTEGRITY ON T1 IMMEDIATE CHECKED;
SET INTEGRITY ON T1_HIS MATERIALIZED QUERY IMMEDIATE UNCHECKED;
SET INTEGRITY ON T1_STG STAGING IMMEDIATE UNCHECKED;

De esta forma las tablas volverán a estar operativas y vacías. Para sincronizar cualquier posible cambio que haya entrado sincronizamos la tabla completa.

REFRESH TABLE T1_HIS NOT INCREMENTAL;


En el próximo artículo se verá como afecta el uso de tablas staging a la inserción en la tabla origen, ya que cada operación realizada efectuará una inserción en la tabla de staging.