Clever Geek Handbook

Materialized view: how to automatically update it when table data changes?

Is there a way in Oracle Materialized views to have it automatically update when there are changes to the tables used in the materialized view? What is update mode and which update method should I use? What parameters should I use with Sql Developer?

Thank you in advance

+3


source to share


2 answers


Yes, you can define a materialized view with ON COMMIT

, for example:

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 updated if the last transaction was done on the main table, of course. Since the update is done after every commit, it is highly recommended to use FAST REFRESH

instead COMPLETE

, it will take too long.

You have a few restrictions and prerequisites to use FAST REFRESH

, check the Oracle documentation: CREATE MATERIALIZED VIEW, QUICK paragraph for more details.

+4


source


I don't think there is a way to automatically copy changes to m.view right after they are created. But there are ways to use FAST (incremental) on-demand refresh, you only need to schedule a job for the m.view or or m.view groups to get the refresh done. You can also use m.view log to keep track of all dml and propagate it to m.view with fast refresh on remote database at db link.



If you need changes to be replicated as soon as they are made, I recommend using golden gate or streams (unless you want to have a GG license). Just beware that the oracle has stopped supporting streams in favor of Golden Gate, so if you have any problems, you are on your own. But anyway, it's a pretty solid replication tool, once you get the hang of it.

0


source






More articles:


All Articles