Anuncios en tutorial de programación PLSQL

jueves, 6 de agosto de 2020

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.

Vistas materializadas (materialized views)

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.

Artículos relacionados: El refresco de las vistas materializadas o materialized views en PLSQL.

73 comentarios:

marcexx66 dijo...

Muy interesante

Unknown dijo...

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

JLPM dijo...

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.

Jesús Gómez dijo...

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

JLPM dijo...

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;

Jesús Gómez dijo...

Muchas gracias Pepelu, efectivamente era eso lo que pasaba, yo lo he solucionado y tira sin problemas, muchas gracias por todo.

Ismael dijo...

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

JLPM dijo...

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
......;

Ismael dijo...

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

JLPM dijo...

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.

jamorens dijo...

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

Joelvin dijo...

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

JLPM dijo...

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';

jamorens dijo...

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

JLPM dijo...

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 ...

jamorens dijo...

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.......

JLPM dijo...

¿Has leído este artículo sobre los triggers PL/SQL?

Unknown dijo...

Hola Pepelu,
Antes de nada decirte que toda la informacion de arriba esta muy bien y es muy util y practica. GRACIAS!

La pregunta es, puedes tener varias MATERIALIZED VIEWS ligadas a una misma tabla? Vamos a suponer que tengo una tabla con muchos campos y quiero tener 2 MAT VIEWS para 2 informes diferentes para no tener una y que este muy sobrecargada, en cuyo caso pierde su funcion original.
es posible?
en caso afirmativo...como puedo discriminar a la hora de hacer una query a la tabla que use una u otra?
MUCHAS GRACIAS DE ANTEMANO

JLPM dijo...

Se pueden tener todas la materialized views que quieras ligadas a una misma tabla. Cada materialized view tendrá un nombre diferente y de esa manera es posible discriminarlas.

Unknown dijo...

Hola Pepelu, no se si puedas ayudarme con este caso, de antemano te agradezco la ayuda,

Actualmente estoy trabajando con Vistas Materializadas y le aplique a una de ellas el DBMS_MVIEW.EXPLAIN_MVIEW procedure, enviandome lo siguiente :

REFRESH_FAST_AFTER_INSERT N NULL mv references PL/SQL function that maintains state
REFRESH_FAST_AFTER_ONETAB_DML N NULL see the reason why REFRESH_FAST_AFTER_INSERT is disabled
REFRESH_FAST_AFTER_ANY_DML N NULL see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled
REFRESH_FAST_PCT N NULL PCT is not possible on any of the detail tables in the materialized view
esto es, el campo POSSIBLE, me lo envia como 'N', mi pregunta es como puedo convertir esta 'N' como 'S' (o sea como puedo crear la VM sin este error), saludos y gracias

JLPM dijo...

Felix,

El FAST REFRESH no admite que se utilicen funciones propias, sólo admite algunas funciones estándar pero no todas.

El primer mensaje de error me indica que estás utilizando una función PL/SQL dentro de la vista materializada. Debes modificar la Materialized View para que no utilice funciones.

Tatoo dijo...

Hola de antemano gracias por la respuesta, al crear una vista materializada y hacerle un refresco COMPLETO cada día por ejemplo por la ejecución de un job nocturno (estaríamos hablando de un select a varias tablas), esto significaría que mi disco se irá llenando progresivamente en cada refresco aun cuando los registros de la vista se vayan borrando diariamente?

JLPM dijo...

Tatoo, la vista materializada ocupará lo que ocupen los datos devuelto por la ejecución de la consulta que genera la vista. Ocupara más espacio si las tablas base aumentan de tamaño y la vista materializada incorpora más registros.

Carlos dijo...

Hola Pepelu.
¿Podria utilizar las vistas materializadas para que dos tablas de diferentes bases de datos pudieran compartir un mismo dato y cuando se actualizara en una base de datos tambien se cambiara en la otra y viceversa?

Es decir tenemos las siguientes bases de datos con sus tablas.

Base de datos A:
Tabla Usuarios_A:{Pk,Nombre_A}

Base de datos B:
Tabla Usuarios_B:{Pk,Nombre_B}

Si ahora cambiamos el campo Nombre_A de Usuarios_A lo deseado seria que tambien se cambiara el campo Nombre_B de Usuarios_B.

La vista materializada en este caso no perteneceria a una unica base de datos master y fuera la otra base de datos la que consulta y actualiza la vista materializada. Es como si ambas bases de datos fueran master.
¿Es posible este enfoque o hay otras soluciones mas apropiadas para conseguir este proposito?

Muchas gracias y un saludo

JLPM dijo...

Hola Carlos,

Si no he entendido mal lo que quieres es tener dos tablas en dos bases de datos diferentes cuyos datos esten sincronizados y que ambas se puedan actualizar manteniendo la sincronización.

Se podría hacer con un trigger PL/SQL pero no con vistas materializadas. De todas formas eso de tener dos tablas masters con los mismos datos no me parece una buena idea.

Aprovecho para comentar que una de las utilidades de las vistas materializadas es poder subir datos de otras bases de datos (se construye la vista accediendo a tablas en bases de datos remotas). Pero claro, las tablas master estaría en la base de datos Oracle remota.

chevito dijo...

Hola buen día, una pregunta. Tengo una tabla con datos de ventas con miles de registros, y es muy lento realizar una consulta del tipo "las ventas del ultimo mes". Es posible hacer una vista materializada con un argumento 'where' cambiante. Es decir, stamos en mayo este mes, y la vista mat tndria la condicion esa, pero el sig mes, tndria como condicion las ventas d junio. Necesariamente tendria que tener vistas independientes y generarlas manualmente? Se q tndria q tener algunas predefinidas (semana, quincena, mes, bimestre) pero como se podria hacer? de antemano muchas gracias y q tnga buen día.

JLPM dijo...

Hola Chevito,

Así a bote pronto se me ocurre que puedes jugar con condiciones como:

-- Ventas desde hace 30 días
WHERE fecha_venta > SYSDATE-30
AND fecha_venta < SYSDATE

-- Ventas del mes corriente
WHERE fecha_venta > SYSDATE-DAYOFMONTH(SYSDATE)+1
AND fecha_venta < SYSDATE

Pero si el problema es sólo en rendimiento de las queries sobre la tabla base, yo probaría primero a crear un índice sobre fecha_venta.

chevito dijo...

Hola, muchas gracias por su respuesta. Bastante interesante. No me queda muy claro lo de los índices. Crearía solamente un índice sobre el campo fecha_venta y ya? solo eso o tendría q hacer más modificaciones, no sé en la consulta o en alguna otra parte, y por último, sería algún tipo en especial de índice. La vdd q soy un poco nuevo en oracle. De nuevo muchas gracias y quet tenga un excelente día.

JLPM dijo...

Chevito, creo que lo mejor es que me envíes por email la consulta que quieres optimizar. Pienso que con que crees un indice debería bastar. Mi email lo puedes ver en mi blogs personal.

chevito dijo...

Hola de nuevo muchas gracias. Estoy realizando pruebas con los índices, y tmbn hice un mecanismo con vista materializada, a ver como nos va. El problema es q en la tabla continuamente se están insertando registros de todas las sucursales, y al tener indices, creo q reduce un poco el rendimiento,pero ya stamos en pruebas. Muchas gracias, que tenga buen día!!!!

Pedro dijo...

Hola Pepelu como estas, mira tengo una vista materializada formada por muchas tablas con inner joins, varias de las tablas base cambian constantemente, necesito que mi vista materializada se mantenga actualizada a estos cambios, como hago esa actualizacion?

JLPM dijo...

Pedro, echa un vistazo a esta entrada:

Tipos de refresco en las vistas materializadas.

Pedro dijo...

Que tal, gracias por la info... Tengo otra curiosidad, como seria el intervalo de refescamiento para que se actualize cada 30 mins?

JLPM dijo...

Hola Pedro,

Tendrías que utilizar un código parecido al siguiente:

CREATE MATERIALIZED VIEW nombre_vm
...
REFRESH START WITH SYSDATE
NEXT SYSDATE + 0.5/24
AS SELECT ...;

rubenever dijo...

hola, necesito actualizar una vista con un trigger en oracle, se puede hacer?, ¿Alguna idea de como encararlo?
Muchas gracias
rubenever@gmail.com

jamorens dijo...

Buenas Ppelu, gacias por toda la ayuda que anteriormente me has bridado, ya he logrado hacer las vistas, tanto logicas como materializadas de igual forma los tigger.
En esta ocasión quisiera preguntarte la forma de hacer un index a una vista meterializada,si puedes enviarme un ejemplo te lo agradezco

JLPM dijo...

Jamorens, los índices se crean igual que sobre una tabla normal. Por ejemplo:

CREATE INDEX mi_vist_mat_idx
ON mi_vist_mat (campo_vista_1);

Unknown dijo...

Buenos dias,

Tengo problemas al crear una vista materializada, no se si es por la version de Oracle o por algun problema con el usuario. Esta vista la creo en Oracle 8 y sin problemas, pero la misma sentencia la lanzao en Oracle 11 y me da el error "ORA-01031: Privilegios insuficientes". Este es el script:

CREATE MATERIALIZED VIEW V1HUB_CABECERA_PRODUCTO
TABLESPACE TBLSPACEHUB
NOCACHE
LOGGING
NOPARALLEL
BUILD IMMEDIATE
REFRESH FORCE ON DEMAND
WITH ROWID
AS
SELECT MVTDATE, SOURCESYSTEM, FILENAME, ITEM
FROM HUB_CABECERA_SWP

¿Podría alguien ayudarme?

Un saludo y muchas gracias

JLPM dijo...

Raquel, ese error significa que en la base de datos de Oracle 11 no tienes los permisos adecuados para crear la vista materializada. Debes pedir al administrador de la misma que ponga los permisos necesarios al usuario que estás utilizando para crearla.

Unknown dijo...

Muchas gracias Pepelu, eso me imaginaba, pero el administrador me aseguraba que el usuario tenia todos los permisos, pero va a ser que no.

Un saludo y muchisimas gracias,
Raquel

Unknown dijo...

Como puedo crearle una vista materializada a otro usuario ?

Mi usuario tiene permisos de create any material....
alter any material...

Pero al tratar de crearla saca error de insuficientes privilegios

JLPM dijo...

Hola Fernando, para crear una vista materializada bajo otro esquema de usuario sólo se necesitan permisos de "Create/Alter Any Materialized View". Si ya los tienes entonces puede ser que no tengas acceso a alguna de las tablas que estés utilizando.

Suu dijo...

Buenos días,

Me ha ayudado mucho tu explicación para entender las vistas materializadas. Muchas gracias. ¿Podrías ayudarme en comprender esto?

EXECUTE exec_sql WITH query=
'CREATE MATERIALIZED VIEW ETEP_VIEW_ESTIMATE_EXP
INITRANS 10 USING NO INDEX
REFRESH FORCE START WITH to_date('01-09-2006 01:00:00','MM-dd-yyyy hh24:mi:ss') NEXT sysdate + 1
AS select trunc(fecha_alta) fecha_alta,oficina_empleo,cod_cat_expediente,estado_actual, count(1) num_expedientes from etep_obj_expedientes_s group by trunc(fecha_alta),oficina_empleo,cod_cat_expediente,estado_actual having count(1) > 2';

¿Qué quiere decir?
¿Qué significa aquí INITRANS, imagino que USING NO INDEX será sin índices, verdad?

Muchas gracias y un saludo,
Suu

JLPM dijo...

Hola Suu,

INITRANS es un parámetro que controla el numero de ITLs (transacciones iniciales) que puede gestionar un bloque, es un parámetro normalmente controlado internamente por la base de datos Oracle. Si se especifica al crear una vista materializada es para mejorar el rendimiento, en teoría cuanto mayor sea el valor, mejor será el rendimiento.

Y efectivamente USING NO INDEX indica que no se usen índices al generar la vista.

Suu dijo...

Muchas gracias

Unknown dijo...

Hola Pepelu, tengo una consulta para vos, estoy creando una vista materializada con parámetros, y uno de esos parámetros es el from de la query que voy a ejecutar, o sea que mi from sería dinámico de acuerdo al parámetro con que se ejecuta la vista, es posible hacer esto ? Como lo logro ?

JLPM dijo...

Hola Christian, para crear una vista materializada como la que indicas deberías hacer un DROP previo de la existente y después recrear la nueva con el nuevo FROM. Yo no veo mucho sentido a hacer algo así con una vista materializada a menos que el borrado y la recreación se haga muy exporádicamente.

OMARE dijo...

Antes que cualquier cosa, muy buena información Pepeleu y sobre todo las ganas de compartirnos tus conocimientos.

Tengo un esquema de replica mediante vistas materializadas, hasta ahi muy bien, el problema es que en la DB principal se agregan nuevas columnas a las tablas maestro, pero en las VM de la DB secundaria no se ven reflejados esos cambios, Mi duda es la siguiente ¿Se puede modificar la sentencia SELECT de la vista materializada para agregarle las nuevas columnas?.

Saludos y gracias

JLPM dijo...

Hola Omar, si te refieres a si se puede utilizar el comando ALTER MATERIALIZED VIEW para alterar el SELECT que define la vista materializada te diré que no se puede.

No obstante, siempre puedes hacer un DROP de la vista materializada y crearla de nuevo con el nuevo SELECT.

OMARE dijo...

Solo unas dudas mas,
-Que pasa con los "Grupos de Refresh", los "LOGS DE LAS MATERIALIZED VIEW" que aparecen en el "Enterprise Manager", se crean nuevamente o esos no se modifican,
-Que pasa con los datos que tiene la materialized view a recrear, se mantienen o se realiza nuevamente la carga.

Saludos y gracias.

JLPM dijo...

Omar, los logs tienes que recrearlos y los datos de la vista materializada hay que cargarlos de nuevo.

OC dijo...

Puedo definir una vista materializada que sea a una hora especifica del dia por ejemplo a las 8PM, gracias de antemano

JLPM dijo...

Hola Oc, claro que se puede. Echa un vistazo a este otro artículo: El refresco de las vistas materializadas.

Paulita dijo...

Hola Papelu: Si que eres un genio en estos de las vistas materializadas. Has tenido respuestas para todos... Mira te cuento, se me recomendó realizar una, debido a que un reporte de una aplicación esta funcionando muy lento. Actualmente es una vista solamente. Sin embargo la query que genera esta vista tiene subquerys, cuando quiero crear la vista materializada no me deja por este motivo. ¿Qué puedo hacer en esos casos? Saludos, Paula.

JLPM dijo...

Paula, en estos casos o bien se rediseña la query para no utilizar subquerys, o se convierten las subquerys en vistas y luego se utilizan las vistas.

Paulita dijo...

Creo que la mejor alternativa es el rediseño de la query, pues crear nuevas vistas me genera un costo de mantención demasiado elevado cuando hablamos de tablas de más de 5000 registros. Basicamente lo que quiero es evitar los full scan table que se hacen en esas subquery, quizás solo sea un tema como dices tú de reformar las querys y posiblemente revisar los índices de las tablas involucradas.

Gracias :P

Anónimo dijo...

Pepelu, felicitaciones por tu pagina. Realmente admiro tu dedicacion y paciencia para con las respuestas de todos los usuarios.

Saludos

Pablo

Luis Roman dijo...

buenas,
es posible crear una vista materializada vacia ?

me explico con un ejemplo sencillo
-- tabla con datos
select count(*) tbl_origen
= 100 registros
-- creacion de vista materializada
create materialized view vm_origen as (select * from tbl_origen)

ahora cuando se cree mi vista materializada me creara con los 100 registros, y yo quiero que me cree solo la estructura para luego hacer el refresh.

ojala me haya explicado, un abrazo

JLPM dijo...

Hola Luis,

Para que la vista materializada se cree inicialmente vacía debes utilizar la cláusula BUILD DEFERRED. Está explicado en el post.

Daniel Gallegos dijo...

Buenas noches, me gustaria saber como puedo identificar que program units y tablas estan usando una vista materializada. Me pidieron que checara eso para saber si es factible eliminar la vista materializada. Tambien me gustaria saber porque una vista materializada se encuentra invalid y como solucionar ese problema.

Daniel Gallegos dijo...

la vista materializada es la siguiente:
CREATE MATERIALIZED VIEW "THOT"."WQ_ATMS_REFERRAL_ASMT_TASKS_MV" ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" BUILD DEFERRED USING INDEX REFRESH COMPLETE ON DEMAND START WITH sysdate+0 NEXT TRUNC(SYSDATE + 1) + (23/24) USING DEFAULT LOCAL ROLLBACK SEGMENT USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE
AS
SELECT SENTENCE;

Y se esta usando un job para que lo ejecute diariamente a las 23:00 horas.
Saludos y gracias por la ayuda

JLPM dijo...

Hola José Daniel,

Lo mejor para identificar aquellos objetos PL/SQL que utilizan tu vista materializada es que utilices la tabla DBA_SOURCE (también debes chequear la ALL_SOURCE).

Puedes utilizar este SQL como referencia:

SELECT name, type, text
FROM dba_source
WHERE UPPER(text) like '%WQ_ATMS_REFERRAL_ASMT_TASKS_MV%'

Daniel Gallegos dijo...

Buenas tardes pepelu, muchas gracias por la respuesta, ahora mi duda es la siguiente:
Hay un job que la esta utilizando y ese job refresca esas vistas materializadas cada dia a las 23:00, y pero creo que al hacerlo las vistas materializadas las vuelve invalidas, porque pasa esto?.
te mando el job:
WHAT dbms_refresh.refresh('"THOT"."WQ_ATMS_REFERRAL_ASMT_TASKS_MV"');
THIS_DATE (null)
THIS_SEC (null)
NEXT_DATE 23-FEB-11
NEXT_SEC 23:00:00
BROKEN N
INTERVAL TRUNC(SYSDATE + 1) + (23/24)
FAILURES 0

JLPM dijo...

Hola José Daniel,

Mientras se está realizando un refresco completo, las vistas materializadas PLSQL no son accesibles.

Echa un vistazo a esta entrada del blog sobre el refresco de vistas materializadas y en concreto a la pregunta que realizo Víctor y a la solución que implementó.

CharlyAndreto dijo...

Hola Pepelu, he estado leyendo tus publicaciones y la vdd te felicito porque conoces mucho y lo mejor es que lo compartes.

Quisiera hacerte una consulta, bueno varias, basadas en tu experiencia, espero puedas ayudarme, el escenario es el siguiente.

Tengo un proceso que se encarga de llenar una tabla de aproximadamente 400 columnas, el cual se tarda normalmente 9 horas, ya que obtiene la informacion de 2 bases de datos remotas, las cuales
llegan a tener hasta 20 millones de registros en algunas de sus tablas.

Actualmente lo hago todo por medio de un cursor principal que me permite filtrar todos aquellos registros que voy a incorporar en mi tabla destino (Aproximadamente 300 diarios), de alli hago busquedas por cada una de las 400 columnas a las tablas de donde se extrae la informacion

Quiero hacerle una re ingenieria a dicho proceso, y he estado pensando en hacerlo con vistas materializadas para poder crearle indices a las mismas y poder bajar los tiempos de respuesta de las consultas.

me podrias dar tu opinion, de cual seria la mejor forma de hacerlo, basado en tu experiencia.

JLPM dijo...

Hola Charly Andreto, cuando se tienen que transferir datos de una base de datos Oracle a otra, lo mejor es contruir una vista materializada o simplemente un vista que contenga todos los datos a transferrir en la base de datos origen, y acceder a dicha vista desde la base de datos destino.

Nunca se deben hacer joins de múltiples tablas a través de un database link. Lo recomendable es construir una vista que haga los joins de forma local y acceder a dicha vista.

CharlyAndreto dijo...

Gracias Pepelu, ahora solo tengo otra pregunta, mi proceso obtiene y calcula al rededor de 400 campos de otras tablas, como catalogos, y necesito poder saber en que tabla, catalogo o calculo, se genero un error, para que el usuario lo corrija y se pueda relanzar el proceso, solo para aquellos registros con error.

He estado pensando en hacer una funcion por cada (tabla, catalogo o calculo), que me permita obtener la informacion para cada uno de mis 400 campos, y que cuando se genere un error o no encuentre cierta informacion que me permita llenar el campo sea capaz de guardar en una tabla en que funcion se genero el error y con esto podran corregir exactamente el campo origen.

Mi pregunta es:, Oracle, contempla algun mecanismo para el control de este tipo de errores, o es necesario programar todo.

JLPM dijo...

El PL/SQL de Oracle permine manejar excepciones y errores (existen excepciones predefinidas y también se pueden configurar excepciones definidas por el usuario).

Utilizando esta funcionalidad puedes insertar registros en una tabla cada vez que se produzca un error, ya sea un error estándar de Oracle o definido por ti mismo.

Unknown dijo...

Pepelu, buen dia. Muchas gracias por los aportes!!!
Mi consulta es la siguiente, tengo una tabla con tres años de informacion y debido a su tamaño quiero tener una con los tres ultimos meses para consultas. El tema es que tiene que estar actualizada a la ultima transaccion. Que me conviene utilizar, vista materializada o trigger, que es mejor para el motor? Muchas gracias!!!

halg dijo...

Saludos, que se almacena el la VIEW LOG de las vistas materializadas cuando el refresh es fast? Como hago para ver el contenido, de poderse ver.
Muchas Gracias.

Unknown dijo...

Sabeís por que al realizar un drop de una vista materializada no me la elimina y el ScriptRunner se ejecuta hasta el infinito ? no tengo ningúna sesion bloqueando la vista.

Gracias , saludos

Unknown dijo...

Saludos, estoy tratando de borrar una vista materializada DROP MATERIALIZED VIEW MONEDA_VW;
pero se queda pegado sin indicar nada, tengo que cancelar la eliminación, no tira un error oracle, favor alquien que me pueda decir por que ocurre esto

Unknown dijo...

Buenas tardes, soy nuevo con Vistas Materializadas, y quisiera saber como podría hacer en Oracle una Vista Materializada en la cual participan varias tablas, es decir con subconsultas en el select.
Gracias por el apoyo

Manuel.