SQL> 
SQL> /*
DOC>
DOC>A simple example of on-commit fast refreh MV.
DOC>Make sure the "filter columns" are included in the MV log.
DOC>
DOC>Don't even try to think what ORACLE is doing.
DOC>Just follow what works, when it comes to on commit.
DOC>
DOC>*/
SQL> 
SQL> create table emp_a as select * from emp;

Table created.

SQL> alter table emp_a add constraint emp_a_pk primary key(empno);

Table altered.

SQL> 
SQL> create materialized view log on emp_a
  2  with rowid(empno, sal, deptno)
  3  including new values;

Materialized view log created.

SQL> 
SQL> create materialized view mv_emp_a
  2  build immediate refresh fast on commit
  3  as
  4  select sum(sal), deptno
  5  from emp_a
  6  group by deptno;

Materialized view created.

SQL> 
SQL> select * from mv_emp_a;

  SUM(SAL)     DEPTNO                                                           
---------- ----------                                                           
  20999.99         10                                                           
      6000         20                                                           
      5600         30                                                           

SQL> update emp_a set sal=100 where deptno=10;

3 rows updated.

SQL> commit;

Commit complete.

SQL> 
SQL> -- Did not work
SQL> select * from mv_emp_a;

  SUM(SAL)     DEPTNO                                                           
---------- ----------                                                           
  20999.99         10                                                           
      6000         20                                                           
      5600         30                                                           

SQL> 
SQL> -- Have to do it this way
SQL> drop materialized view mv_emp_a;

Materialized view dropped.

SQL> create materialized view mv_emp_a
  2  build immediate refresh fast on commit
  3  as
  4  select count(*), sum(sal), deptno, count(sal)
  5  from emp_a
  6  group by deptno;

Materialized view created.

SQL> 
SQL> select * from mv_emp_a;

  COUNT(*)   SUM(SAL)     DEPTNO COUNT(SAL)                                     
---------- ---------- ---------- ----------                                     
         3        300         10          3                                     
         2       6000         20          2                                     
         4       5600         30          4                                     

SQL> 
SQL> update emp_a set sal=40 where deptno=10;

3 rows updated.

SQL> select count(1) from mlog$_emp_a;

  COUNT(1)                                                                      
----------                                                                      
         6                                                                      

SQL> commit;

Commit complete.

SQL> select count(1) from mlog$_emp_a;

  COUNT(1)                                                                      
----------                                                                      
         0                                                                      

SQL> select * from mv_emp_a;

  COUNT(*)   SUM(SAL)     DEPTNO COUNT(SAL)                                     
---------- ---------- ---------- ----------                                     
         3        120         10          3                                     
         2       6000         20          2                                     
         4       5600         30          4                                     

SQL> 
SQL> drop materialized view mv_emp_a;

Materialized view dropped.

SQL> drop materialized view log on emp_a;

Materialized view log dropped.

SQL> drop table emp_a;

Table dropped.

SQL> 
SQL> spool off