On-Demand Refresh of Materialized Views (Mviews)
On-demand refresh refers to the explicit manual triggering of the refresh process for a materialized view (Mview) whenever required. Unlike automatic refresh strategies (such as on commit or on start), an on-demand refresh occurs only when the user or an automated job explicitly requests it.
This refresh type is useful in scenarios where you want more control over when the materialized view is refreshed and avoid the overhead of automatic refreshes.
When to Use On-Demand Refresh:
Scheduled Refreshes: You can trigger the refresh at specific times based on business needs. For example, if the materialized view contains summarized data (e.g., sales totals or call logs), you might refresh it every night or after batch data loads.
Performance Control: If refreshing the Mview frequently impacts performance, you can schedule or trigger the refresh process only when required, for instance, after large inserts or data updates.
Integration with External Systems: When the data in the Mview needs to be synchronized with external processes or data systems, an on-demand refresh allows for more flexibility.
Resource Optimization: If the materialized view’s underlying tables are updated frequently but only certain changes need to be reflected in the view, you can manually trigger the refresh after critical changes, thus optimizing resource usage.
Command for On-Demand Refresh:
To perform an on-demand refresh, you would use the DBMS_MVIEW.REFRESH procedure. Here’s how it works:
Syntax:
BEGIN
DBMS_MVIEW.REFRESH(
list => 'materialized_view_name', -- Materialized View(s) to refresh
method => 'C' -- Refresh Method (C = Complete, F = Fast, etc.)
);
END;
/
list: Specifies the name of the materialized view (or a list of views separated by commas) you want to refresh.
method: Specifies the refresh method, such as:
'C' for Complete Refresh.
'F' for Fast Refresh.
'A' for Automatic (Oracle decides based on conditions).
You can also specify the mode:
ON DEMAND refresh is triggered manually, so no other mode is required.
Example 1: Complete Refresh
BEGIN
DBMS_MVIEW.REFRESH('sales_summary_mview', 'C');
END;
/
This will trigger a complete refresh of the sales_summary_mview.
Example 2: Fast Refresh
BEGIN
DBMS_MVIEW.REFRESH('customer_mview', 'F');
END;
/
This will trigger a fast refresh for the customer_mview, assuming that the Mview log exists and incremental changes can be applied.
Scheduling On-Demand Refresh:
Although on-demand refresh is manually triggered, it can be scheduled automatically using Oracle’s DBMS_SCHEDULER or DBMS_JOB. Here's how you can automate the refresh at specific intervals:
Example: Scheduling Refresh Using DBMS_SCHEDULER
BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'refresh_sales_mview',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN DBMS_MVIEW.REFRESH(''sales_summary_mview'', ''C''); END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'FREQ=DAILY; BYHOUR=2; BYMINUTE=0; BYSECOND=0', -- Refresh at 2:00 AM daily
enabled => TRUE
);
END;
/
This will create a job that refreshes the sales_summary_mview every day at 2:00 AM using a complete refresh.
Advantages of On-Demand Refresh:
Control: It allows you to control when and how the Mview is refreshed, giving you the flexibility to optimize for performance and resource usage.
Efficiency: Refreshing large materialized views only when necessary can avoid unnecessary resource consumption and slowdowns during peak times.
Error Handling: Since you manually trigger the refresh, you can handle any issues or errors that may arise during the refresh process (e.g., resource contention, data anomalies).
Disadvantages of On-Demand Refresh:
Potential for Stale Data: Since the refresh only occurs when triggered, the Mview may become stale if not refreshed frequently enough.
Dependency on Manual Intervention: If not scheduled or automated, on-demand refresh requires manual intervention, which could lead to delays in keeping the data current.
Inconsistent Data States: Without regular or automated refresh, different parts of the application or users may view outdated data at different times.
Conclusion:
On-demand refresh is a useful feature in Oracle that provides flexibility and control over when materialized views are updated. It’s ideal for scenarios where you need to avoid unnecessary refreshes and optimize resource utilization, while still ensuring the materialized view contains the latest data when needed.
Top comments (0)