Oracle M-Views Refresh Groups
A refresh group can contain materialized views from more than one materialized view group to maintain transactional (read) consistency across replication group boundaries.
To preserve referential integrity and transactional (read) consistency among multiple materialized views, Oracle has the ability to refresh individual materialized views as part of a refresh group. After refreshing all of the materialized views in a refresh group, the data of all materialized views in the group correspond to the same transactionally consistent point in time.
While you may want to define a single refresh group for each materialized view group, it may be more efficient to use one large refresh group that contains objects from multiple materialized view groups. Such a configuration reduces the amount of overhead needed to refresh your materialized views. A refresh group can contain up to 400 materialized views.
One configuration that we want to avoid is using multiple refresh groups to refresh the contents of a single materialized view group. Using multiple refresh groups to refresh the contents of a single materialized view group may introduce inconsistencies in the materialized view data, which may cause referential integrity problems at the materialized view site. Only use this type of configuration when we have in-depth knowledge of the database environment and can prevent any referential integrity problems.
Refresh Group Size
There are a few trade-offs to consider when you are deciding on the size of your refresh groups. Oracle is optimized for large refresh groups. So, large refresh groups refresh faster than an equal number of materialized views in small refresh groups, assuming that the materialized views in the groups are similar. For example, refreshing a refresh group with 100 materialized views is faster than refreshing five refresh groups with 20 materialized views each. Also, large refresh groups enable you to refresh a greater number of materialized views with only one call to the replication management API.
During the refresh of a refresh group, each materialized view in the group is locked at the materialized view site for the amount of time required to refresh all of the materialized views in the refresh group. This locking is required to prevent users from updating the materialized views during the refresh operation, because updates may make the data inconsistent. Therefore, having smaller refresh groups means that the materialized views are locked for less time when you perform a refresh.
Network connectivity must be maintained while performing a refresh. If the connectivity is lost or interrupted during the refresh, then all changes are rolled back so that the database remains consistent. Therefore, in cases where the network connectivity is difficult to maintain, consider using smaller refresh groups.
Advanced Replication includes an optimization for null refresh. That is, if there were no changes to the master tables or master materialized views since the last refresh for a particular materialized view, then almost no extra time is required for the materialized view during materialized view group refresh. However, for materialized views in a database prior to release 8.1, consider separating materialized views of master tables that are not updated often into a separate refresh group of their own. Doing so shortens the refresh time required for other materialized view groups that contain materialized views of master tables that are updated frequently.
On-Demand Refresh
Scheduled materialized view refreshes may not always be the appropriate solution for your environment. For example, immediately following a bulk data load into a master table, dependent materialized views no longer represent the master table's data. Rather than wait for the next scheduled automatic group refreshes, you can manually refresh dependent materialized view groups to immediately propagate the new rows of the master table to associated materialized views.
You may also want to refresh your materialized views on-demand when your materialized views are integrated with a sales force automation system located on a disconnected laptop.
The following example illustrates an on-demand refresh of the hr_refg refresh group:
SQL> EXECUTE DBMS_REFRESH.REFRESH('hr_refg');
Related Oracle Articles: Materialized Views (MViews) Materialized View Logs Oracle Materialized View Types
No comments:
Post a Comment