Materialized Views


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
  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.


drop materialized view mv ;

Linking to SQL Snippets ™

To link to this page in Oracle Technology Network Forums or OraFAQ Forums cut and paste this code.

  • [url=]SQL Snippets: Materialized Views - REFRESH COMPLETE[/url]

To link to this page in HTML documents or Blogger comments cut and paste this code.

  • <a href="">SQL Snippets: Materialized Views - REFRESH COMPLETE</a>

To link to this page in other web sites use the following values.

  • Link Text : SQL Snippets: Materialized Views - REFRESH COMPLETE
  • URL (href):