Como las cargas de un Data warehouse se realizan de manera periódica, y además es habitual la creación de tablas agregadas para mejorar la eficiencia y tiempo de respuesta de nuestros informes, un recurso de optimización física que puede aportar grandes mejoras es la utilización de vistas materializadas.
Qué es una vista materializada
La vista materializada no es más que una vista, definida con una sentencia SQL de Oracle, de la que además de almacenar su definición, se almacenan los datos que retorna, realizando una carga inicial y después cada cierto tiempo un refresco de los mismos.
Así, si tenemos un Datawarehouse que se actualiza diariamente, podríamos utilizar vistas materializadas para ir actualizando tablas intermedias que alimenten nuestros esquemas de DWH, o directamente para implementar tablas agregadas que se refrescarán a partir de nuestras tablas base.
La creación de este tipo de vistas con Oracle SQL no es tan compleja como puede parecer, lo más importante es tener claro cada cuánto tiempo queremos actualizar la información de las vistas, y qué método de refresco utilizar.
También tendremos que asegurarnos de que nuestra licencia de base de datos nos permite utilizarlas (ha de ser una versión Enterprise).
Sintaxis básica de Oracle SQL para la creación de una vista materializada
CREATE MATERIALIZED VIEW mi_vista_materializada
[TABLESPACE mi_tablespace]
[BUILD {IMMEDIATE | DEFERRED}]
[REFRESH {ON COMMIT | ON DEMAND | [START WITH fecha_inicio] NEXT fecha_intervalo } |
{COMPLETE | FAST | FORCE} ]
[{ENABLE|DISABLE} QUERY REWRITE] AS
SELECT t1.campo1, t2.campo2
FROM mi_tabla1 t1 , mi_tabla2 t2
WHERE t1.campo_fk = t2.campo_pk AND …
Comentarios sobre las diferentes opciones de la sentencia Oracle SQL de creación de la vista:
-
Carga de datos en la vista
BUILD IMMEDIATE:
Los datos de la vista se cargan en el mismo momento de la creación
BUILD DEFERRED:
Sólo se crea la definición, los datos se cargarán más adelante. Para realizar esta carga se puede utilizar la función REFRESH del package DBMS_MVIEW:
begin
dbms_mview.refresh('mi_vista_materializada');
end;
-
Método y temporalidad del refresco de los datos
Cada cuánto tiempo se refrescarán:
REFRESH ON COMMIT:
Cada vez que se haga un commit en los objetos origin definidos en la select
REFRESH ON DEMAND:
Como con la opción DEFERRED del BUILD, se utilizarán los procedures REFRESH, REFRESH_ALL_MVIEWS o REFRESH_DEPENDENT del package DBMS_MVIEW
REFRESH [START WITH fecha_inicio] NEXT fecha_intervalo:
START WITH indica la fecha del primer refresco (fecha_inicio suele ser un SYSDATE)
NEXT indica cada cuánto tiempo se actualizará (fecha_intervalo podría ser SYSDATE +1 para realizar el refresco una vez al día)
-
De qué manera se refrescarán
REFRESH COMPLETE:
El refresco se hará de todos los datos de la vista materializada, la recreará completamente cada vez que se lance el refresco
REFRESH FAST:
El refresco será incremental, es la opción más recomendable, lo de fast ya da una idea del porqué.
Este tipo de refresco tiene bastantes restricciones según el tipo de vista que se esté creando.
Se pueden consultar en General Restrictions on Fast Refresh de la documentación oficial de Oracle
Una de las cosas importantes a tener en cuenta es que para poder utilizar este método casi siempre es necesario haber creado antes un LOG de la Vista materializada, indicando los campos clave en los que se basará el mantenimiento de la vista.
Se utiliza la instrucción de Oracle SQL "CREATE MATERIALIZED VIEW LOG ON":
CREATE MATERIALIZED VIEW LOG ON mi_tabla_origen WITH PRIMARY KEY INCLUDING NEW VALUES;
REFRESH FORCE:
Con esta opción se indica que si es posible se utilice el metodo FAST, y si no el COMPLETE.
Para saber si una vista materializada puede utilizar el método FAST, el package DBMS_MVIEW proporciona el procedure EXPLAIN_MVIEW
-
Activación de la reescritura de consultas para optimizar el Data warehouse
ENABLE QUERY REWRITE:
Se permite a la base de datos la reescritura de consultas
DISABLE QUERY REWRITE:
Se desactiva la reescritura de consultas
La opción QUERY REWRITE es la que más vamos a utilizar si queremos las vistas materializadas para optimizar nuestro Data warehouse.
Esta opción permite crear tablas agregadas en forma de vistas materializadas, y que cuando se lance una SELECT la base de datos pueda reescribirla para consultar la tabla o vista que vaya a devolver los datos solicitados en menos tiempo, todo de manera totalmente transparente al usuario
Lo único que hay que hacer es crear las tablas agregadas como vistas materializadas con QUERY REWRITE habilitado.
Ejemplos de vistas materializadas de Oracle
Son muchas combinaciones, pero la sentencia final no es tan compleja.
Primer paso de la ETL de un Data warehouse
Si quisiéramos crear con SQL de Oracle una vista materializada de una tabla que se refresque un día a la semana, y de manera incremental haríamos lo siguiente:
CREATE MATERIALIZED VIEW LOG ON mi_tabla_origen WITH PRIMARY KEY INCLUDING NEW VALUES; CREATE MATERIALIZED VIEW mi_vista_materializada REFRESH FAST NEXT SYSDATE + 7 AS SELECT campo1, campo2, campo8 FROM mi_tabla_origen WHERE campo2 > 5000;
Esta vista podría servirnos para alimentar la carga de un Data Mart que se realizara semanalmente. Podríamos programarla para que se refrescara justo antes del inicio del proceso de carga, o como primer paso en la ETL, y ya tendríamos los datos necesarios actualizados, e independientes del origen de datos (no tendríamos que molestar más al operacional). Otra ventaja a tener en cuenta es que si hay algún problema con el acceso a los datos de origen, si no los hemos eliminado, en la vista materializada aún tendremos los datos del último refresco, con lo que aunque el refresco fallara no nos encontraríamos un error que truncara la carga de nuestro Data Warehouse, o una tabla vacía.
Por supuesto, en las condiciones del WHERE de la sentencia SQL de creación podríamos seleccionar sólo los registros necesarios, sólo los del último mes, etc.
Tablas agregadas para optimizar el Data Warehouse
Otro ejemplo importante sería la utilización de vistas materializadas para la creación de tablas agregadas.
-- Oracle SQL para crear agregadas con materilized views CREATE MATERIALIZED VIEW ventas_agregadas_mv BUILD IMMEDIATE REFRESH COMPLETE ENABLE QUERY REWRITE AS SELECT id_producto, sum(importe) total_ventas FROM ventas GROUP BY id_producto;
Con esta sencilla sentencia de Oracle SQL se crearía una tabla agregada de total de ventas por producto de una supuesta tabla de ventas que seria la tabla de hechos.
A nivel de sesión también habría que asegurarse de que la opción QUERY_REWRITE estuviera activada. Por si acaso, desde SQL Plus, se puede habilitar con la sentencia SQL:
SQL> ALTER SESSION SET QUERY_REWRITE_ENABLED=TRUE;
Si ahora dentro de esta sesión se ejecuta la sentencia SQL
SQL> SELECT sum(importe)
FROM ventas;la base de datos preparará el plan de ejecución teniendo en cuenta la vista materializada creada e internamente realizará la selección sobre la vista ventas_agregadas_mv.
Una manera sencilla de comprobarlo, aparte de examinar el plan de ejecución, o de comparar tiempos antes y después de la creación de la vista, o desactivando el QUERY_REWRITE, es comprobar que esta query SQL devuelve resultados en el mismo tiempo que la query
SELECT sum(importe) FROM ventas_agregadas_mv;
Para consultar más detalles, o la sintaxis SQL completa de la creación de vistas materializadas Oracle, el capítulo Create Materialized View del manual de referencia SQL de Oracle es un buen recurso.
Con Oracle Enterprise Manager (OEM), o con la consola web de la base de datos también se pueden crear las vistas materializadas de una manera más asistida, pero igualmente es importante tener claros los conceptos antes de hacerlo.
Libros de Administración Oracle (DBA) y PL/SQL
¿Quieres profundizar más en PL/SQL de Oracle o en administración de bases de datos Oracle? Puedes hacerlo consultando alguno de estos libros de Oracle.
Los libros que ves a continuación son una selección de los que a mi me parecen más interesantes para aprender administración y desarrollo PL/SQL, teniendo en cuenta precio y temática, espero que te puedan ser de utilidad:
- eBooks de Oracle gratuítos para la versión Kindle, o muy baratos (menos de 4€):
