This post was originally published on this site

Materialized Views are often used in data warehouses to improve query performance on aggregated data. But what if it takes too long to refresh the materialized views? Here are some basic rules to improve refresh performance.

Unlike indexes, materialized views are not automatically updated with every data change. 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. 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. How can we reduce this time? This blog post contains some basic rules that should be known to everybody working with materialized views.

Complete Refresh

The simplest form to refresh a materialized view is a Complete Refresh. It loads the contents of a materialized view from scratch. 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. So, the most important part to improve the refresh performance is to improve the SQL statement to load the materialized view. But why is a Complete Refresh running longer than the underlying query, especially for large materialized views?

By default, a Complete Refresh is running within one transaction. 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. During this time, users can still use the materialized view and see the old data. At the end of the refresh, the transaction is committed, and the new data is visible for all users.

The advantage of this behavior is that the users can still use the materialized view while it is refreshed. But the price for this is quite high, because all rows of the materialized view must be deleted with a DELETE command. If the materialized view contains let’s say millions of rows, this can take a long time.

To improve performance of a Complete Refresh, the optional parameter atomic_refresh of the procedure dbms_mview.refresh is very useful. The default is TRUE, which means that the refresh is executed within one single transaction, i.e. with a DELETE and an INSERT statement. If the parameter is set to FALSE, the materialized view is deleted with a much faster TRUNCATE command. The drawback of this method is that no data is visible to the users during the refresh. If this is feasible in your environment, you can use the following command for a Complete Refresh:

BEGIN

   dbms_mview.refresh(‘MV_PROD_YEAR_SALES’, method => ‘C’, atomic_refresh => FALSE);

END;

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. This is also the case for indexes created on the materialized view. If atomic_refresh is set to FALSE, the indexes are set to UNUSABLE at the beginning and rebuilt after the Complete Refresh. During the refresh, index statistics are gathered, too.

Fast Refresh

A more elegant and efficient way to refresh materialized views is a Fast Refresh. With this refresh method, only the changes since the last refresh are applied to the materialized view. The name “Fast Refresh” is a bit misleading, because there may be situations where a Fast Refresh is slower than a Complete Refresh. The name “incremental refresh” would be more appropriate. But in most cases, this method is much faster than a Complete Refresh.

An important precondition for a Fast Refresh is a materialized view log on each of the base tables that are referenced in the materialized view. All columns that are used in the query must be added to the materialized view log. If multiple materialized views are created, only one materialized view log per base table is required, with all columns that are used in at least one of the materialized views.

The following example uses a materialized view on the base tables SALES, TIMES and PRODUCTS. So, three materialized view logs must be created:

CREATE MATERIALIZED VIEW LOG ON sales

  WITH SEQUENCE, ROWID (quantity_sold,amount_sold,prod_id,time_id,cust_id)

  INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON times

  WITH SEQUENCE, ROWID (time_id,calendar_year)

  INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON products

  WITH SEQUENCE, ROWID (prod_id,prod_category)

  INCLUDING NEW VALUES;

In the next step, a materialized view is created. It aggregates sales data per product category and calendar year. The goal is to make this materialized view Fast Refreshable. By the way: If the materialized view is used for query rewrite, it is highly recommended to use the old Oracle join syntax instead of ANSI join syntax (see blog post ANSI Join Syntax and Query Rewrite).

CREATE MATERIALIZED VIEW mv_prod_year_sales

ENABLE QUERY REWRITE

AS

SELECT p.prod_category,

       t.calendar_year,

       SUM(s.amount_sold)

  FROM sales s,

       products p,

       times t

 WHERE s.prod_id = p.prod_id

   AND s.time_id = t.time_id

GROUP BY p.prod_category,

         t.calendar_year;

To test the Fast Refresh behavior, let’s do a (pseudo) update on the product dimension and then try to run a Fast Refresh. The error message ORA-32314 tells us that a Fast Refresh is not possible:

UPDATE products SET prod_id = prod_id WHERE ROWNUM = 1;

COMMIT;

BEGIN

   dbms_mview.refresh(‘MV_PROD_YEAR_SALES’, method => ‘F’);

END;

/

ORA-32314: REFRESH FAST of “ODWH”.”MV_PROD_YEAR_SALES” unsupported after deletes/updates

This is the frustrating part of using materialized views: There are several preconditions to enable Fast Refresh, and if only one of them is missing, the Fast Refresh method does not work. In this case, we get an error message, but if the optional parameter method is omitted, a “Force Refresh” is executed instead. In other words: If a Fast Refresh is not possible, a Complete Refresh is used.

All the restrictions on Fast Refresh are listed in the Oracle documentation. But lazy people like me prefer to use an easier way: The procedure dbms_mview.explain_mview tells us what capabilities are supported of a particular materialized view and – even more important – what is the reason when a feature does not work. The result of the procedure is written to the table MV_CAPABILITIES_TABLE. Before the first usage of the explain procedure, this table must be created with the script utlxmv.sql (available in the $ORACLE_HOME/rdbms/admin directory). The following code example shows how the procedure dbms_mview.explain_mview can be used:

DELETE FROM mv_capabilities_table;

BEGIN

   dbms_mview.explain_mview(‘MV_PROD_YEAR_SALES’);

END;

/

SELECT capability_name, possible, msgtxt, related_text 

  FROM mv_capabilities_table

 WHERE capability_name LIKE ‘REFRESH%’;

CAPABILITY_NAME                P MSGTXT                                                       RELATED_TEXT        

—————————— – ———————————————————— ——————–

REFRESH_COMPLETE               Y                                                                                  

REFRESH_FAST                   Y                                                                                  

REFRESH_FAST_AFTER_INSERT      Y                                                                                  

REFRESH_FAST_AFTER_ONETAB_DML  N SUM(expr) without COUNT(expr)                                SUM(S.AMOUNT_SOLD)  

REFRESH_FAST_AFTER_ONETAB_DML  N COUNT(*) is not present in the select list                                       

REFRESH_FAST_AFTER_ANY_DML     N see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled                     

REFRESH_FAST_PCT               Y                                                                                  

The result of procedure dbms_mview.explain_mview tells us the reasons why a Fast Refresh after an UPDATE is not possible: two additional expressions COUNT(S.AMOUNT_SOLD) and COUNT(*) are required in the query. With this information, we can recreate the materialized view with the required expressions:

DROP MATERIALIZED VIEW mv_prod_year_sales;

CREATE MATERIALIZED VIEW mv_prod_year_sales

ENABLE QUERY REWRITE

AS

SELECT p.prod_category,

       t.calendar_year,

       SUM(s.amount_sold),

       COUNT(s.amount_sold),

       COUNT(*)

  FROM sales s,

       products p,

       times t

 WHERE s.prod_id = p.prod_id

   AND s.time_id = t.time_id

GROUP BY p.prod_category,

         t.calendar_year;

After rerunning procedure dbms_mview.explain_mview we can see that all refresh capabilities are possible now. Now there are no more restrictions that prevent a Fast Refresh.

DELETE FROM mv_capabilities_table;

BEGIN

   dbms_mview.explain_mview(‘MV_PROD_YEAR_SALES’);

END;

/

SELECT capability_name, possible, msgtxt, related_text 

  FROM mv_capabilities_table

 WHERE capability_name LIKE ‘REFRESH%’;

CAPABILITY_NAME                P MSGTXT                                                       RELATED_TEXT        

—————————— – ———————————————————— ——————–

REFRESH_COMPLETE               Y                                                                                  

REFRESH_FAST                   Y                                                                                  

REFRESH_FAST_AFTER_INSERT      Y                                                                                  

REFRESH_FAST_AFTER_ONETAB_DML  Y                                                                                  

REFRESH_FAST_AFTER_ANY_DML     Y                                                                                  

REFRESH_FAST_PCT               Y                                                                                  

Finally, we can repeat our test and see that the materialized view is now updated with a Fast Refresh:

UPDATE products SET prod_id = prod_id WHERE ROWNUM = 1;

COMMIT;

BEGIN

   dbms_mview.refresh(‘MV_PROD_YEAR_SALES’, method => ‘F’);

END;

/

SELECT mview_name, staleness, last_refresh_type

  FROM user_mviews

 WHERE mview_name = ‘MV_PROD_YEAR_SALES’;

MVIEW_NAME           STALENESS           LAST_REFRESH_TYPE

——————– ——————- —————–

MV_PROD_YEAR_SALES   FRESH               FAST             

Further Reading

When you work with materialized views or plan to use them, I highly recommend to read the chapter “Refreshing Materialized Views” in the Data Warehousing Guide of the Oracle documentation. Compared to previous versions of the documentation, the newer versions are easier to understand. Instead of a list of restrictions, the documentation contains now a good sections with Tips for Refreshing Materialized Views.

If you like to read a short and good overview of materialized views with examples of how to use and refresh them, you can find these descriptions in chapter 15 of the book Troubleshooting Oracle Performance, 2nd Edtition of my Trivadis colleague Christian Antognini.