Materialized View: How to automatically refresh it upon table data changes?

sql oracle views oracle-sqldeveloper materialized-views
Jemru picture Jemru · Aug 18, 2014 · Viewed 16.9k times · Source

Is there a way in Oracle Materialized views so that it automatically refresh itself when there are changes on the tables used in the materialized view? What is the Refresh Mode and Refresh Method that I should use? What options should I use using Sql Developer?

Thank you in advance

Answer

Wernfried Domscheit picture Wernfried Domscheit · Aug 18, 2014

Yes, you can define a Materialized View with ON COMMIT, e.g.:

CREATE MATERIALIZED VIEW sales_mv
   BUILD IMMEDIATE
   REFRESH FAST ON COMMIT
   AS SELECT t.calendar_year, p.prod_id ... FROM ...

In this case after every commit the MV is refreshed, provided the last transaction was done on master table, of course. Since refresh is done after each commit it is strongly recommendd to use FAST REFRESH, rather than COMPLETE this would last too long.

You have several restrictions and pre-conditions in order to use FAST REFRESH, check Oracle documentation: CREATE MATERIALIZED VIEW, FAST Clause for details.

Related questions

In Oracle, is it possible to INSERT or UPDATE a record through a view?

In Oracle, is it possible to INSERT or UPDATE a record (a row) through a view?

Read More
Filter "text" column on all_views

Is there any way I could filter the text column on oracle's all_views table? For example: SELECT * FROM ALL_VIEWS WHERE UPPER(TEXT) LIKE '%FOO%'; Exception: ORA-00932: inconsistent datatypes: expected NUMBER got LONG 00932. 00000 - "inconsistent datatypes: expected %…

Read More
Get list of all tables in Oracle?

How do I query an Oracle database to display the names of all tables in it?

Read More