No se refresca vista materializada.

[mariomario89]

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:

Código SQL:

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

[huesos52]

Prueba cambiando esto
REFRESH COMPLETE ON DEMAND
por esto
REFRESH COMPLETE ON COMMIT

Nos cuentas

[mariomario89]

Hola huesos52, probé con el cambio que me dijiste pero me ha saltado este error:

Error que empieza en la línea: 1 del comando -

Código SQL:

CREATE MATERIALIZED VIEW "mv_health_rules" ("period", "id_service", "id_event", "total")
TABLESPACE plat_dat
BUILD IMMEDIATE
USING INDEX
REFRESH COMPLETE 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.
 

¿Es posible que no le este gustando que le pase una fecha concreta?
Saludos

[huesos52]

Efectivamente, buscando un poco el problema, no puedes realizar un refresh on commit cuando haces uso de pseudocolumnas tales como SYSDATE o ROWNUM. Porque esto basicamente requiere de la reescritura de la consulta.

Hice pruebas sin hacer uso de SYSDATE y funciona perfecto.

Debes decirnos si la información requiere ser refrescada cada cambio o puede hacerse cada cierto intervalo de tiempo.

Otra opción es utilizar
REFRESH
START WITH SYSDATE NEXT SYSDATE + (1/60)/24
FORCE que actualiza la vista cada minuto y si te permite hacer uso de pseudocolumnas como sysdate.

[mariomario89]

Hola de nuevo huesos52,

Al final la cree con el refresh que me dijistes y parece que funciona correctamente. La he puesto a actualizar cada hora. La idea de esta vista es para monitorear los registros que hubo hoy (desde las 00:00 hasta la hora actual --> sysdate) con el mismo periodo de tiempo del día anterior. Mi idea era que la vista se actualizara cada vez que se llamara a la vista pero este modo tb puede servirme :)

Asi es como ha quedado:

Código SQL:

CREATE MATERIALIZED VIEW "mv_health_rules" ("period", "id_service", "id_event", "total")
TABLESPACE plat_dat
BUILD IMMEDIATE
USING INDEX 
REFRESH 
START WITH SYSDATE NEXT SYSDATE + 1/24 
FORCE
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;
 

Lo unico que me tiene un poco mosca es que cuando miro la vista en el sqldeveloper en la pestaña de Vista Materializadas me sale una x al lado del nombre.

Saludos y muchas gracias

[gnzsoloyo]

@mariomario89: Por favor, usa el HIGHLIGHT "SQL" que tienes disponible en el combo de la edicion de mensajes. Permite visualizar el código con más claridad.
Para eso está...