Anuncios en tutorial de programación PLSQL

jueves, 15 de junio de 2023

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 en SQL y PLSQL 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.

Materialized View Refresh

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

En caso del ejemplo, la base de datos Oracle refrescará automáticamente la vista materializada mañana a la 9:00 AM y posteriormente todos los martes a la 4:00 PM.

30 comentarios:

Joelvin dijo...

buenas tardes pepelu actualmente estoy realizando unas pruebas con un paquete en el hay un grupo de procedimiento que funcionan en conjunto. sucede que el paquete debe verificar 3 millones y solo procesa 900 mil luego se interrumpe y no da ningun error . lo ejecuto nuevamente y solo procesa 1000.

JLPM dijo...

Joelvin,

Me temo que no puedo ayudarte con la información que me das pero lo que cuentas suena bastante raro.

Felipe dijo...

Buenas Pepelu, ¿cómo haría que el refresco de mi vista materializada sea cada hora? El problema que es aproximadamente cada hora o algo más se realiza una descarga masiva de datos y hay que recalcular todos los datos. Lo que no sé es cómo poner que lo haga todos los días cada hora empezando desde hoy.

Muchas gracias por tu ayuda y el artículo

JLPM dijo...

Hola Felipe,

Sería algo tan sencillo como utilizar:

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

Moshi dijo...

Hola Pepelu.

Despues de crear la vista materialazada con su respectiva configuración para refrescarla, ¿puedo volverla a ejecutar antes o despues de que se refresque la información?

De antemano muchas gracias por tu respuesta.

Saludos

JLPM dijo...

Moshi, si te refieres a cuando puedes recrear la vista para actualizar la consulta que saca los datos, lo puedes hacer en cualquier momento.

Moshi dijo...

Hola, ahora tengo otra duda relacionada con los dblink usando vistas materializadas.
Trataré de explicar lo que intento hacer.
En la instancia A tengo un catalogo, usado por el sistema X, que se actualiza varias veces por mes.
En la instancia B tengo otro catalogo, usado por el sistema Y, que actualmente se actualiza de manera manual.
Este catalogo tiene algunos campos con la misma información del catalogo de la instancia A.
Lo que quiero hacer es usar el dblink para enlazar las instancias A y B y usando una vista materializada, sustituir el catalogo y actualizarlo una vez al día.
Ambas instancias estan en equipos distintos.
Pero no se de que lado tengo que hacer el dblink y ejecutar la vista.

Creo que fue demasiado para lo que necesito saber, solo espero haberme explicado correctamente.

Saludos.

JLPM dijo...

Moshi,

Por lo que cuentas puedes crear el DBLink en cualquiera de las bases de datos. Yo lo haría en aquella donde haya un mayor volumen de datos en local, para así minimizar el tiempo de traspaso de datos a través de la red.

Anónimo dijo...

Gracias por tu artículo.

Me ha venido estupendamente ya que estaba teniendo un problema de rendimiento con unas vistas que se refrescaban cada 10 minutos y el sistema se ralentizaba enormemente. Ahora las he programado para que se refresquen una vez al día a las 2 de la mañana y problema resuelto.

jmarben dijo...

Excelente tu blog. Con tu entrada inicial y las respuestas que has dado a otras personas que tenían dudas me has ayudado bastante.

Gracias por compartir.

Unknown dijo...

Hola, resulta que tengo una vista que esta conformada por otra vista y está relacioanda con otras tablas, para no tener que entender el modelo de datos se podrá hacer que al sufrir un cambio las tablas que tiene la vista actualice otra tabla tempotal.

JLPM dijo...

Hector Mauricio, no entiendo muy bien cual es tu problema, de todas formas te diré que no resulta muy conveniente encadenar vistas desde el punto de vista del rendimiento.

Si quieres puedes contactarme en pepelublog[arroba]gmail[punto]com.

jamorens dijo...

Buenas, amigo Pepelu, quisiera que me ayudaras nuevamente en algo, realice un vista materializada con un REFRESH FORCE, el problema es que se me esta llevando mucho tiempo realizar el refresco, leyendo en tu blog, me he interezado por el refresh fast pero con los rangos ROWID, para que me actualize solo los cambios realizados en el dia, este refresh se ejecutará a través de prodecure en las noches por medio de un job, te agradeceria cualquier ejemplo y explicación del mismo, gracias

JLPM dijo...

Hola Jamorens, lo que me preguntas puede ser tema de un nuevo post. Basícamente necesitas crear con el comando SQL CREATE MATERIALIZED VIEW LOG las tablas de log necesarias para tener la capacidad de hacer un REFRESH FAST. Y luego al definir la materializeed view usar REFRESH FAST WITH ROWID.

WBC dijo...

Hola,

Debo decir que me gustó la manera tan clara de explicar que tienes, muchas gracias por compartir tus conocimientos con nosotros.
Mi duda es como calendarizo una vista materializada para que se ejecute el primer dia de cada mes.. Gracias!

JLPM dijo...

Hola Wendy, perdona por el retraso pero tu mensaje se me había pasado por alto.

La cláusula NEXT sería algo similar a lo siguiente:

NEXT LAST_DAY(TRUNC(SYSDATE)) + 1

De esta manera el refresco debería empezar a las 00:00 del primer día de cada mes.

Unknown dijo...

Hola,
He leido tu post, y me surgen algunas dudas, como por ejemplo cómo saber cuándo ha sido la última vez que la vista materializada se ha refrescado? he creado una con estas opciones,

CREATE MATERIALIZED VIEW bd.mivistamaterializada
BUILD IMMEDIATE
REFRESH START WITH (TRUNC(SYSDATE ,'MM') + 20/ 24 )+ 1
NEXT (TRUNC(SYSDATE + 30,'MM') + 20/ 24 )+ 1
AS
SELECT...


he querido hacer esto: que la actualice los días 2 de cada mes a las 8 pm, pero creo que no lo está haciendo, o cómo saber si lo ha hecho.

Saludos

Gracias.

JLPM dijo...

Hola José A.,

La definición de la vista materializada que has hecho me parece correcta.

Para chequear si una vista materializada se ha refrescado puedes utilizar las vistas DBA_MVIEW y DBA_MVIEW_ANALYSIS, pero también puedes guardar una copia de la vista el día antes del refresco y al día siguiente comprobar si ha cambiado.

Unknown dijo...

Buenas,

Estoy haciendo una vista materializada, con refresh force, que primero aplica un refresco FAST y si no puede hace un COMPLETE, el problema es que nunca hace un FAST, y durante 5 min que tarda en cargarse la vista materializa, si la consultamos, no tiene ningún dato, yo pensaba que siempre mantenía una instancia de los datos, hasta que se volcará los nuevos datos, ¿hay alguna forma de evitar que la vista se quede en blanco durante el tiempo de refresco?

Saludos a todos.

JLPM dijo...

Hola Victor, si el refresco tiene que ser de tipo COMPLETE lo adecuado es refrescar la vista materializada a horas en las que no haya muchos usuarios trabajando. No se puede evitar que la vista no muestre datos durante un refresco de tipo COMPLETE.

Unknown dijo...

Buenas Pepelu,

Al final como el refresco me tardaba al menos 10 minutos, y durante ese tiempo la vista no sacaba data, he creado dos vistas materializadas, la primera que tarda un buen tiempo en refrescarse en modo COMPLETE, y la segunda que tira de la primera, y el refresco es instantáneo, esta es la que es visible al usuario. El refresco de cada una de ellas lo programe para que cuando la vista materializa que usa el usuario se refresque ya se haya refrescado la anterior vista. Esta es la solución que he podido aplicar a mi problema.

Saludos.

Unknown dijo...

Buenos días,
ME ayudan con algo?
Resulta que estoy realizando un refresh de una vista materializada, esta al ejecutar se realizar de forma correcta sin errores, pero al programar un job para que esta realice el refrescamiento en cierta hora, al ejecutarse por el job programado este genera el siguiente error:
ORA-12012: error en la ejecución automática del trabajo 2343
ORA-01422: la recuperación exacta devuelve un número mayor de filas que el solicitado
ORA-06512: en línea 4

De casualidad saben que puede ser o alguna idea para superar este problemilla, con el refresh atraves del job programado

JLPM dijo...

Hola Wilmer,

Necesitaría ver el código de tu vista materializada para ver si puedo ayudarte.

halg dijo...

Interesante información, quisiera hacer una vista materializada que se ejecute el primer dia del mes.

Antonio Andrade dijo...

Jose Luis, ... y que haciendo una búsqueda sobre refrescar vistas materializadas dé yo contigo :D
saludos

JLPM dijo...

Antonio, pues me has descolocado porque no te sitúo, no me suena el apellido Andrade. ¿Eres Antonio de Sedecal?

Antonio Andrade dijo...

El Jamoni

Anónimo dijo...

Hola José, estaba viendo el tema de las vistas materializadas y quisiera saber si puedes ayudarme, ya que necesito que mi vista se actualice diariamente a la 01AM, esta es mas o menos la sintaxis que manejo.

ALTER MATERIALIZED VIEW BICEVIDANET.MV_SINIESTRALIDAD_SALUD
REFRESH COMPLETE
ON DEMAND
START WITH TO_DATE('07-sep-2018 01:00:00','dd-mon-yyyy hh24:mi:ss')

ahí le digo que parta a tal hora, pero no logro dar con la sintaxis para que se refresque diariamente a la misma hora.

Agradecería tu ayuda.

Ivo Moreno

Oracle 11gR2

Saludos

Anónimo dijo...

ya lo resolví, y aunque no fue por una pronta respuesta tuya jaja, se agradece la ayuda prestada del blog.

Dejo el codigo para alguién mas

ALTER MATERIALIZED VIEW BICEVIDANET.MV_SINIESTRALIDAD_SALUD
REFRESH COMPLETE
ON DEMAND
START WITH TO_DATE('07-sep-2018 23:59:59','dd-mon-yyyy hh24:mi:ss')
NEXT TRUNC(SYSDATE) + 1 + 05/24 + 00/1440 + 00/86400


Saludos

Alejandro dijo...

Hola amigo , como estas?
He revisado tu blog y me ha ayudado mucho. Una consulta. Me podrias ayudar?
como podria hacer para que se ejecute el script todos los dias a las 9 de la mañana? como se especifica eso en el next?

Favor agradecerua tu ayuda

Saludos