Fast RefreshĪ more elegant and efficient way to refresh materialized views is a Fast Refresh. During the refresh, index statistics are gathered, too. If atomic_refresh is set to FALSE, the indexes are set to UNUSABLE at the beginning and rebuilt after the Complete Refresh. This is also the case for indexes created on the materialized view. Since Oracle 12c, there is a nice side effect of this refresh method: Because of Online Statistics Gathering, statistics are calculated on the materialized view automatically. If this is feasible in your environment, you can use the following command for a Complete Refresh:ĭbms_mview.refresh(‘MV_PROD_YEAR_SALES’, method => ‘C’, atomic_refresh => FALSE ) The drawback of this method is that no data is visible to the users during the refresh. If the parameter is set to FALSE, the materialized view is deleted with a much faster TRUNCATE command. The default is TRUE, which means that the refresh is executed within one single transaction, i.e. To improve performance of a Complete Refresh, the optional parameter atomic_refresh of the procedure dbms_mview.refresh is very useful. If the materialized view contains let’s say millions of rows, this can take a long time. But the price for this is quite high, because all rows of the materialized view must be deleted with a DELETE command.
The advantage of this behavior is that the users can still use the materialized view while it is refreshed. At the end of the refresh, the transaction is committed, and the new data is visible for all users. During this time, users can still use the materialized view and see the old data. At the beginning of a Complete Refresh, the old data of the materialized view is deleted, Then, the new data is inserted by running the underlying SQL query. But why is a Complete Refresh running longer than the underlying query, especially for large materialized views?īy default, a Complete Refresh is running within one transaction. So, the most important part to improve the refresh performance is to improve the SQL statement to load the materialized view. This means, if the SQL query of the materialized view has an execution time of two hours, the Complete Refresh takes at least two hours as well – or ofter even longer. It loads the contents of a materialized view from scratch. The simplest form to refresh a materialized view is a Complete Refresh. How can we reduce this time? This blog post contains some basic rules that should be known to everybody working with materialized views. But what happens if the refresh of a materialized view takes a lot of time? In several performance reviews for customers, I have seen materialized views that need hours or even days(!) to refresh. They must explicitly be refreshed, either on every commit, on a periodically time schedule or – typically in data warehouses – at the end of an ETL job. Unlike indexes, materialized views are not automatically updated with every data change.
But what if it takes too long to refresh the materialized views? Here are some basic rules to improve refresh performance. Materialized Views are often used in data warehouses to improve query performance on aggregated data.
0 kommentar(er)
