Refreshing Materialized View Data in Font

Encoding QR Code in Font Refreshing Materialized View Data

Since a materialized view is defined on underlying master tables, when the data in the master tables changes, the materialized view becomes outdated. To take care of this problem, materialized views are updated, thus keeping them in sync with the master tables. The following sections present the materialized view refresh options.

You can choose between the ON COMMIT and ON DEMAND modes of data refresh. ON COMMIT: In this mode, whenever a data change in one of the master tables is committed, the materialized view is refreshed automatically to reflect the change. ON DEMAND: In this mode, you must execute a procedure like DBMS_MVIEW.REFRESH to update the materialized view. The default refresh mode is ON DEMAND.

You can choose from the following four refresh types: COMPLETE: This refresh option will completely recalculate the query underlying the materialized view. Thus, if the materialized view originally took you 12 hours to build, it ll take about the same time to rebuild it. Obviously, you wouldn t want to use this option each time a few rows are modified, dropped, or inserted into your master tables. FAST: Under the fast refresh mechanism, Oracle will use a materialized view log to log all changes to the master tables. It ll then use the materialized view log to update the master tables, thus avoiding a complete refresh of the view. You can use other techniques to perform a fast refresh, but the materialized view log is the most frequently used device for this purpose. The materialized view log is a table based on the associated materialized view. Each of the tables involved in the join in the materialized view needs its own materialized view log to capture changes to the tables.

FORCE: If you choose this option, Oracle will try to use the FAST refresh mechanism. If it isn t able to use it for some reason, it ll use the COMPLETE refresh method. NEVER: This refresh option never refreshes a materialized view. Obviously, this isn t a viable option for a materialized view whose master tables undergo significant change over time. The default refresh type is FORCE.

Even after you specify the query rewrite mechanism, the Oracle cost-based optimizer may not always automatically rewrite a query, accessing the master tables instead of the materialized view. Thus, even though you have a materialized view, the optimizer ignores it, defeating the purpose of creating and maintaining the materialized view. The Oracle optimizer does this because some conditions for query rewrite may not have been met. You can use the Oracle-supplied DBMS_MVIEW package, to diagnose this and other materialized view problems. You can use the DBMS_MVIEW package s procedures in the following way: Use the EXPLAIN_MVIEW procedure to see what types of query rewrite are possible. Use the EXPLAIN_REWRITE procedure to see why a particular query is not being rewritten to use the materialized view. Use the TUNE_MVIEW procedure to enable a query rewrite. This procedure will suggest how you can rewrite a materialized view to make it eligible for a query rewrite. The TUNE_MVIEW procedure also tells you how to satisfy the requirements for a fast refreshable materialized view. The procedure will take your input and produce a materialized view creation script (and any necessary materialized view logs) that is ready to implement. I discuss the DBMS_MVIEW package in more detail in 24.

In this section, I ll show you how to create a basic materialized view, using some of the options that I described in the previous sections. If you aren t sure about which materialized views to create, you can take advantage of Oracle s SQL Access Advisor, which can make specific recommendations regarding the use of indexes and materialized views. The SQL Access Advisor can design a materialized view and tell you whether it s eligible for a query rewrite. The Using the SQL Access Advisor section, later in this chapter, covers the SQL Access Advisor in detail. There are three steps required to get the materialized views going, although the creation itself is simple: 1. Grant the necessary privileges. 2. Create the materialized view log (assuming you re using the FAST refresh option). 3. Create the materialized view.