Manual Oracle Sql*Plus

Vistas materializadas

El SQL de las bases de datos Oracle permite crear vistas materializadas o materialized views. Estas vistas materializadas, a parte de almacenar la definición de la vista propiamente dicha, también almacenan los registros que resultan de la ejecución de la sentencia SELECT que define la vista. Como las vistas normales, la sentencia SELECT es la base de la vista, pero la sentencia SQL se ejecuta cuando se crea la vista y los resultados se almacenan físicamente constituyendo una tabla real que ocupa sitio en el disco duro. Esta tabla puede definirse utilizando los mismos parámetros de almacenamiento que se pueden utilizar para una tabla normal (tablespace, etcétera). Las vistas materializadas también admiten índices, esta funcionalidad resulta muy útil a la hora de mejorar el rendimiento de las sentencias PLSQL o SQL que utilicen vistas materializadas.

Cuando una sentencia SQL o PL/SQL accede a una vista materializada el servidor de la base de datos Oracle, transforma la sentencia dirigiéndose directamente a los datos de la vista que están ya almacenados, en lugar de utilizar los datos de las diferentes tablas utilizadas en la definición de dicha vista.

Evidentemente, si una vista (view) utiliza muchas tablas base enlazadas de forma compleja, y dicha vista va a ser utilizada frecuentemente, será muy conveniente definirla como una vista materializada o materialized view. Esto contribuirá enormemente a mejorar el rendimiento de la base de datos, ya que la sentencia SQL base de la vista sólo se ejecutará una vez.

Por otro lado, está el inconveniente de que si la vista materializada o materialized view va a tener que reutilizarse en el futuro, entonces necesitaremos un mecanismo para actualizar o refrescar dicha vista materializada, ya que las tablas base de la vista pueden haber sufrido modificaciones desde la creación de la misma.

Por todo esto, a la hora de determinar si una vista debe definirse como vista o es mejor definirla como vista materializada, debemos valorar los costes de tener que ejecutar la sentencia SQL base de una vista normal siempre que se acceda a dicha vista, frente a los costes de almacenamiento y actualización de una vista materializada.

Sintaxis del comando SQL utilizado para crear vistas materializadas

CREATE MATERIALIZED VIEW nombre_vistam

 [TABLESPACE nombre_ts]

 [PARALELL (DEGREE n)]

 [BUILD {INMEDIATE|DEFERRED}]

 [REFRESH {FAST|COMPLETE|FORCE|NEVER|ON COMMIT}]

 [{ENABLE|DISABLE} QUERY REWRITE]

AS SELECT ... FROM ... WHERE ...

Los valores por defecto de las distintas opciones están subrayados.

Si se elige la opción BUILD INMEDIATE, entonces la tabla asociada con la vista materializada se puebla con datos en el momento de la ejecución del comando SQL CREATE. Por el contrario, si se utiliza BUILD DEFERRED, el comando CREATE creará sólo la estructura de la vista, pero la tabla física asociada no se poblará con datos hasta que se realice el primer refresco o actualización de la vista materializada.

La opción REFRESH permite indicar el mecanismo que la base de datos utilizará para refrescar o actualizar la vista materializada. Los diferentes mecanismos y la forma en que una vista materializada o materialized view puede refrescarse, serán objeto de otro artículo en este blog. Como anticipo diré que un refresco completo o COMPLETE, significa que la tabla asociada con la vista materializada se borra completamente, volviéndose a insertar todos los registros devueltos por la ejecución de la sentencia SQL base de la vista, y que un refresco rápido o FAST, significa que la vista materializada se actualiza sólo según hayan sido los cambios realizados sobre las tablas base de la vista desde el último refresco. Para poder utilizar el refresco rápido o FAST, hay que crear previamente los logs de la vista materializada utilizando el comando CREATE MATERIALIZED VIEW LOG.

La opción ENABLE/DISABLE QUERY REWRITE determina si el optimizador Oracle puede o no reescribir las sentencias SQL de manera que, de ser posible, en la fase de ejecución se utilice la vista materializada en lugar de las tablas base de la vista incluidas en la sentencia SQL original. Este es un tema ciertamente complejo y que será objeto de un artículo completo en este blog. Como anticipo indicaré que la reescritura de sentencias SQL sólo está disponible cuando se utiliza el optimizador Oracle basado en costes.

El refresco de las vistas materializadas en SQL y PL/SQL

Ya he hablado en otro artículo acerca del funcionamiento básico de las vistas materializadas (materialized views), en éste voy a exponer los distintos tipos de refresco que se pueden utilizar para actualizar una vista materializada con los cambios provocados por las actualizaciones en las tablas base utilizadas en la misma. El tipo de refresco que debemos elegir dependerá de la frecuencia de actualización de las tablas base y de las necesidades que tengamos de disponer de datos exactos.

Tipos de refresco

COMPLETE: Este tipo de refresco implica el borrado de los datos existentes y la reinserción de todos los datos mediante la reejecución de la consulta SELECT que define la vista materializada.

FAST: El refresco aplica sólo a los cambios realizados sobre las tablas base desde el último refresco. Puede ser de dos tipos

- Utilizando los logs de la vista materializada: En este caso todos los cambios sobre las tablas base se almacenan en dichos logs, aplicándose en el momento del refresco a la vista materializada. Estos logs deben ser creados sobre todas las tablas base de la vista utilizando el comando CREATE MATERIALIZED VIEW LOG, es decir, necesitaremos crear un log por cada tabla base que se utilice en la vista materializada.

- Utilizando rangos ROWID: Este tipo de refresco necesita de otro tipo de logs, los llamados logs de carga directa (direct loader logs).

Conviene puntualizar que no todas las vistas materializadas pueden soportar el refresco FAST. Por ejemplo, el uso de funciones SQL como SUM, AVG, MAX, MIN o COUNT no son admitidas por este tipo de refresco.

 

FORCE: Es la opción por defecto si no se selecciona ningún tipo de refresco a la hora de crear la vista materializada. Este tipo de refresco funciona de la siguiente manera, si es posible la vista se refrescará utilizando el mecanismo FAST, en caso contrario se empleará la opción COMPLETE.

NEVER: Esta opción suprime todos los refrescos de la vista materializada.

Formas de refresco

Refresco manual: Los refrescos manuales de las vistas materializadas se realizan utilizando el paquete PL/SQL estándar DBMS_MVIEW. Este paquete incluye un buen número de funciones y procedimientos PLSQL que permiten gestionar las vistas materializadas. Entre ellos cabe destacar:

DBMS_MVIEW.REFRESH ('nombre_vista_materializada') - Refresca una vista materializada específica.

DBMS_MVIEW.REFRESH_DEPENDENT ('nom_tab1, nom_tab2, ...') - Refresca todas las vistas materializadas que utilicen como tabla base alguna de las tablas o vistas materializadas indicadas en la lista (los nombres de las tablas o vistas materializadas deben separarse mediante comas).

DBMS_MVIEW.REFRESH_ALL_MVIEWS (n) - Refresca todas las vistas materializadas del sistema devolviendo un entero (n) que indica el número de registros que se han refrescado.

Todos estos procedimientos y funciones admiten parámetros adicionales entre los que cabe mencionar: el tipo de refresco (? - force, f - fast, c - complete), el segmento de rollback que se debe usar durante el refresco, si se continúa (true) o no (false) realizando el refresco tras detectar un error, si el refresco de todas las vistas materializadas se realiza en una sola transacción de manera que si falla el refresco de una vista falla el refresco de todas (true) o de si cada vista materializada se refresca en transacciones separadas (false).

Los refrescos manuales requieren que los parámetros del sistema JOB_QUEUE_PROCESSES y JOB_QUEUE_INTERVAL estén configurados para permitir la ejecución de trabajos encolados.

Refresco automático: Esta forma de refresco puede realizarse de dos formas:

ON COMMIT: La vista materializada se refresca cada vez que se ejecuta un COMMIT sobre alguna de las tablas base de la vista. Esto significa que la ejecución del COMMIT tomará más tiempo lo que puede afectar seriamente al rendimiento. Yo personalmente no recomiendo este tipo de refresco.

Refresco programado: El refresco se programa para que ocurra a una hora o tiempo determinado. Por ejemplo, una vista se podría programar para que se refresque todos los días a una determinada hora mediante el uso de las cláusulas START WITH (seguido de la hora en formato datetime del primer refresco automático) y NEXT (seguido de una expresión en formato datetime que se utilizará para calcular el intervalo entre refrescos automáticos). En este caso también es necesario configurar correctamente el parámetro del sistema JOB_QUEUE_PROCESSES.

Ejemplo:

CREATE MATERIALIZED VIEW nombre_vm...

REFRESH START WITH ROUND(SYSDATE + 1) + 9/24

        NEXT NEXT_DAY(TRUNC(SYSDATE), 'TUESDAY') + 16/24

AS SELECT ...;

Comments