Update: thanks to Connor McDonald for pointing out how to fix this issue (see "The Solution" and subsequent explanation below). Connor wrote a followup post based on this article discussing count(*) vs count(1) differences.

The Problem

I recently had a data integrity issue with an Oracle materialized view (MV) in where no errors were raised but the data was not correct in the MV. The following situation is what triggered caused the issue:

• MV with fast refresh

• Underlying query contains an aggregate function (min, max, sum, count, etc)

• An update or delete occurs on base table

The following code snippet highlights the issue:

-- reset
-- drop table demo_emps;
-- drop materialized view demo_emps_mv;

create table demo_emps (
    id number primary key, 
    name varchar2(255),
    sal number
);

insert into demo_emps values(1, 'martin', 100);

-- Required for fast refresh
create materialized view log on demo_emps
with rowid, sequence(id, sal)
including new values
;

-- Will return the max sal
create materialized view demo_emps_mv
refresh fast on commit
as
select max(sal) max_sal
from demo_emps
;

-- Returns 100 (correct)
select *
from demo_emps_mv
;

MAX_SAL 
------- 
    100 

-- Insert a new row
insert into demo_emps values(2, 'sally', 200);

-- Commit required for MV Refresh:
commit;

-- Returns 200 (correct)
select *
from demo_emps_mv
;

MAX_SAL 
------- 
    200 

-- View MV Status
select staleness
from user_mviews
where 1=1
    and mview_name = 'DEMO_EMPS_MV'
;

STALENESS 
--------- 
FRESH     

-- Update rows (add 1 to each salary)
-- Same issue will also occur if a row is deleted
update demo_emps
set sal = sal + 1
;

commit;

-- Should show 201 but shows old 200 (incorrect)
select *
from demo_emps_mv
;

MAX_SAL 
------- 
    200 

-- View MV Status
-- Note: Staleness now shows that it is UNUSABLE
select staleness
from user_mviews
where 1=1
    and mview_name = 'DEMO_EMPS_MV'
;

STALENESS 
--------- 
UNUSABLE

Despite no errors raised the data is clearly not correct. Some thoughts:

• I think the only way to resolve this issue is to create the MV as refresh on demand and do manual refreshes

• It is can be very hard to catch this bug and I recommend monitoring your MVs to ensure the data is correct

  • Update: You can catch this issue but need to analyze the MV (see below)

This issue is documented in MOS 726333.1 Why Does Mview Staleness in Dba_Mview Become Unusable?

For a MV with MIN/MAX or COUNT we only support fast refresh if DML is only insert. On delete/update we won't be able to do fast refresh. For such on-commit mview refresh case we mark it as UNUSABLE.

The Solution

This solution is an excerpt from Connor McDonald sent me.

This can be resolved by modifying the MV to also include count(*) (specifically count(*) and not anything else). Using the above examples:

-- Drop the MV objects 
drop materialized view log on demo_emps;
drop materialized view demo_emps_mv;

-- Rebuilding MV log to include name (not part of fix but needed for grouping)
create materialized view log on demo_emps
with rowid, sequence(id, sal, name)
including new values
;

create materialized view demo_emps_mv
refresh fast on commit
as
select
    name,
    max(sal) max_sal,
    -- Key to solving the issue
    count(*) cnt
from demo_emps
-- Needs to have a "group by" clause (most MVs will)
group by name
;

select *
from demo_emps_mv
;

NAME   MAX_SAL CNT 
------ ------- --- 
martin     101   1 
sally      201   1 

-- Before this update triggered the issue
update demo_emps
set sal = sal + 5
;

commit;

-- Now values in MV are correct
select *
from demo_emps_mv
;

NAME   MAX_SAL CNT 
------ ------- --- 
martin     106   1 
sally      206   1

Explanation

Going back to the first block of code if I rebuild the MV and run dbms_mview.explain_mview to analyze the MV. Some key notes:

• 5006: MV supports insert

• 6007 MV does not other DML

• 6008 Reason why other DML is not supported / solution

drop materialized view demo_emps_mv;

create materialized view demo_emps_mv
refresh fast on commit
as
select max(sal) max_sal
from demo_emps
;

-- Used to store results of dbms_view.explain_mview
-- See https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_MVIEW.html#GUID-DB06F5D8-C883-4443-9123-E1519B44AFA6
truncate table mv_capabilities_table;

exec dbms_mview.explain_mview('DEMO_EMPS_MV');

select 
    seq,
    capability_name,
    possible,
    related_text,
    msgtxt
from mv_capabilities_table
order by seq
;


  SEQ CAPABILITY_NAME               POSSIBLE RELATED_TEXT MSGTXT                                                                             
----- ----------------------------- -------- ------------ ---------------------------------------------------------------------------------- 
    1 PCT                           N                                                                                                        
 1002 REFRESH_COMPLETE              Y                                                                                                        
 2003 REFRESH_FAST                  Y                                                                                                        
 3004 REWRITE                       N                                                                                                        
 4005 PCT_TABLE                     N        DEMO_EMPS    relation is not a partitioned table                                                
 -- INSERTS ARE OK
 5006 REFRESH_FAST_AFTER_INSERT     Y                                                                                                        
 -- OTHER DML NOT OK
 6007 REFRESH_FAST_AFTER_ONETAB_DML N                     mv uses the MIN, MAX or ANY_VALUE aggregate functions                              
 6008 REFRESH_FAST_AFTER_ONETAB_DML N                     COUNT(*) is not present in the select list                                         
 7009 REFRESH_FAST_AFTER_ANY_DML    N                     see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled                       
 8010 REFRESH_FAST_PCT              N                     PCT is not possible on any of the detail tables in the materialized view           
 9011 REWRITE_FULL_TEXT_MATCH       N                     query rewrite is disabled on the materialized view                                 
10012 REWRITE_PARTIAL_TEXT_MATCH    N                     query rewrite is disabled on the materialized view                                 
11013 REWRITE_GENERAL               N                     query rewrite is disabled on the materialized view                                 
12014 REWRITE_PCT                   N                     general rewrite is not possible or PCT is not possible on any of the detail tables 
13015 PCT_TABLE_REWRITE             N        DEMO_EMPS    relation is not a partitioned table

Updating the MV to use the solution (with count(*)) and running the same analysis has the following results:

• Lines 6007 and 6008 aren't present anymore

• REFRESH_FAST_AFTER_ANY_DML is now marked as Y

drop materialized view demo_emps_mv;

create materialized view demo_emps_mv
refresh fast on commit
as
select
    name,
    max(sal) max_sal,
    count(*) cnt
from demo_emps
group by name
;

truncate table mv_capabilities_table;

exec dbms_mview.explain_mview('DEMO_EMPS_MV');

select 
    seq,
    capability_name,
    possible,
    related_text,
    msgtxt
from mv_capabilities_table
order by seq
;


  SEQ CAPABILITY_NAME            POSSIBLE RELATED_TEXT MSGTXT                                                                             
----- -------------------------- -------- ------------ ---------------------------------------------------------------------------------- 
    1 PCT                        N                                                                                                        
 1002 REFRESH_COMPLETE           Y                                                                                                        
 2003 REFRESH_FAST               Y                                                                                                        
 3004 REWRITE                    N                                                                                                        
 4005 PCT_TABLE                  N        DEMO_EMPS    relation is not a partitioned table                                                
 5006 REFRESH_FAST_AFTER_INSERT  Y                                                                                                        
 7007 REFRESH_FAST_AFTER_ANY_DML Y                                                                                                        
 8008 REFRESH_FAST_PCT           N                     PCT is not possible on any of the detail tables in the materialized view           
 9009 REWRITE_FULL_TEXT_MATCH    N                     query rewrite is disabled on the materialized view                                 
10010 REWRITE_PARTIAL_TEXT_MATCH N                     query rewrite is disabled on the materialized view                                 
11011 REWRITE_GENERAL            N                     query rewrite is disabled on the materialized view                                 
12012 REWRITE_PCT                N                     general rewrite is not possible or PCT is not possible on any of the detail tables 
13013 PCT_TABLE_REWRITE          N        DEMO_EMPS    relation is not a partitioned table