Data Integrity for Fast Refresh Materialized Views with Aggregates and Updates/Deletes

Here's a hack :-)

Add a GROUP BY and a COUNT

SQL> create table demo_emps (
  2      id number primary key,
  3      name varchar2(255),
  4      sal number
  5  );

Table created.

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

1 row created.

SQL>
SQL> create materialized view log on demo_emps
  2  with rowid, sequence, commit scn, primary key (sal)
  3  including new values;

Materialized view log created.

SQL>
SQL> create materialized view demo_emps_mv
  2  refresh fast on commit
  3  as
  4  select 1 grp, max(sal) max_sal, count(*) c
  5  from demo_emps
  6  group by 1
  7  ;

Materialized view created.

SQL>
SQL> select *
  2  from demo_emps_mv
  3  ;

       GRP    MAX_SAL          C
---------- ---------- ----------
         1        100          1

SQL>
SQL> insert into demo_emps values(2, 'sally', 200);

1 row created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select *
  2  from demo_emps_mv
  3  ;

       GRP    MAX_SAL          C
---------- ---------- ----------
         1        200          2

SQL>
SQL> select staleness
  2  from user_mviews
  3  where 1=1
  4      and mview_name = 'DEMO_EMPS_MV'
  5  ;

STALENESS
-------------------
FRESH

SQL>
SQL> update demo_emps
  2  set sal = sal + 1
  3  ;

2 rows updated.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select *
  2  from demo_emps_mv
  3  ;

       GRP    MAX_SAL          C
---------- ---------- ----------
         1        201          2

SQL>
SQL> select staleness
  2  from user_mviews
  3  where 1=1
  4      and mview_name = 'DEMO_EMPS_MV'
  5  ;

STALENESS
-------------------
FRESH

SQL>
SQL>