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?

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