Vistas materializadas (materialized views) en SQL y PL/SQL (1)
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 materializadasCREATE 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.
Artículos relacionados: El refresco de las vistas materializadas o materialized views en PLSQL.
17 comentarios:
Muy interesante
Hola,
Estoy pensando en crear una vista materializada (que tendría columnas) a partir de una select con clausula union de varias tablas existentes.
La nueva vista materializada sería la clave primaria i foreing key de una nueva tabla.
Crees que sería una práctica recomendable?.
Felicidades por tus artículos.
Luis
Luis,
No entiendo muy bien a que te refieres con que la vista materializada sería la clave primaria foreing key de una nueva tabla.
Las vistas materializadas están especialmente recomendadas para utilizarse en la generación de informes y mejorar el rendimiento de la base de datos.
La forma de funcionamiento sería:
- Crea una vista materializada con una cunsulta que genere los resultados del informe.
- Refrescar la vista materializada una o dos veces al día dependiendo de las necesidades.
- Generar el informe utilizando la vista filtrando los datos según se necesite.
Buenas,
Me parecen muy interesantes tus ayudas del blog y las utilizo a menudo, pero me gustaría plantearte una duda.
Estoy intentando hacer una vista materializada ara un informe, el cual tiene muchos cálculos para la obtención de datos necesarios para mi empresa, por lo que me he creado funciones para obtenerlas y estas funciones son columnas de una vista, si creo la vista normalmente me funciona, pero con la vista materializada me da un error en estas funciones --> ORA-01723: las columnas de longitud cero no están permitidas. ¿A que se debe esto?¿No se pueden utilizar funciones en las vistas materializadas?
Muchas gracias por todo
Hola Jesús,
El FAST REFRESH no admite que se utilicen funciones propias, sólo admite algunas funciones estándar pero no todas.
El problema también podría ser que estés definiendo alguna columna con valor NULL. En este caso debes usar la función CAST para indicar que la columna no tiene longitud cero. Por ejemplo:
CREATE MATERIALIZED VIEW nombre_vistam
AS SELECT campo_1, CAST(NULL as VARCHAR2(10)) campo_null
FROM nombre_tabla;
Muchas gracias Pepelu, efectivamente era eso lo que pasaba, yo lo he solucionado y tira sin problemas, muchas gracias por todo.
Hola pepelu, estoy empezando en el mundo del Oracle y tengo una inquietud que tiene que ver con lo aquí comentado, espero me puedas ayudar...
He hecho un cursor con una consulta entre 2 tablas... clientes y sus datos financieros...
los cuales estan ordenados por sus nombres, ademas obtengo un numero de indice y de pagina algo como esto
indice pagina codigo nombre direccion saldo
1 1 000658 Barrantes Juan av. el pilar 391 58.30
1 2 001505 Bolivar Maria av. el olivar 122 0.00
1 3 000522 Caceres Sonia jr giraoles 1005 0.00
2 1 006091 Duran Rodrigo pje. camelias 523 17.80
2 2 001555 Jara Roxana av. ramon castilla 35 12.30
2 3 000058 Palomino Rey jr. tafur t. 2563 0.00
3 1 009074 Perez Marcos av. paseo colon 598 98.60
3 2 000205 Ramones Luis pj. diandria 1435 0.00
3 3 004125 Torres Daysi pje. quijandria 266 0.00
.
..
...
el indice y la pagina no son datos de tabla, yo los calculo(dividiendo el rownum y sacando el residuo.. etc.)
lo que necesito porsteriormente son los mismos datos pero ordenados por codigo, pero conservando el indice y la pagina
algo como esto
indice pagina codigo nombre direccion saldo
2 3 000058 Palomino Rey jr. tafur t. 2563 0.00
3 2 000205 Ramones Luis pj. diandria 1435 0.00
1 3 000522 Caceres Sonia jr giraoles 1005 0.00
1 1 000658 Barrantes Juan av. el pilar 391 58.30
1 2 001505 Bolivar Maria av. el olivar 122 0.00
2 2 001555 Jara Roxana av. ramon castilla 35 12.30
3 3 004125 Torres Daysi pje. quijandria 266 0.00
2 1 006091 Duran Rodrigo pje. camelias 523 17.80
3 1 009074 Perez Marcos av. paseo colon 598 98.60
.
..
...
y despues necesito los mismos datos ordenados por saldo(no muestro el ejemplo porque creo que ya se entiende la idea)
entonces .... claro que podria hacer dos, tres o mas cursores haciendo un ligero cambio en la consulta para ordenarlos por dichos campos...
pero, eso es lo que no quiero hacer, porque ejecutaria 2 o mas veces la misma consulta, creo que innecesariamente..
no habria forma de almacenar la primera consulta en una especie de tabla temporal en memoria(sin necesidad de crearla fisicamente, tal vez con el with)
de tal manera que las siguientes consultas la haria a esta tabla temporal ordenandolas por los campos que deseo....
espero que se haya entendido la idea y que me puedan ayudar... desde ya muchas gracias
Hola Ismael,
Echa un vistazo a este artículo: tablas temporales en Oracle.
También se me ocurre que crees un par de funciones, una que devuelva el "índice" y otra que devuelva la "página", cuyos argumentos sean los valores que utilizas para realizar los cálculos y las incluyas en la primera consulta:
SELECT
calc_indice(arg1, ..., argn),
calc_pagina(arg1, ..., argm),
codigo,
......
saldo
FROM
......
WHERE
......;
Muchas gracias pepelu no pensaba que me ivan a responder tan rapido...pero no te ofendas, mi problema no es con el indice ni la pagina... te dire lo que estoy haciendo actualmente...
mediante un procedure inserto los datos de mi consulta en una tabla temporal, luego como necesito mas de una vez esa data(pero en diferente orden) hago la consulta a la tabla temporal con diferente order by...entonces como ví en en la explicacion del WITH, queria saber si habia algo parecido, o con el mismo WITH, podria crear una tabla temporal(no fisicamente con el CREATE GLOBAL TEMPORARY TABLE)... algo asi....
(dejemos de lado el indice y la pagina)
WITH tb_tmp AS (
SELECT c.codigo, c.nombre, c.direccion, d.saldo
FROM clientes c, datos_finan d
WHERE c.codigo = d.codcli)
SELECT codigo, nombre, direccion, saldo from tb_tmp
order by codigo;
--si es que se puede, quiero hacer lo siguiente
cursor cur_ordnombre is
SELECT codigo, nombre, direccion, saldo from tb_tmp
order by nombre;
cursor cur_orddirec is
SELECT codigo, nombre, direccion, saldo from tb_tmp
order by direccion;
cursor cur_ordsaldo is
SELECT codigo, nombre, direccion, saldo from tb_tmp
order by saldo;
eso es lo que quisiera hacer, no sé si se podrá, espero que haya sabido explicarlo y que me haya dejado entender.... reitero mi agradecimiento por haberme ayudado
Ismael, ya entendí lo que quieres hacer. La sentencia WITH no permite hacer lo que propones, la tabla o tablas que se definen dentro del WITH son para utilizarlas dentro de la consulta SELECT que viene después del WITH.
Buenas, pepelu, me estoy iniciando en oracle y necesito un favor a ver si me puedes ayudar estoy estoy tratando de realizar una view materialized de este querys el cual contiene sub-queyrs y no me permite
CREATE VIEW cuenta
AS (select a.*,(select e.nombre_empresa from empresa e
where e.id_empresa = a.id_empresa ) as x
from asegurado a where a.id_asegurado = 'XXXXXXXX')
favor si me puedes ayudar te lo agradeceria
buenas tardes actualmente me encontré con la necesidad de crea una vista materializada, sin embargo no encuentro un ejemplo en el que trabajen con mas de una tabla base para la creación de la vista el cual es mi caso, a ver si me prodrian proporcionar un ejemplo de este caso
gracias
Jaromens, Joelvin, creo que con este ejemplo os respondo a los dos:
CREATE MATERIALIZED VIEW cuenta AS
SELECT a.*, e.nombre_empresa
FROM asegurado a, empresa e
WHERE e.id_empresa = a.id_empresa
AND a.id_asegurado = 'XXXXXXXX';
Gracias, pepelu, me funciono muy bien , ya la realice ahora una pregunta, puedo realizar una view a partir de varias sub-consultas y crearle un index?
Gracias de antemano
Las vistas materializadas admiten la creación de índices, las vistas normales no.
Aunque no sé si es esto lo que quieres, puedes crear una view con varias sub-consultas uniéndolas con el comando UNION:
SELECT campo1,...,campoN FROM ... WHERE ...
UNION [ALL]
SELECT campo1,...,campoN FROM ... WHERE ...
UNION [ALL]
SELECT campo1,...,campoN FROM ... WHERE ...
Buenas pepelu espero que estes bien gracias por la ayuda, un pregunta estoy investigando sobre los trigger y realmente no me queda muy claro estuve leyendo y es algo como de restriciones sobre un determinado evento dentro de un registro en una tabla, sera que me puedes ayudar con esto, gracias.......
¿Has leído este artículo sobre los triggers PL/SQL?
Publicar un comentario en la entrada