SQL Snippets ™: Tutorials for Oracle Developers

Now that we know how materialized view logs track changes to base tables we can use them to perform fast materialized view refreshes, i.e. refreshes where only the individual materialized view rows affected by base table changes are updated. This is also called "incremental" refreshing.

Earlier in this tutorial we saw how the rowids for each row in a materialized view changed after a complete refresh. Now let's see what happens to a materialized view's rowids after a fast refresh. First we use the REFRESH FAST clause to specify that the default refresh method should be fast.

create materialized view log on t with sequence ;

create materialized view mv
  REFRESH FAST
  as select * from t
;

select key, val, rowid
from   mv ;
 

       KEY VAL   ROWID
---------- ----- ------------------
         1 a     AAAWm+AAEAAAAaMAAA
         2 b     AAAWm+AAEAAAAaMAAB
         3 c     AAAWm+AAEAAAAaMAAC
         4       AAAWm+AAEAAAAaMAAD
 

Now we refresh the materialized view. The "F" value for the "method" parameter ensures the refresh will be a Fast one.

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

select key, val, rowid
from   mv ;
 

       KEY VAL   ROWID
---------- ----- ------------------
         1 a     AAAWm+AAEAAAAaMAAA
         2 b     AAAWm+AAEAAAAaMAAB
         3 c     AAAWm+AAEAAAAaMAAC
         4       AAAWm+AAEAAAAaMAAD
 

The rowids did not change. Thus, with a fast refresh the materialized view data is not touched when no changes have been made to the base table, unlike a complete refresh where each row would have been created anew.

Now let's update a row in the base table.

update t set val = 'XX'
where  key = 3 ;

commit;

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

select key, val, rowid
from   mv ;
 

       KEY VAL   ROWID
---------- ----- ------------------
         1 a     AAAWm+AAEAAAAaMAAA
         2 b     AAAWm+AAEAAAAaMAAB
         3 XX    AAAWm+AAEAAAAaMAAC
         4       AAAWm+AAEAAAAaMAAD
 

Still no change in the rowids. In row 3 we can see that VAL changed from "c" to "XX" though, telling us that row 3 was updated during the refresh.

Defaults

The REFRESH FAST clause of the CREATE MATERIALIZED VIEW command tells Oracle what type of refresh to perform when no refresh option is specified. A materialized view created with REFRESH FAST can still be refreshed completely if required though. In the following example note how, even though MV was created above with the REFRESH FAST clause, all its rowids change after the refresh. This indicates that a complete refresh was performed.

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

select key, val, rowid
from   mv ;
 

       KEY VAL   ROWID
---------- ----- ------------------
         1 a     AAAWm+AAEAAAAaMAAE
         2 b     AAAWm+AAEAAAAaMAAF
         3 XX    AAAWm+AAEAAAAaMAAG
         4       AAAWm+AAEAAAAaMAAH
 

Similarly a materialized view created with REFRESH COMPLETE can be fast refreshed (assuming the materialized view is capable of being fast refreshed, we'll learn more about this later).

drop materialized view mv ;

create materialized view mv
  REFRESH COMPLETE
  as select * from t
;
 

select key, val, rowid
from   mv ;

       KEY VAL   ROWID
---------- ----- ------------------
         1 a     AAAWnBAAEAAAAaMAAA
         2 b     AAAWnBAAEAAAAaMAAB
         3 XX    AAAWnBAAEAAAAaMAAC
         4       AAAWnBAAEAAAAaMAAD

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

select key, val, rowid
from   mv ;

       KEY VAL   ROWID
---------- ----- ------------------
         1 a     AAAWnBAAEAAAAaMAAA
         2 b     AAAWnBAAEAAAAaMAAB
         3 XX    AAAWnBAAEAAAAaMAAC
         4       AAAWnBAAEAAAAaMAAD
 

Note how none of the rowids in MV changed, indicating a fast refresh.

Cleanup

drop materialized view mv ;

drop materialized view log on t ;

update t set val = 'c' where key = 3 ;
commit ;