Create a materialized view on commit with PIVOT function

Question

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?

• Balazs Papp: Provide the table definitions.

Answer 1

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:

• Create it refresh complete on demand
• Run dbms_mview.explain_mview on it to see what the issues are

Create the mv_capabilities_table before doing this to store the output from the explain procedure.

Do this and you'll find:

create table t (
 c1 int 
);
create materialized view log on t 
 with rowid, sequence ( c1 ) including new values;
insert into t values ( 1 );
insert into t values ( 2 );
commit;
create materialized view mv 
refresh complete on demand
as
 select one, two 
 from t
 pivot (
 count(*) for c1 in ( 1 one, 2 two ) 
 );
create table mv_capabilities_table (
 statement_id varchar(30) ,
 mvowner varchar(30) ,
 mvname varchar(30) ,
 capability_name varchar(30) ,
 possible character(1) ,
 related_text varchar(2000) ,
 related_num number ,
 msgno integer ,
 msgtxt varchar(2000) ,
 seq number
) ;
exec dbms_mview.explain_mview('mv');
select capability_name, possible, msgtxt 
from mv_capabilities_table
where capability_name like 'REFRESH%' ;
CAPABILITY_NAME POSSIBLE MSGTXT 
REFRESH_COMPLETE Y <null> 
REFRESH_FAST N <null> 
REFRESH_FAST_AFTER_INSERT N SQL PIVOT or UNPIVOT is present 
REFRESH_FAST_AFTER_INSERT N subquery or named view in FROM list even after view merging 
REFRESH_FAST_AFTER_INSERT N inline view or subquery in FROM list not supported for this type MV 
REFRESH_FAST_AFTER_INSERT N view or subquery in from list 
REFRESH_FAST_AFTER_ONETAB_DML N see the reason why REFRESH_FAST_AFTER_INSERT is disabled 
REFRESH_FAST_AFTER_ANY_DML N see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled 
REFRESH_FAST_PCT N PCT FAST REFRESH is not possible if query contains an inline view

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:

drop materialized view mv ;
create materialized view mv 
refresh complete on demand
as
 select count ( case when c1 = 1 then 1 end ) one, 
 count ( case when c1 = 1 then 1 end ) two 
 from t;
truncate table MV_CAPABILITIES_TABLE;
exec dbms_mview.explain_mview('mv');
select capability_name, possible, msgtxt 
from mv_capabilities_table
where capability_name like 'REFRESH%' ;
CAPABILITY_NAME POSSIBLE MSGTXT 
REFRESH_COMPLETE Y <null> 
REFRESH_FAST Y <null> 
REFRESH_FAST_AFTER_INSERT Y <null> 
REFRESH_FAST_AFTER_ONETAB_DML N CASE expressions present in materialized view 
REFRESH_FAST_AFTER_ANY_DML N see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled 
REFRESH_FAST_PCT N PCT is not possible on any of the detail tables in the materialized view 

Some fast refreshes are now possible. So you can do:

alter materialized view mv 
 refresh fast on commit;
select refresh_method, refresh_mode
from user_mviews
where mview_name = 'MV';
REFRESH_METHOD REFRESH_MODE 
FAST COMMIT 

• Iúri dos Anjos: I managed to change it to "case when" just before I've check your answer and checked explain_mview, but it also says it cant REFRESH_FAST_AFTER_ONETAB_DML because of "case when" clause (as you've showed too). :( I don't know how I can solve this. Is it that important? Thanks anyway.
• Iúri dos Anjos: Is there another way to do a "COUNT IF" statement that REFRESH_FAST_AFTER_ONETAB_DML and REFRESH_FAST_AFTER_INSERT are possible?
• Chris Saxon: Adding count(*) to the query should fix the other refresh_fast limitations (except PCT - which doesn't apply because it's not partitioned)
• Iúri dos Anjos: But using "count(*)" does not give me the same result
• Chris Saxon: You need a count(*) in addition to the count case expressions shown