DEFERRED REFRESH

1. Why Use DEFERRED Refresh?

Using a DEFERRED refresh has several advantages:

• Performance: In scenarios where the data changes frequently but real-time consistency isn’t essential, deferring the refresh can significantly improve performance by reducing the load on the system.
• Reduced Locking: Real-time refreshes might involve locks on the tables or materialized views, but with a deferred refresh, these operations are minimized.
• Batch Processing: If you need to handle large updates or complex queries in batches, deferring refresh can be beneficial. For example, refreshing the view once a night after the day's transactions are completed.

2. Implementation of Deferred Refresh:

To implement a deferred refresh for a materialized view, you typically define it when you create or alter the view. Here’s a basic example:

CREATE MATERIALIZED VIEW sales_mv

   BUILD IMMEDIATE

   REFRESH DEFERRED

   AS

   SELECT * FROM sales;

In this example, the materialized view sales_mv is created, and its refresh is set to DEFERRED, meaning that it won't automatically refresh. You can explicitly refresh it when needed using the DBMS_MVIEW.REFRESH procedure.

3. Refresh Mechanism:

Once you have set up a DEFERRED refresh, you can refresh the materialized view using the following command:

EXEC DBMS_MVIEW.REFRESH('sales_mv');

You can also specify the refresh type (FAST, COMPLETE, FORCE) if needed.

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

Where:

• 'C' stands for Complete refresh.

4. Considerations When Using Deferred Refresh:

• Data Staleness: Since the data in the materialized view is not refreshed in real-time, there will be a delay in reflecting changes in the underlying tables. This can be problematic if the data needs to be highly up-to-date for reporting or real-time analytics.
• Manual Control: You are responsible for when the refresh happens. If you forget to refresh the materialized view, users may be querying outdated data.
• Complexity in Maintenance: Managing refresh schedules manually may require more administrative overhead, especially in complex environments with multiple materialized views or dependencies.

5. Scheduling Deferred Refresh:

You can automate the refresh using Oracle's DBMS_SCHEDULER to schedule the refresh at a specific time or interval. Here's an example of how to schedule a refresh:

BEGIN

   DBMS_SCHEDULER.create_job (

      job_name        => 'refresh_sales_mv',

      job_type        => 'PLSQL_BLOCK',

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

      start_date      => SYSTIMESTAMP,

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

      enabled         => TRUE

   );

END;

/

This example creates a job that refreshes the sales_mv materialized view daily at 2:00 AM.

6. DEFERRED Refresh vs Immediate Refresh:

• Immediate Refresh: As soon as a change is made in the base table, the materialized view is refreshed automatically. It can affect performance, especially in high-transaction environments.
• Deferred Refresh: The refresh is postponed to a later time, which can help in reducing performance overhead but requires careful management to ensure data is up-to-date when needed.

7. Monitoring Refreshes:

You can check the status of the materialized view refresh by querying the DBA_MVIEW_REFRESH_TIMES or USER_MVIEW_REFRESH_TIMES view. It will show when the last refresh occurred.

SELECT * FROM DBA_MVIEW_REFRESH_TIMES

WHERE mview_name = 'SALES_MV';

This can help you track the refresh history and identify if any refresh jobs were missed or delayed.

Summary:

• A DEFERRED refresh allows manual control over when materialized views are updated.
• It is useful for performance optimization and situations where real-time consistency isn’t a strict requirement.
• The refresh can be triggered using DBMS_MVIEW.REFRESH or scheduled with Oracle Scheduler.
• Careful management is necessary to ensure the materialized view doesn’t become outdated for users relying on the data.

Let me know if you need more examples or further clarification!