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

Question

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 1

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.