Materialized Views

ON DEMAND

Up to this point in the tutorial we have always refreshed our materialized views manually with the DBMS_MVIEW.REFRESH command. This is know as ON DEMAND refreshing and it is the default refresh mode when none is specified in the CREATE MATERIALIZED VIEW command. In other words this

create materialized view mv
  as select * from t
;
 

is equivalent to this.

drop materialized view mv ;

create materialized view mv
  REFRESH ON DEMAND
  as select * from t
;
 

To refresh ON DEMAND materialized views we explicitly call one of the following procedures.

Here is an example that uses DBMS_MVIEW.REFRESH.

insert into t values ( 5, 'e' );
commit;
 
select * from mv where key = 5 ;

no rows selected

 
execute DBMS_MVIEW.REFRESH( 'MV' );

select * from mv where key = 5 ;
 
       KEY VAL
---------- -----
         5 e
 

Cleanup

drop materialized view mv ;

delete from t where key = 5 ;
commit;
 



Linking to SQL Snippets ™

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

  • [url=http://www.sqlsnippets.com/en/topic-12890.html]SQL Snippets: Materialized Views - ON DEMAND[/url]

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

  • <a href="http://www.sqlsnippets.com/en/topic-12890.html">SQL Snippets: Materialized Views - ON DEMAND</a>

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

  • Link Text : SQL Snippets: Materialized Views - ON DEMAND
  • URL (href): http://www.sqlsnippets.com/en/topic-12890.html