Real-Time Refresh(Vistas Materializadas) en Oracle

Por Renato Coronado

INTRODUCCIÓN

Las vistas materializadas en Oracle nos permiten mejorar el rendimiento de consultas complejas almacenando las filas producidas por la consulta en el segmento de la tabla. De esa manera, muchas consultas pueden acceder a estos resultados precalculados. Es la mejor manera de ahorrar tiempo en análisis complejos, en uniones y agregaciones usadas para mostrar la data el dashboards y reportes así como el trabajo puede ser hecho una sola vez y después ser usado por múltiples usuarios corriendo las mismas consultas , sobre todo en un ambiente de producción donde los registros de una tabla son muchísimos. Si la vista materializada es up-to-date, y es más rápido hacerlo, Oracle puede reescribir la consulta de modo que obtenga la data para la vista materializada, incluso si la consulta está referenciando la tabla original. Cualquier cambio en la tabla, marca la vista materializada como obsoleta y el registro automático no puede pasar por default. Nosotros podemos aumentar la velocidad de actualización de la vista materializada usando materialized view logs para ubicar los cambios, de este modo, solo los cambios son aplicados durante el refresh. En otras palabras, una vista materializada es un segmento de una tabla cuyo contenido es periódicamente actualizado basado en una consulta.

La complicación proviene del retraso entre la última actualización de la vista materializada y los cambios posteriores de DML en las tablas base. Las actualizaciones rápidas le permiten ejecutar actualizaciones con mayor frecuencia y, en algunos casos, puede utilizar las actualizaciones activadas al confirmar los cambios en las tablas base, pero esto puede representar una sobrecarga significativa desde una perspectiva de rendimiento de DML.

Antes nuestras opciones eran confiar en la data obsoleta o volver a consultar toda la tabla directamente cuando la vista materializada fue obsoleta.

Actualmente, hay otra opción: La sesión actual puede utilizar una combinación de los datos obsoletos de la vista materializada y el contenido de los materialized logs para así poder actualizar la data, esto es llamado Real Time Materialized View.

A continuación, se mostrará una gráfico mostrando cómo funciona Real-Time Refresh:

IMPLEMENTACIÓN

Sintaxis básica:

Las vistas materializadas en Oracle se pueden crear de dos maneras:

Normal:

CREATE MATERIALIZED VIEW nombre-vista

BUILD [IMMEDIATE | DEFERRED]

REFRESH [FAST | COMPLETE | FORCE ]

ON [COMMIT | DEMAND ]

[[ENABLE | DISABLE] QUERY REWRITE]

SELECT ...;

Preconstruida(Pre-Build):

CREATE MATERIALIZED VIEW nombre-vista

ON PREBUILT TABLE

REFRESH [FAST | COMPLETE | FORCE ]

ON [COMMIT | DEMAND ]

[[ENABLE | DISABLE] QUERY REWRITE]

SELECT ...;

Opciones de la cláusula Build:

IMMEDIATE: La vista materializada se llena de immediato.
DEFERRED: La vista materializada se completa en la primera actualización solicitada.

Tipos de refresh:

FAST: Se intenta un refresh rápido. Si los materialized logs no se encuentran en las tablas, falla.
COMPLETE: el segmento de tabla que admite la vista materializada se trunca y se vuelve a llenar por completo utilizando la consulta asociada.
FORCE: se intenta un refresh rápido. Si no es posible, se realiza una actualización completa.

Un refresh puede ser desencadenado(triggered) de dos maneras:

ON COMMIT: el refresh es desencadenado por un cambio en la data confirmado.
ON DEMMAND: el refresh es iniciado mediante una solicitud manual o programada.

La cláusula QUERY REWRITE le indica al optimizador si la vista matrializada debe considerarse para las operaciones de reescritura de consultas.

La cláusula ON PREBUILT TABLE le dice a la base de datos que use un segmento de la tabla ya existente, el cual debe tener el mismo nombre que la vista materializada y admitir la misma estructura de las columnas que consulta.

Escenario propuesto

Para comprender la funcionalidad que nos ofrece Oracle Real-Time Refresh, introduciremos un escenario.

El escenario será implementar una base de datos que nos permita almacenar las líneas de pedido de una empresa comercializadora de productos, en la cual los analistas de mercado se encuentra constantemente consultando la tabla lineas_de_pedido, que contiene todas las líneas de pedido de la empresa, así como también se encuentran haciendo reportes de forma periódica. Así mismo, se tiene interés en saber cuántos ordenan el producto 1, por lo cual se hizo un apartado específico para orden 1, dado que es el producto estrella de la empresa y siempre está en constante mejora con la finalidad de satisfacer cada vez más a sus clientes.

La empresa, a lo largo de estos años, se hizo reconocida a nivel nacional y almacena dentro de sí miles de registros, lo cual el hacer consultas constantemente será un requerimiento un poco complicado, dado que son muchísimos registros y es aquí donde Real-Time Refresh podría ayudarnos a solucionar este problema: el trabajo de una consulta puede ser hecho una sola vez y después ser usado por múltiples usuarios corriendo las mismas consultas, como fue mencionado en la introducción.

A continuación, se presentará la estructura de la tabla lineas_de_pedido(Se han omitido algunos campos por simplicidad y para mostrar la funcionalidad de manera mucho más entendible):

Creación de usuario:

Primero, crearemos un usuario(No se pueden crear vistas materializados en una tabla que le pertenezca al usuario SYS, sino saldrá el error ORA-12010) el cuál contendrá la tabla y se le creará la vista materializada y sus logs correspondientes. Así mismo le daremos los permisos correspondientes(por motivos de simplicidad del artículo, le daremos el rol de dba):

CREATE USER renato IDENTIFIED BY oracle;

GRANT DBA TO renato;

Nota: Para proceder a hacer el contenido restante del artículo, debemos conectarnos al usuario creado con el siguiente query: connect renato/oracle o sqlplus renato/oracle.

Creación de la tabla:

Ahora, crearemos una tabla de prueba que actuará como una tabla base en este ejemplo:

CREATE TABLE lineas_de_pedido (

id NUMBER(10),

id_orden NUMBER(10),

cantidad_linea NUMBER(5),

total NUMBER(10,2),

fecha_creacion DATE,

CONSTRAINT orders_pk PRIMARY KEY (id)

);

Inserción de datos: Para que se pueda ver la utilizad de una vista materializada, vamos a llenar la tabla con 100000 filas de datos aleatorios. Nota: no se debería hacer en un ambiente de producción, se hizo solo con motivos de mostrar la funcionalidad. Insertamos la data con una mayor facilidad utilizando APPEND_VALUES Hint, un utilitario de Oracle que nos permite insertar data con mayor performance.

INSERT /*+ APPEND */ INTO lineas_de_pedido SELECT level AS id, TRUNC(DBMS_RANDOM.value(1,1000)) AS id_orden, TRUNC(DBMS_RANDOM.value(1,20)) AS cantidad_linea, ROUND(DBMS_RANDOM.value(1,1000),2) AS total, TRUNC(SYSDATE - DBMS_RANDOM.value(0,366)) AS fecha_creacion FROM dual CONNECT BY level <= 100000; COMMIT;

Ejecutamos el siguiente query, motivo para que Oracle pueda recopilar estadísticas de la tabla:

EXEC DBMS_STATS.gather_table_stats(USER, 'lineas_de_pedido');

Creando los logs de la Vista Materializada(Materialized View Logs): Dado que un refresh implica truncar el segmento de la vista materializada y volver a llenarlo mediante la consulta relacionada, trae consigo bastante tiempo así como procesamiento, sobre todo cuando se realiza una consulta a una tabla remota. Con el motivo de reducir los costos de replicación, crearemos los logs de la vista materializada para capturar todos los cambios en la tabla desde el último refresh. Esto nos permite una actualización rápida, en donde solo necesitaremos aplicar los cambios en lugar de actualizar por completo la vista materializada. Mediante el siquiente query crearemos el Materialized view logs para poder hacer el tracking de cualquier cambio DML en la tabla base:

CREATE MATERIALIZED VIEW LOG ON lineas_de_pedido WITH ROWID, SEQUENCE(id_orden, cantidad_linea, total) INCLUDING NEW VALUES;

Creando la Vista Materializada(Materialized View): Creamos la vista materializada, la cual hace algunas agregaciones como un parte del group by. Al poner ENABLE ON QUERY COMPUTATION le decimos a Oracle que esta es una real-time materialized view.

CREATE MATERIALIZED VIEW resumen_linea_rtmv REFRESH FAST ON DEMAND ENABLE QUERY REWRITE ENABLE ON QUERY COMPUTATION AS SELECT id_orden, SUM(cantidad_linea) AS sum_cantidades_lineas, SUM(total) AS sum_totales, COUNT(*) AS cantidad_filas FROM lineas_de_pedido GROUP BY id_orden;

Ejecutamos el siguiente query para que Oracle pueda recopilar estadísticas de la vista materializada creada:

EXEC DBMS_STATS.gather_table_stats(USER, 'resumen_linea_rtmv');

Reescritura básica:

Este query es bastante similar al usado por la vista materializada creada, está accediendo a la tabla base y está limitando la data solo al registro cuyo id_orden sea 1. Así mismo, podemos ver que retorna un 92 como cantidad de filas(CANTIDAD_FILAS).

Nota: Si ejecutamos una consulta que podría ser atendida rápidamente por la vista materializada, Oracle reescribirá la consulta para así poder usar la vista materializada, como bien se mencionó en la introducción. Ejecutaremos el siguiente query:

SELECT id_orden,

SUM(cantidad_linea) AS sum_cantidades_lineas,

SUM(total) AS sum_totales,

COUNT(*) AS cantidad_filas

FROM lineas_de_pedido

WHERE id_orden = 1

GROUP BY id_orden;

Ahora, ejecutaremos esta consulta para poder ver el plan de ejecución:

SET LINESIZE 200 PAGESIZE 100

SELECT *

FROM dbms_xplan.display_cursor();

De manera más clara:

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

SQL_ID 3pyvm3rg8xaz8, child number 0

-------------------------------------

SELECT id_orden, SUM(cantidad_linea) AS sum_cantidades_lineas,

SUM(total) AS sum_totales, COUNT(*) AS cantidad_filas FROM

lineas_de_pedido WHERE id_orden = 1 GROUP BY id_orden

Plan hash value: 784120407

---------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | 4 (100)| |

|* 1 | MAT_VIEW REWRITE ACCESS FULL| RESUMEN_LINEA_RTMV | 1 | 18 | 4 (0)| 00:00:01 |

---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("RESUMEN_LINEA_RTMV"."ID_ORDEN"=1)

20 rows selected.

Podemos ver en el plan de ejecución que se utilizó la vista materializada, en lugar de acceder a la tabla base. Observamos el valor de cantidad_filas de 92. A su vez, podemos darnos cuenta que Oracle hizo una reescritura del query para poder usar la vista materializada(MAT_VIEW REWRITE ACCESS FULL)

Reescritura Básica + Real-Time Refresh: Ahora procederemos a modificar los datos en la tabla, de manera que la data de la vista materializada creada pasaría a estar obsoleta. Insertamos un registro en la tabla lineas_de_pedido ejecutando el siguiente query:

INSERT INTO lineas_de_pedido VALUES (100001, 1, 50, 18000, SYSDATE);

COMMIT;

Ahora procedemos a ejecutar la siguientes consultas para poder verificar si la vista materializada está considerada como obsoleta.

COLUMN mview_name FORMAT A30

SELECT mview_name,

staleness,

on_query_computation

FROM user_mviews;

Nota: Al ver el estado de la vista materializada nos damos cuenta que quedó obsoleta(Después de NEEDS_COMPILE aparece un “Y”|, refiriéndose a “Yes”).

Una vista materializada regular ya no se consideraría para las reescrituras de consultas a menos que tengamos el parámetro QUERY_REWRITE_INTEGRITY establecido en STALE_TOLERATED para la sesión. Pero desde que tenemos la opción ENABLE ON QUERY COMPUTATION en la vista materializada, todavía se considera utilizable, ya que Oracle va a modificar dinámicamente los valores para reflejar los cambios en los registros de la vista materializada: esta es la esencia de Real-Time Refresh en Oracle.

Ahora volvemos a ejecutar la consulta, para verificar que el registro se insertó correctamente así como poder ver en el plan de ejecución cómo se actualizó la vista materializada mediante los logs:

SELECT id_orden,

SUM(cantidad_linea) AS sum_cantidades_lineas,

SUM(total) AS sum_totales,

COUNT(*) AS cantidad_filas

FROM lineas_de_pedido

WHERE id_orden = 1

GROUP BY id_orden;

Como podemos notar, cantidad_filas ha aumentado en 1.

Ahora, procedemos a revisar el plan de ejecución mediante el query:

SET LINESIZE 200 PAGESIZE 100

SELECT *

FROM dbms_xplan.display_cursor();

De manera más clara:

PLAN_TABLE_OUTPUT

SQL_ID 3pyvm3rg8xaz8, child number 1

-------------------------------------

SELECT id_orden, SUM(cantidad_linea) AS sum_cantidades_lineas,

SUM(total) AS sum_totales, COUNT(*) AS cantidad_filas FROM

lineas_de_pedido WHERE id_orden = 1 GROUP BY id_orden

Plan hash value: 1973604023

------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | 19 (100)| |

| 1 | VIEW | | 3 | 156 | 19 (27)| 00:00:01 |

| 2 | UNION-ALL | | | | | |

|* 3 | FILTER | | | | | |

|* 4 | HASH JOIN OUTER | | 1 | 38 | 9 (23)| 00:00:01 |

|* 5 | MAT_VIEW ACCESS FULL | RESUMEN_LINEA_RTMV | 1 | 22 | 4 (0)| 00:00:01 |

| 6 | VIEW | | 1 | 16 | 5 (40)| 00:00:01 |

| 7 | HASH GROUP BY | | 1 | 39 | 5 (40)| 00:00:01 |

| 8 | VIEW | | 1 | 39 | 4 (25)| 00:00:01 |

|* 10 | VIEW | | 1 | 103 | 4 (25)| 00:00:01 |

| 11 | WINDOW SORT | | 1 | 194 | 4 (25)| 00:00:01 |

|* 12 | TABLE ACCESS FULL | MLOG$_LINEAS_DE_PEDIDO | 1 | 194 | 3 (0)| 00:00:01 |

| 13 | VIEW | | 2 | 104 | 10 (30)| 00:00:01 |

| 14 | UNION-ALL | | | | | |

|* 15 | FILTER | | | | | |

| 16 | NESTED LOOPS OUTER | | 1 | 95 | 4 (25)| 00:00:01 |

| 17 | VIEW | | 1 | 78 | 4 (25)| 00:00:01 |

|* 18 | FILTER | | | | | |

| 19 | HASH GROUP BY | | 1 | 39 | 4 (25)| 00:00:01 |

|* 20 | VIEW | | 1 | 39 | 4 (25)| 00:00:01 |

|* 22 | VIEW | | 1 | 103 | 4 (25)| 00:00:01 |

| 23 | WINDOW SORT | | 1 | 194 | 4 (25)| 00:00:01 |

|* 24 | TABLE ACCESS FULL | MLOG$_LINEAS_DE_PEDIDO | 1 | 194 | 3 (0)| 00:00:01 |

|* 25 | INDEX UNIQUE SCAN | I_SNAP$_RESUMEN_LINEA_RTMV | 1 | 17 | 0 (0)| |

| 26 | NESTED LOOPS | | 1 | 103 | 6 (34)| 00:00:01 |

| 27 | VIEW | | 1 | 81 | 5 (40)| 00:00:01 |

| 28 | HASH GROUP BY | | 1 | 39 | 5 (40)| 00:00:01 |

| 29 | VIEW | | 1 | 39 | 4 (25)| 00:00:01 |

|* 31 | VIEW | | 1 | 103 | 4 (25)| 00:00:01 |

| 32 | WINDOW SORT | | 1 | 194 | 4 (25)| 00:00:01 |

|* 33 | TABLE ACCESS FULL | MLOG$_LINEAS_DE_PEDIDO | 1 | 194 | 3 (0)| 00:00:01 |

|* 34 | MAT_VIEW ACCESS BY INDEX ROWID| RESUMEN_LINEA_RTMV | 1 | 22 | 1 (0)| 00:00:01 |

|* 35 | INDEX UNIQUE SCAN | I_SNAP$_RESUMEN_LINEA_RTMV | 1 | | 0 (0)| |

------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

3 - filter("AV$0"."OJ_MARK" IS NULL)

4 - access(SYS_OP_MAP_NONNULL("ID_ORDEN")=SYS_OP_MAP_NONNULL("AV$0"."GB0"))

5 - filter("RESUMEN_LINEA_RTMV"."ID_ORDEN"=1)

10 - filter((("MAS$"."OLD_NEW$$"='N' AND "MAS$"."SEQ$$"="MAS$"."MAXSEQ$$") OR

(INTERNAL_FUNCTION("MAS$"."OLD_NEW$$") AND "MAS$"."SEQ$$"="MAS$"."MINSEQ$$")))

12 - filter("MAS$"."SNAPTIME$$">TO_DATE(' 2020-07-07 16:47:48', 'syyyy-mm-dd hh24:mi:ss'))

15 - filter(CASE WHEN ROWID IS NOT NULL THEN 1 ELSE NULL END IS NULL)

18 - filter(SUM(1)>0)

20 - filter("MAS$"."ID_ORDEN"=1)

22 - filter((("MAS$"."OLD_NEW$$"='N' AND "MAS$"."SEQ$$"="MAS$"."MAXSEQ$$") OR

(INTERNAL_FUNCTION("MAS$"."OLD_NEW$$") AND "MAS$"."SEQ$$"="MAS$"."MINSEQ$$")))

24 - filter("MAS$"."SNAPTIME$$">TO_DATE(' 2020-07-07 16:47:48', 'syyyy-mm-dd hh24:mi:ss'))

25 - access("RESUMEN_LINEA_RTMV"."SYS_NC00005$"=SYS_OP_MAP_NONNULL("AV$0"."GB0"))

31 - filter((("MAS$"."OLD_NEW$$"='N' AND "MAS$"."SEQ$$"="MAS$"."MAXSEQ$$") OR

(INTERNAL_FUNCTION("MAS$"."OLD_NEW$$") AND "MAS$"."SEQ$$"="MAS$"."MINSEQ$$")))

33 - filter("MAS$"."SNAPTIME$$">TO_DATE(' 2020-07-07 16:47:48', 'syyyy-mm-dd hh24:mi:ss'))

34 - filter(("RESUMEN_LINEA_RTMV"."ID_ORDEN"=1 AND "RESUMEN_LINEA_RTMV"."CANTIDAD_FILAS"+"AV$0"."D0">0))

35 - access("RESUMEN_LINEA_RTMV"."SYS_NC00005$"=SYS_OP_MAP_NONNULL("AV$0"."GB0"))

Result Cache Information (identified by operation id):

------------------------------------------------------

9 -

21 -

30 -

Note

-----

- dynamic statistics used: dynamic sampling (level=2)

82 rows selected.

Podemos ver que el plan de ejecución incluye trabajo adicional para completar el avance.

Así mismo, también podemos darnos cuenta que Oracle todavía está usando la vista materializada( MAT_VIEW ACCESS FULL | RESUMEN_LINEA_RTMV), como también está utilizando los logs de la vista materializada(TABLE ACCESS FULL | MLOG$_LINEAS_DE_PEDIDO ). De esta manera, se está consultando a los logs y se aplican solamente los cambios hechos.

Consulta directa a la vista materializada

Además de reescribir las consultas, también se puede consultar directamente a la vista materializada de forma directa. Al hacer la consulta, obtenemos el contenido actual de la vista materualizada por defecto:

SELECT id_orden,

sum_cantidades_lineas,

sum_totales,

cantidad_filas

FROM resumen_linea_rtmv

WHERE id_orden = 1;

Al hacer la consulta a la misma vista materializada, nos damos cuenta que la data sigue estando como antes de ser modificada. Nota: La vista de la data ha sido nivelada solo en nuestra sesión: La vista materializada no ha sido actualizada todavía. SET LINESIZE 200 PAGESIZE 100 SELECT * FROM dbms_xplan.display_cursor();

Revisando el plan de ejecución, podemos darnos cuenta que se hizo un escaneo regular del segmento donde está almacenada la vista materializada.

Consulta a la vista materializada con FRESH_MV:

Este utilitario le dice a Oracle que queremos aprovechar la funcionalidad en tiempo real cuando hacemos una consulta directa contr la vista materializada, por lo que vemos cantidad_filas aumentado a 93, paar ello, ejecutamos el siquiente query:

SELECT /*+ FRESH_MV */

id_orden,

sum_cantidades_lineas,

sum_totales,

cantidad_filas

FROM resumen_linea_rtmv

WHERE id_orden = 1;

Como podemos ver, podemos obtener la información actualizada consultando directamente la vista materializada mediante FRESH_MV.

SET LINESIZE 200 PAGESIZE 100

SELECT *

FROM dbms_xplan.display_cursor();

PLAN_TABLE_OUTPUT

SQL_ID 87f19u93pg8wq, child number 0

-------------------------------------

SELECT /*+ FRESH_MV */ id_orden, sum_cantidades_lineas,

sum_totales, cantidad_filas FROM resumen_linea_rtmv