SQL Snippets ™: Tutorials for Oracle Developers

There are various ways to refresh the data in a materialized view, the simplest way being a complete refresh. When a complete refresh occurs the materialized view's defining query is executed and the entire result set replaces the data currently residing in the materialized view. The REFRESH COMPLETE clause tells Oracle to perform complete refreshes by default when a materialized view is refreshed.

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

Let's see a complete refresh in action now. We will use the DBMS_MVIEW.REFRESH procedure to initiate it. The "list" parameter accepts a list of materialized views to refresh (in our case we only have one) and the "method" parameter accepts a "C", for Complete refresh.

select key, val, rowid from mv ;
 

       KEY VAL   ROWID
---------- ----- ------------------
         1 a     AAAWgHAAEAAAAIEAAA
         2 b     AAAWgHAAEAAAAIEAAB
         3 c     AAAWgHAAEAAAAIEAAC
         4       AAAWgHAAEAAAAIEAAD
 

execute DBMS_MVIEW.REFRESH( LIST => 'MV', METHOD => 'C' );
 

select key, val, rowid from mv ;
 

       KEY VAL   ROWID
---------- ----- ------------------
         1 a     AAAWgHAAEAAAAIEAAE
         2 b     AAAWgHAAEAAAAIEAAF
         3 c     AAAWgHAAEAAAAIEAAG
         4       AAAWgHAAEAAAAIEAAH
 

Note how the rowids in the second query differ from those of the first, even though the data in table T was unchanged throughout. This is because complete refreshes create a whole new set of data, even when the new result set is identical to the old one.

If a materialized view contains many rows and the base table's rows change infrequently refreshing the materialized view completely can be an expensive operation. In such cases it would be better to process only the changed rows. We will explore this type of refresh next.

Cleanup

drop materialized view mv ;