I'm trying to create a materialized view with on commit refresh while using a PIVOT on it's select.
I have the following tables:
- Events: represents an event that the system process. It's like a representation of a job.
- Columns: Id, Type (number)
- EventsMessages: messages of the event. 1:N (1 event can have N messages). It has a type column defining its message type: critical, alert, notification, success.
- Columns: Id, EventId (FK), Message (text)
- EventContainers: containers that have events, so other entities have containers and not a list of event. N:N (1 container han have N event; 1 event is on N containers), the relationship between them is named as EventContainersRelation table.
- Columns for the relation table: EventId (FK), ContainerId (FK). Table EventContainers is not needed in this example.
The code is like this:
CREATE MATERIALIZED VIEW test_mv BUILD IMMEDIATE REFRESH FORCE ON COMMIT AS SELECT DISTINCT * FROM ( SELECT EC.ContainerId AS Id, EM.Type FROM EventMessages EM INNER JOIN Events E ON E.Id = EM.EventId INNER JOIN EventContainersRelation EC ON EC.EventId = E.Id WHERE E.Estado IN (1, 4) ) PIVOT ( COUNT(Type) for tipo in (1 AS CriticalMsgs, 2 AS AlertMsgs, 4 AS NotificationMsgs, 8 AS SuccessMsgs) )
When I run this, I get the following error:
ORA-12054: não é possível definir o atributo de atualização ON COMMIT para a view 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.
The same code works if I use ON DEMAND instead of ON COMMIT.
I've already checked the restrictions on materialized views on https://docs.oracle.com/cd/B28359_01/server.111/b28313/basicmv.htm and I'm not sure what I'm doing wrong. Is there anyway to see a more detailed message? Is there something I'm doing wrong?
Considering I really can't use ON COMMIT here, so while using ON DEMAND, is it a bad thing to make it refreshes everytime it gets inserted, updated or deleted manually in my procedures? (Note that is the almost the same as ON COMMIT, but manually).
Is there another recommended way to achieve this?