Vistas materializadas
En este tutorial vamos a ver lo que son las vistas materializadas
(materialized views) y vamos a hacer un ejemplo de creación
de una vista materializada y su posterior uso. Lo primero de todo es recordar
lo que es una vista en un modelo de base de datos relacional.
Una vista es una consulta almacenada que representa un conjunto
de tablas (posiblemente de diferentes esquemas) a la que le vamos a poner
un nombre y vamos a tratarla como si fuese una tabla más de nuestro
esquema, pero sin llegar a ser realmente una tabla. Algo que tiene que quedar
claro es que una vista NO guarda datos, sino que solo almacena la consulta
que nos va a ayudar a acceder a los datos. Pero, ¿porqué usar
vistas? es muy sencillo, principalmente hay dos motivos. El primer motivo
es de seguridad, a lo mejor no necesitamos que determinados usuarios acceden
a toda la información de la base de datos y simplemente queremos formar
un conjunto de datos específicos para dichos usuarios. El segundo motivo
tiene que ver con la estructura de nuestro modelo de datos, ya que si es bastante
complejo o con muchas tablas nos puede ser muy útil crear este tipo
de vistas para organizar una cierta información de modo que nos sea
mucho más cómodo acceder a ella mediante consultas mucho más
sencillas.
Definición
A diferencia de las vistas «normales» una vista materializada
almacena físicamente los datos resultantes de ejecutar
la consulta definida en la vista. Este tipo de vistas materializadas realizan
una carga inicial de los datos cuando se definen y posteriormente con una
frecuencia establecida se actualizan los datos de la misma.Con la utilización
de vistas materializadas logramos aumentar el rendimiento de las consultas
SQL además de ser un método de optimización
a nivel físico en modelos de datos muy complejos y/o con muchos datos.
Una vez definida una vista materializada uno de los problemas
que nos encontramos es el de la actualización de los datos.
Como se ha comentado antes, estas vistas contienen fisicamente los datos de
las «tablas base», por lo que si cambian los datos de estas tablas
no se reflejarán en la vista materializada. Para ello necesitamos establecer
un mecanismo de resfresco automático en el que tendremos que definir
el tipo y la forma de refresco.
La sentencia SQL que nos permite definir una vista
materializada es esta:
CREATE MATERIALIZED VIEW nombre_vista [TABLESPACE nombre_ts] [PARALELL (DEGREE n)] [BUILD {INMEDIATE|DEFERRED}] [REFRESH {FAST|COMPLETE|FORCE|NEVER}|{ON COMMIT|ON DEMAND|[START WITH fecha_inicio] NEXT intervalo}] [{ENABLE|DISABLE} QUERY REWRITE] AS SELECT ... FROM ... WHERE ...
Con la palabra BUILD establecemos la forma de carga
de datos en la vista. Con la opción INMEDIATE (opción
por defecto) se cargarán los datos justo después de crear la
vista, mientras que con la opción DEFERRED se definirá
la vista cuando se ejecute la sentencia SQL sin cargar ningún
dato, que se cargarán cuando se realize el primer refresco de la vista.
Con la palabra REFRESH definimos el método y
la frecuencia de refresco de los datos.
La palabra QUERY REWRITE establece si queremos que
el optimizador de nuestra base de datos pueda reescribir
las consultas. El optimizador, sabiendo que ya existe una determinada vista
materializada, puede modificar internamente nuestra consulta sobre una determinada
tabla, de tal forma que se mejore el rendimiento de la consulta devolviendo
los mismos datos que la consulta original.
Refresco
Como es entendible la política de refresco de cada vista
repende altamente de nuestras necesidades y requerimientos sobre la frecuencia
de actualización de los datos de las «tablas base».
Tipos de refresco
-
COMPLETE : se borrarán todos los
datos de la vista y se volverá a ejecutar la consulta definida en
la vista por lo que se recargarán fisicamente los datos de las «tablas
base». - FAST : podemos decir que este tipo de refresco es una actualización
incremental, es decir, solo se refrescarán aquellos datos que se hayan
modificado desde el último refresco. Evidentemente este tipo de refresco
es mucho más fast 😉 que el complete. Pero, ¿cómo
sabe la base de datos que datos se han modificado desde el último refresco?
lo sabe gracias a que previamente hemos tenido que crear unos determinados
log de la vista (VIEW LOG) sobre
cada una de las «tablas base» de la vista materializada. -
FORCE : si se puede realizar el refresco
tipo FAST se ejecuta, y sino se realiza el refresco COMPLETE.
Es el valor por defecto del tipo de refresco. - NEVER : nunca se realizará un refresco de la vista.
CREATE MATERIALIZED VIEW LOG ON tabla_base WITH PRIMARY KEY INCLUDING NEW VALUES;
Hay que decir que si usamos funciones sum, avg, max, min, etcétera,
no vamos a poder usar este tipo de refresco.
Formas de refresco
- Refresco manual : mediante el paquete de PL/SQL
DBMS_MVIEW podemos forzar a realizar un refresco usando para ello
la función REFRESH. - Refresco automático : este refresco automático
podemos hacerlo usando la palabra ON COMMIT, con la que se fuerza
al refresco de la vista en el momento en el que se haga un commit
sobre una de las «tablas base» de dicha vista. Otro tipo de refresco
automático es el llamado refresco programado, en el cual podemos definir
el momento exacto en el que queremos que se refresque nuestra vista. Para
ello tenemos que definir la fecha del refresco en formate datetime
y el intervalo de este.
DBMS_MVIEW.REFRESH ('nombre_vista');
Con la función REFRESH_DEPENDENT se refrescarán
todas las vistas materializadas que tengan algunas de sus «tablas base»
en la lista de tablas pasada como parámetro de entrada.
DBMS_MVIEW.REFRESH_DEPENDENT ('tabla1, tabla2, tabla3, ... , tablaN');
Con la función REFRESH_ALL_MVIEWS se refrescarán
todas las vistas materializadas de nuestra base de datos.
Ejemplo práctico
Una vez visto todos los detalles teóricos vamos a hacer un pequeño
ejemplo práctico para ver como definir vistas materializadas y analizar
sus comportamientos.
Lo primero que vamos a hacer es crear una tabla (SQL) en nuestra base
de datos (Oracle 9) y insertar algunos datos.
CREATE TABLE tabla (campo1 int PRIMARY KEY, campo2 int); insert into tabla values (1,2); insert into tabla values (2,298); insert into tabla values (3,223); insert into tabla values (4,121); insert into tabla values (5,34); insert into tabla values (6,34); insert into tabla values (7,78); insert into tabla values (8,44); insert into tabla values (9,34); insert into tabla values (10,12);
A continuación creamos un VIEW LOG para la anterior tabla.
CREATE MATERIALIZED VIEW LOG ON tabla WITH PRIMARY KEY INCLUDING NEW VALUES;
Despúes creamos nuestra vista materializada.
CREATE MATERIALIZED VIEW tabla_vm BUILD IMMEDIATE REFRESH FAST ON COMMIT AS SELECT * FROM tabla;
Como se puede ver, la definición de la vista es absurda, ya que es la
propia tabla, pero para nuestro ejemplo nos servirá ya que lo que se
pretende es ver el comportamiento de la vista materializada creada.
En la anterior sentencia SQL creamos una vista materializada de nombre
tabla_vm que se cargará inicialmente justo cuando se
ejecute (BUILD IMMEDIATE), se refrescarán solo aquellas entradas
que se hallan modificado en la tabla base (FAST) y lo harán
cuando se ejecute la acción de commit (ON COMMIT).
Vamos a ver si realmente la teoría se corresponde con la práctica.
Inicialmente tanto la tabla base como la vista materializada contienen estos
valores:
Cuando ejecutamos una sentencia sobre la tabla base que actualize un determinado
dato de ella (update tabla set campo2 = 123 where campo1 = 1) podemos
ver que se ha actualizado en la tabla base pero no en la vista materializada
ya que aun no se ha lanzado el refresco.
tabla base – tabla
|
vista materializada – tabla_vm
|
Para que la vista se refresque y se actualize solo con el dato modificado en
la tabla base tenemos que hacer COMMIT.
Como se puede ver en la imagen de arriba ya tenemos los datos totalmente actualizados
en nuestra vista materializada.
Este pequeño ejemplo no tiene valor para poder analizar la mejora en
el rendimiento al usar este tipo de vistas pero nos ha servido (…o eso espero…)
para que se vea como se puede crear una vista materializada y como es su funcionamiento.
Para borrar la vista materializada creada tenemos que ejecutar la siguiente
sentencia SQL.
drop materialized view tabla_vm;
Aquí dejo los
scripts SQL utilizados.
Conclusión
Como ya se ha comentado anteriormente la definición de
este tipo de vista solo tiene sentido para modelos de datos muy complejos
y con muchos datos, ya que si no es así no se percibiría apenas
la mejora en el rendimiento y lo que provocaríamos es tener una serie
de datos físicamente en nuestra base de datos sin mucho sentido.
Ahora viene lo mejor….además estás vistas materializadas
no están disponibles para todas las bases de datos del mercado, solo
se pueden usar con base de datos Oracle, DB2, Informix, Microsoft SQL Server
(ellos las llaman vistas indexadas y son muy similares), Adaptative Server
Enterprisa y ANTs Data Server.
Aquí
os dejo el enlace a la documentación oficial de Oracle
en relación a las vistas materializadas (materialized views)
Muy buen tutorial, sí señor 😉
Sólo una cosilla: en el texto
# [BUILD {INMEDIATE|DEFERRED}]
Recordar que en inglés \\\»inmediato\\\» es \\\»IMMEDIATE\\\», con dos \\\»m\\\», en lugar de \\\»nm\\\», como en español. Por tanto, el BUILD debería ser:
BUILD IMMEDIATE
en lugar de:
BUILD INMEDIATE
Es sólo una tontería, pero por si acaso 😉
Muchas gracias por el tutorial ^^
Gracias por el aporte, me ha sido de mucha utilidad.
Saludos,
Gracias, excelente explicación, la verdad no las conocía, pero voy a comenzar a utilizarlas. Saludos.
Estimado, una consulta. Tengo una tabla en un esquema y la vista materializada en otro, esto funciona a la perfección.
Dado el volumen de datos estoy tratando de que se refresque on commit, y para ello cree el log de la vista materializada en el mismo esquema de la tabla, pero al alterar la vista materializada con On Commit me reclama.
Tendrás algún consejo?
Saludos
Rodrigo
tengo una base de datos muy transaccional, he creado una vista materializada fast refresh on commit, que involucra 5 tablas estas tablas son muy grandes, el objetivo de esta vista materializada es tener la información reciente resumida, el problema es que el fast refresh on commit, tarda muchisimo, alguien ha tenido este problema
Mira tu por donde, uno se pone a buscar información sobre vistas materializadas en SQL Server y se cruza con este artículo. Saludos amigo Alfonso
Excelente explicación. Ojalá pudieras sacar un artículo que explique características avanzadas de Vistas Materializadas, como por ejemplo cuando usar PRIMARY KEY y cuando ROWID, Casos complejos donde si se puede usar refresh fast.
Esto funciona para mysql?
Como puedo saber cuanto demoro (tiempo?) cada actualización programada en la vista materializada.
Muy buena explicación, gracias
como puedo darle el privilegio un usuario para ver las vistas materializadas
GRANT ALL ON nombredelavista TO usuario;
Y si no son todos los privilegios, cambia ALL por los privilegios que necesites.