SQL Snippets ™: Tutorials for Oracle Developers

In REFRESH FAST Categories and DBMS_MVIEW.EXPLAIN_MVIEW we saw an insert-only materialized view which could be fast refreshed after inserts into the base table but needed a complete refresh after other types of DML. With these types of materialized views it is often most convenient to let Oracle decide which refresh method is best. The REFRESH FORCE method does just that. It performs a FAST refresh if possible, otherwise it performs a COMPLETE refresh.

create materialized view log on t2
  with primary key, rowid, sequence ( t_key, amt )
  including new values
;

create materialized view mv
  REFRESH FORCE
  as
    select t_key, max( amt ) amt_max
    from t2
    group by t_key
;

select rowid, t_key, amt_max from mv ;
 

ROWID                   T_KEY    AMT_MAX
------------------ ---------- ----------
AAAWpLAAEAAAAaMAAA          1        300
AAAWpLAAEAAAAaMAAB          2        250
 

First let's try an insert and a refresh.

insert into t2 values ( 5, 2, 500 );
commit;

execute dbms_mview.refresh( list => 'MV' );

select rowid, t_key, amt_max from mv ;
 

ROWID                   T_KEY    AMT_MAX
------------------ ---------- ----------
AAAWpLAAEAAAAaMAAA          1        300
AAAWpLAAEAAAAaMAAB          2        500
 

Since the rowids did not change but the AMT_MAX values did we can tell that a FAST refresh was performed. Now let's try a delete followed by a refresh.

delete from t2 where key = 5 ;
commit;

execute dbms_mview.refresh( list => 'MV' );

select rowid, t_key, amt_max from mv ;
 

ROWID                   T_KEY    AMT_MAX
------------------ ---------- ----------
AAAWpLAAEAAAAaMAAC          1        300
AAAWpLAAEAAAAaMAAD          2        250
 

In the REFRESH FAST Categories topic we received an "ORA-32314: REFRESH FAST of "SCOTT"."MV" unsupported after deletes/updates" error at this point. This time with REFRESH FORCE we did not. Instead Oracle performed a COMPLETE refresh (note how the rowids for each row changed).

Cleanup

drop materialized view mv ;

drop materialized view log on t2 ;