On Demand Refresh

In Oracle, On Demand Refresh refers to the ability to manually trigger the refresh of a materialized view (MV) at any time. This approach provides more control over when the materialized view is refreshed, rather than relying on automatic refresh mechanisms based on a set schedule or events.

An On Demand Refresh is typically used when you want to ensure that the materialized view is up-to-date with the underlying data, but only when you explicitly decide to do so, rather than periodically or automatically.

 

1. What is an On Demand Refresh?

An On Demand Refresh means the user or administrator manually triggers the refresh of a materialized view at a specified time. This can be done at any point based on specific business requirements, like before generating reports, executing critical queries, or after certain data updates have occurred.

 

2. When Should I Use On Demand Refresh?

An On Demand Refresh is useful in scenarios where:

• You want complete control over when the materialized view is refreshed, rather than having it refresh on a fixed schedule or automatically.
• You need the materialized view to reflect the most recent data only when necessary (e.g., before running a particular report or query).
• You need to avoid excessive refreshes when there are only minimal changes to the underlying data, reducing resource consumption.

 

3. How Does On Demand Refresh Work?

Oracle allows you to perform an On Demand Refresh through the DBMS_MVIEW.REFRESH procedure. The refresh will update the materialized view based on the selected refresh method (e.g., Fast, Complete, Force).

The process of refreshing the materialized view manually includes:

• Step 1: You decide when the materialized view needs refreshing.
• Step 2: You manually trigger the refresh using the DBMS_MVIEW.REFRESH procedure.
• Step 3: Oracle executes the refresh, applying changes incrementally (for Fast Refresh) or rebuilding the entire view (for Complete Refresh).

 

4. How to Perform an On Demand Refresh?

To manually refresh a materialized view on demand, you can use the following command:

EXEC DBMS_MVIEW.REFRESH('materialized_view_name');

Where:

• 'materialized_view_name' is the name of the materialized view you want to refresh.
• The default refresh method will be Force (attempts Fast Refresh, and falls back to Complete Refresh if necessary).

For a Complete Refresh or Fast Refresh, you can specify the method explicitly:

• Fast Refresh:

EXEC DBMS_MVIEW.REFRESH('materialized_view_name', 'F');

• Complete Refresh:

EXEC DBMS_MVIEW.REFRESH('materialized_view_name', 'C');

• Force Refresh (default behavior):

EXEC DBMS_MVIEW.REFRESH('materialized_view_name', 'R');

 

5. When Is On Demand Refresh Useful?

On Demand Refresh is especially useful when:

• You need the materialized view to reflect the latest data, but only when needed, not based on an automatic schedule.
• You want to avoid unnecessary refreshes that would consume system resources and potentially affect system performance.
• You are working with large datasets, and you need to ensure that refresh operations occur only at specific points in time, rather than continuously or frequently.
• You need to synchronize data in a materialized view just before critical reporting, analysis, or data extraction.

 

6. How Does On Demand Refresh Differ from Other Refresh Methods?

• On Demand Refresh vs. Scheduled Refresh: In a scheduled refresh, the materialized view is automatically refreshed based on a predefined schedule (e.g., hourly, daily). With On Demand Refresh, you manually initiate the refresh process whenever you deem it necessary.
• On Demand Refresh vs. Automatic Refresh: Oracle allows you to set materialized views to refresh automatically at intervals (for instance, every 15 minutes). With On Demand Refresh, you have more control over the timing of when the refresh should occur.

 

7. How to Schedule an On Demand Refresh?

While On Demand Refresh itself refers to manual initiation, you can combine On Demand Refresh with scheduling to automate when you might want the refresh to occur without constant monitoring.

For example, you could schedule the refresh using DBMS_SCHEDULER to execute at a specified time:

BEGIN

   DBMS_SCHEDULER.create_job (

      job_name        => 'refresh_sales_mv',

      job_type        => 'PLSQL_BLOCK',

      job_action      => 'BEGIN DBMS_MVIEW.REFRESH(''sales_mv'', ''R''); END;',

      start_date      => SYSTIMESTAMP,

      repeat_interval => 'FREQ=DAILY; BYHOUR=0; BYMINUTE=0;',

      enabled         => TRUE

   );

END;

/

This would refresh the sales_mv materialized view daily at midnight.

 

8. Monitoring On Demand Refresh

You can monitor the refresh process using the following methods:

• Check the refresh status: You can use the DBA_MVIEW_REFRESH_TIMES view to check the last refresh times and method used (whether Fast, Complete, or Force).

SELECT * FROM DBA_MVIEW_REFRESH_TIMES

WHERE mview_name = 'YOUR_MVIEW_NAME';

This will show when the most recent refresh occurred, and whether it was a fast or complete refresh.

• Check materialized view logs: Monitor the underlying logs for materialized views, which track changes made to base tables. These logs can help identify why a Fast Refresh might fail and require a Complete Refresh.

 

9. Advantages of On Demand Refresh

• Control Over Timing: You can trigger the refresh exactly when you need it, allowing you to avoid unnecessary resource usage and focus on updates when it’s essential.
• Minimized Resource Consumption: By performing the refresh only when required, you minimize unnecessary overhead, especially if the base tables don’t change frequently.
• Reduced System Load: In busy environments, performing On Demand Refresh ensures that materialized views are updated only when necessary, avoiding refreshes during peak hours.

 

10. Disadvantages of On Demand Refresh

• Manual Effort: On Demand Refresh requires manual intervention or scheduling, which means that someone needs to monitor when the refresh is needed, especially if the underlying data changes frequently.
• Risk of Stale Data: Since the refresh happens only when triggered, there is a risk that the materialized view may become stale if it is not refreshed frequently enough.
• Error-Prone: If the manual process of refreshing is not executed correctly (e.g., forgetting to trigger a refresh before critical reporting), the data in the materialized view might be out of sync with the base tables.

 

11. Best Practices for On Demand Refresh

• Ensure Proper Logging: Maintain proper materialized view logs on the base tables to facilitate fast refresh when it is triggered.
• Monitor Data Changes: Be proactive in monitoring when significant changes occur in the base tables to ensure the materialized view is refreshed as needed.
• Use with Reporting: Use On Demand Refresh just before running reports or performing important analysis, ensuring the data is up-to-date without refreshing unnecessarily.
• Combine with Scheduling for Critical Times: You can combine On Demand Refresh with a scheduler for automatic updates during off-peak hours or for specific events that require up-to-date data.

 

12. Conclusion

An On Demand Refresh allows Oracle users to refresh materialized views manually when needed, providing flexibility and control over when the data in the materialized view is updated. It is beneficial when you need to minimize resource consumption, avoid unnecessary refreshes, and control when the view reflects the latest data. However, it does require careful monitoring and manual effort to avoid having stale data in the materialized view.