Refreshing Materialized Views

When creating a materialized view, you have the option of specifying whether the
refresh occurs ON DEMAND or ON COMMIT. In the case of ON COMMIT, the materialized
view is changed every time a transaction commits, thus ensuring that the materialized
view always contains the latest data. Alternatively, you can control the time when
refresh of the materialized views occurs by specifying ON DEMAND. In this case, the
materialized view can only be refreshed by calling one of the procedures in the DBMS_
MVIEW package.

DBMS_MVIEW provides three different types of refresh operations.

■ DBMS_MVIEW.REFRESH
Refresh one or more materialized views.
■ DBMS_MVIEW.REFRESH_ALL_MVIEWS
Refresh all materialized views.
■ DBMS_MVIEW.REFRESH_DEPENDENT
Refresh all materialized views that depend on a specified master table or
materialized view or list of master tables or materialized views.

Performing a refresh operation requires temporary space to rebuild the indexes and
can require additional space for performing the refresh operation itself. Some sites
might prefer not to refresh all of their materialized views at the same time: as soon as some underlying detail data has been updated, all materialized views using this data will become stale. Therefore, if you defer refreshing your materialized views, you can either rely on your chosen rewrite integrity level to determine whether or not a stale materialized view can be used for query rewrite, or you can temporarily disable query rewrite with an ALTER SYSTEM SET QUERY_REWRITE_ENABLED = FALSE
statement.

After refreshing the materialized views, you can re-enable query rewrite as
the default for all sessions in the current database instance by specifying ALTER
SYSTEM SET QUERY_REWRITE_ENABLED as TRUE. Refreshing a materialized view
automatically updates all of its indexes. In the case of full refresh, this requires
temporary sort space to rebuild all indexes during refresh. This is because the full
refresh truncates or deletes the table before inserting the new full data volume. If
insufficient temporary space is available to rebuild the indexes, then you must
explicitly drop each index or mark it UNUSABLE prior to performing the refresh
operation.
If you anticipate performing insert, update or delete operations on tables referenced
by a materialized view concurrently with the refresh of that materialized view, and
that materialized view includes joins and aggregation, Oracle recommends you use ON
COMMIT fast refresh rather than ON DEMAND fast refresh.


Complete Refresh
A complete refresh occurs when the materialized view is initially defined as BUILD
IMMEDIATE, unless the materialized view references a prebuilt table. For materialized
views using BUILD DEFERRED, a complete refresh must be requested before it can be
used for the first time. A complete refresh may be requested at any time during the life of any materialized view. The refresh involves reading the detail tables to compute the results for the materialized view. This can be a very time-consuming process, especially if there are huge amounts of data to be read and processed.

Therefore, you should always consider the time required to process a complete refresh before requesting it.

There are, however, cases when the only refresh method available for an already built
materialized view is complete refresh because the materialized view does not satisfy
the conditions specified in the following section for a fast refresh.



Fast Refresh
Most data warehouses have periodic incremental updates to their detail data.You can use the SQL*Loader or any bulk load utility to perform incremental loads of detail data. Fast refresh of your materialized views is usually efficient, because instead of having to recompute the entire materialized view, the changes are applied to the existing data Thus, processing only the changes can result in a very fast refresh time.


Partition Change Tracking (PCT) Refresh
When there have been some partition maintenance operations on the detail tables, this
is the only method of fast refresh that can be used.

In the absence of partition maintenance operations on detail tables, when you request
a FAST method (method => 'F') of refresh through procedures in DBMS_MVIEW
package, Oracle will use a heuristic rule to try log-based rule fast refresh before
choosing PCT refresh. Similarly, when you request a FORCE method (method =>
'?'), Oracle will choose the refresh method based on the following attempt order:
log-based fast refresh, PCT refresh, and complete refresh. Alternatively, you can
request the PCT method (method => 'P'), and Oracle will use the PCT method
provided all PCT requirements are satisfied.


ON COMMIT Refresh
A materialized view can be refreshed automatically using the ON COMMIT method.
Therefore, whenever a transaction commits which has updated the tables on which a
materialized view is defined, those changes will be automatically reflected in the
materialized view. The advantage of using this approach is you never have to
remember to refresh the materialized view. The only disadvantage is the time required
to complete the commit will be slightly longer because of the extra processing
involved. However, in a data warehouse, this should not be an issue because there is
unlikely to be concurrent processes trying to update the same table.


Manual Refresh Using the DBMS_MVIEW Package
When a materialized view is refreshed ON DEMAND, one of four refresh methods can be
specified as shown in the following table. You can define a default option during the
creation of the materialized view.



Three refresh procedures are available in the DBMS_MVIEW package for performing ON
DEMAND refresh. Each has its own unique set of parameters.


Refresh Specific Materialized Views with REFRESH

Use the DBMS_MVIEW.REFRESH procedure to refresh one or more materialized views.
Some parameters are used only for replication, so they are not mentioned here. The
required parameters to use this procedure are:

■ The comma-delimited list of materialized views to refresh
■ The refresh method: F-Fast, P-Fast_PCT, ?-Force, C-Complete
■ The rollback segment to use
■ Refresh after errors (TRUE or FALSE)
A Boolean parameter. If set to TRUE, the number_of_failures output
parameter will be set to the number of refreshes that failed, and a generic error
message will indicate that failures occurred. The alert log for the instance will give details of refresh errors. If set to FALSE, the default, then refresh will stop after it encounters the first error, and any remaining materialized views in the list will not be refreshed.
The following four parameters are used by the replication process. For warehouse
refresh, set them to FALSE, 0,0,0.
■ Atomic refresh (TRUE or FALSE)
If set to TRUE, then all refreshes are done in one transaction. If set to FALSE, then
the refresh of each specified materialized view is done in a separate transaction. If
set to FALSE, Oracle can optimize refresh by using parallel DML and truncate
DDL on a materialized views.


For example, to perform a fast refresh on the materialized view cal_month_sales_
mv, the DBMS_MVIEW package would be called as follows:

DBMS_MVIEW.REFRESH('CAL_MONTH_SALES_MV', 'F', '', TRUE, FALSE, 0,0,0, FALSE);


Multiple materialized views can be refreshed at the same time, and they do not all
have to use the same refresh method. To give them different refresh methods, specify
multiple method codes in the same order as the list of materialized views (without
commas). For example, the following specifies that cal_month_sales_mv be
completely refreshed and fweek_pscat_sales_mv receive a fast refresh:

DBMS_MVIEW.REFRESH('CAL_MONTH_SALES_MV, FWEEK_PSCAT_SALES_MV', 'CF', '',
TRUE, FALSE, 0,0,0, FALSE);

If the refresh method is not specified, the default refresh method as specified in the materialized view definition will be used.



Refresh All Materialized Views with REFRESH_ALL_MVIEWS
An alternative to specifying the materialized views to refresh is to use the procedure DBMS_MVIEW.REFRESH_ALL_MVIEWS. This procedure refreshes all materialized
views. If any of the materialized views fails to refresh, then the number of failures is reported.

The parameters for this procedure are:

■ The number of failures (this is an OUT variable)
■ The refresh method: F-Fast, P-Fast_PCT, ?-Force, C-Complete
■ Refresh after errors (TRUE or FALSE)
A Boolean parameter. If set to TRUE, the number_of_failures output
parameter will be set to the number of refreshes that failed, and a generic error
message will indicate that failures occurred. The alert log for the instance will give details of refresh errors. If set to FALSE, the default, then refresh will stop after it encounters the first error, and any remaining materialized views in the list will not be refreshed.
■ Atomic refresh (TRUE or FALSE)

If set to TRUE, then all refreshes are done in one transaction. If set to FALSE, then
the refresh of each specified materialized view is done in a separate transaction. If
set to FALSE, Oracle can optimize refresh by using parallel DML and truncate
DDL on a materialized views.

An example of refreshing all materialized views is the following:

DBMS_MVIEW.REFRESH_ALL_MVIEWS(failures,'C','', TRUE, FALSE);



Refresh Dependent Materialized Views with REFRESH_DEPENDENT
The third procedure, DBMS_MVIEW.REFRESH_DEPENDENT, refreshes only those
materialized views that depend on a specific table or list of tables. For example,
suppose the changes have been received for the orders table but not for customer
payments. The refresh dependent procedure can be called to refresh only those
materialized views that reference the orders table.

The parameters for this procedure are:

■ The number of failures (this is an OUT variable)
■ The dependent table
■ The refresh method: F-Fast, P-Fast_PCT, ?-Force, C-Complete
■ The rollback segment to use
■ Refresh after errors (TRUE or FALSE)
A Boolean parameter. If set to TRUE, the number_of_failures output
parameter will be set to the number of refreshes that failed, and a generic error
message will indicate that failures occurred. The alert log for the instance will give details of refresh errors. If set to FALSE, the default, then refresh will stop after it encounters the first error, and any remaining materialized views in the list will not be refreshed.
■ Atomic refresh (TRUE or FALSE)
If set to TRUE, then all refreshes are done in one transaction. If set to FALSE, then
the refresh of each specified materialized view is done in a separate transaction. If
set to FALSE, Oracle can optimize refresh by using parallel DML and truncate
DDL on a materialized views.
■ Whether it is nested or not
If set to TRUE, refresh all the dependent materialized views of the specified set of
tables based on a dependency order to ensure the materialized views are truly
fresh with respect to the underlying base tables.

Posted in: Materialized Views