Oracle - No se refresca vista materializada.

Buenos días a todos,
Me gustaría crear una vista materializada la cual almacene los registros de ayer hasta el presente y que se actualice cada vez que vaya a ser consultada. Esta es la vista que he creado pero que no llega a actualizarse y se queda con los datos que se insertaron en su creación:

CREATE MATERIALIZED VIEW "mv_health_rules" ("period", "id_service", "id_event", "total")
TABLESPACE plat_dat
BUILD IMMEDIATE
USING INDEX
REFRESH COMPLETE ON DEMAND
USING DEFAULT LOCAL ROLLBACK SEGMENT
USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE
AS
select
to_date(concat(to_char("created_at", 'YYYY-MM-DD HH24'),':00:00'),'YYYY-MM-DD HH24:MI:SS') as "period"
, "id_service"
, "id_event"
, count(*) as "total"
from "bill_events"
where "created_at" between trunc(sysdate)-1 and sysdate
group by to_char("created_at", 'YYYY-MM-DD HH24'), "id_service", "id_event"
order by 1;

¿Alguien que pueda decirme el porque no se refresca la MV con los nuevos registros?

Saludos
Mario

Prueba reemplazar REFRESH COMPLETE ON DEMAND por REFRESH FAST ON COMMIT o REFRESH FORCE ON COMMIT

Hola Ramiro,

He probado con lo que me has dicho pero en los dos caso recibo el siguiente error:

Error que empieza en la línea: 1 del comando -
CREATE MATERIALIZED VIEW "mv_health_rules" ("period", "id_service", "id_event", "total")
TABLESPACE plat_dat
BUILD IMMEDIATE
USING INDEX
REFRESH FORCE ON COMMIT
USING DEFAULT LOCAL ROLLBACK SEGMENT
USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE
AS
select
to_date(concat(to_char("created_at", 'YYYY-MM-DD HH24'),':00:00'),'YYYY-MM-DD HH24:MI:SS') as "period"
, "id_service"
, "id_event"
, count(*) as "total"
from "bill_events"
where "created_at" between trunc(sysdate)-1 and sysdate
group by to_char("created_at", 'YYYY-MM-DD HH24'), "id_service", "id_event"
order by 1
Error en la línea de comandos : 14 Columna : 6
Informe de error -
Error SQL: ORA-12054: no se puede definir el atributo de refrescamiento ON COMMIT para la vista materializada
12054. 00000 - "cannot set the ON COMMIT refresh attribute for the materialized view"
*Cause: The materialized view did not satisfy conditions for refresh at
commit time.
*Action: Specify only valid options.
Error que empieza en la línea: 1 del comando -
CREATE MATERIALIZED VIEW "mv_health_rules" ("period", "id_service", "id_event", "total")
TABLESPACE plat_dat
BUILD IMMEDIATE
USING INDEX
REFRESH FAST ON COMMIT
USING DEFAULT LOCAL ROLLBACK SEGMENT
USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE
AS
select
to_date(concat(to_char("created_at", 'YYYY-MM-DD HH24'),':00:00'),'YYYY-MM-DD HH24:MI:SS') as "period"
, "id_service"
, "id_event"
, count(*) as "total"
from "bill_events"
where "created_at" between trunc(sysdate)-1 and sysdate
group by to_char("created_at", 'YYYY-MM-DD HH24'), "id_service", "id_event"
order by 1
Error en la línea de comandos : 14 Columna : 6
Informe de error -
Error SQL: ORA-12054: no se puede definir el atributo de refrescamiento ON COMMIT para la vista materializada
12054. 00000 - "cannot set the ON COMMIT refresh attribute for the materialized view"
*Cause: The materialized view did not satisfy conditions for refresh at
commit time.
*Action: Specify only valid options.


Saludos

Mario

esta bien, es posible que no cumplas alguna de las resticciones de esa clausula, como que la primary key de la tabla maestra tiene que estar en el select o que no tengas privilegio "ON COMMIT REFRESH" en la master table.
revisá lo anterior y si no, volviendo al script original con la clausuala ON DEMAND debes usar alguno de los siguientes procedures del paquete DBMS_MVIEW para refrescarla.

"REFRESH Procedure": Consistently refreshes one or more materialized views that are not members of the same refresh group.

"REFRESH_ALL_MVIEWS Procedure": Refreshes all materialized views that do not reflect changes to their master table or master materialized view.

"REFRESH_DEPENDENT Procedure": Refreshes all table-based materialized views that depend on a specified master table or master materialized view, or list of master tables or master materialized views.

Hola Ramiro,

Es posible que tenga que crear una materialized view log sobre la tabla maestra? ya qu eno veo necesidad de incluir el id en esta MV.

Saludos

Es verdad me habia olvidado de eso, pero de todas formas no va a funcionar sin la clave primaria en el select. El log es para poder hacer el update incremental de tipo REFRESH FAST ON COMMIT o REFRESH FORCE ON COMMIT.
En caso de hacerla ON DEMAND con estas opciones tambien necesitas el log

Al final modifica la opcion del refreh y la VM se esta actualizando.

Añadi la opcion REFRESH START WITH SYSDATE NEXT SYSDATE + 1/24 FORCE y la actualiza cada hora.

Por lo visto no se puede realizar un refresh on commit cuando se hace uso de pseudocolumnas tales como SYSDATE o ROWNUM. Porque esto basicamente requiere de la reescritura de la consulta.
He añadido la opcion de REFRESH START WITH SYSDATE NEXT SYSDATE + 1/24 FORCE que actualiza la vista cada hora y permite hacer uso de pseudocolumnas como sysdate.

Solo me queda una cosilla que me tiene algo mosca y es que viendo la VM desde sqldeveloper, en la pestaña de "Vistas Materializadas" me aparece una x roja al lado del nombre de la VM. Aunque está funcionando.

Saludos y gracias.

Bueno me alegro que aunque no te pude dar una solución concreta te haya podido ayudar a encontrale la vuelta.

Saludos.

Muchos años tarde, pero servirá de consulta para alguien más.
Como bien dices Mario, las funciones no te permiten hacer el refresh ON COMMIT, y tampoco el count.
La solución fue correcta, programar el refresh de la tabla periódicamente, o en su defecto desde la aplicación que consulta hacerlo de forma manual.

La liga que encontré que explica de mejor manera este tema es esta:
https://www.plsql.biz/2007/12/el-refresco-de-las-vistas.html