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?
Best Answer
To use on commit refreshes, an MV must also be fast refreshable.
If you're struggling to figure out why you can't set certain refresh properties for an MV, it's best to:
refresh complete on demand
dbms_mview.explain_mview
on it to see what the issues areCreate the mv_capabilities_table before doing this to store the output from the explain procedure.
Do this and you'll find:
As the REFRESH_FAST_AFTER_INSERT line says, SQL PIVOT or UNPIVOT is present. So you can't use on commit.
You can get around this by using old-style manual pivots:
Some fast refreshes are now possible. So you can do: