This content has been marked as final. Show 8 replies
when updating or inserting / deleting data of some specific tables, my materialized view should be updated.
-> Yes it does get updated , if you have created materialized view as "REFRESH ON COMMIT" else you need to do a manual refresh using DBMS_SNAPSHOT.REFRESH or dbms_mview.refresh
Thanks for your answers,
the case with FAST REFRESH doesn't work. Our materialized are very complex and are using e.g subselect-statments, analytic functions,UNION, Order, Group by.... According to the documentation, we can't use the FAST REFRESH.
Using the REFRESH ON COMMIT could be possible. Having a look at the documentation, there is one interesting sentence: A REFRESH ON COMMIT materialized view is refreshed automatically when a transaction that does DML to one of the materialized view's detail tables commits
What are the materialized view's detail tables? Are that all tables in the FROM-Part of the query?
Based how often you like to see new data for reporting / querying on MV, you have to decide refresh frequency. You have to keep it in mind that it adds load to your DB if you set it REFRESH ON COMMIT (but that's the way if you like to see the change immediately).
REFRESH FAST ON COMMIT or REFRESH FAST NEXT <<INTERVAL>>.
FAST refresh has limitations
As it seems i can't use the REFRESH ON COMMIT functionality on my materialized views, because they are to complex (see my last answer)
So, I am at the beginning of the thread again: how can I update my materialized views, when DML-statements are used on some specific tables.
Using manually the functions DBMS_SNAPSHOT.REFRESH or dbms_mview.refresh could be possible. But inserting them in a trigger isn't such a good idea, so far as i know.
Thanks for helping,
Edited by: Martin_L on Mar 4, 2013 5:00 AM
Edited by: Martin_L on Mar 4, 2013 5:01 AM