Complete Refresh

A Complete Refresh in Oracle refers to the process of completely rebuilding a materialized view by re-executing the query that defines the view, effectively discarding the old contents and recalculating the result from scratch. This is in contrast to the Fast Refresh, which only applies incremental changes to the materialized view based on the changes in the base tables.

 

1. What is a Complete Refresh?

A Complete Refresh updates a materialized view by re-executing the underlying query. Unlike Fast Refresh, which only applies changes to the materialized view, Complete Refresh involves recalculating the entire content of the materialized view from the base tables. This ensures that the materialized view is fully synchronized with the current state of the underlying data.

• No Materialized View Log Needed: Complete refresh does not require a materialized view log on the base tables, as it completely regenerates the materialized view.
• Full Recalculation: It re-runs the entire query that defines the materialized view, meaning it retrieves all data again, regardless of whether or not the underlying data has changed.

 

2. When to Use Complete Refresh

You might choose a Complete Refresh in the following situations:

• Complex Queries: If the materialized view is based on a complex query (e.g., involving joins, aggregations, or non-key columns), fast refresh may not be possible or practical. In such cases, a complete refresh is the safest option.
• Base Tables Without Primary Keys: If the base table does not have a primary or unique key, fast refresh cannot track incremental changes, so a complete refresh is required.
• Error Recovery: If there are issues with the materialized view's integrity or if the fast refresh method has failed or is not applicable, performing a complete refresh can ensure data consistency.
• No Requirement for Incremental Updates: If real-time or frequent incremental updates are not required, a complete refresh provides a straightforward approach to ensure data accuracy.

 

3. How to Perform a Complete Refresh

To perform a complete refresh on a materialized view, you can use the DBMS_MVIEW.REFRESH procedure. For example:

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

Where:

• 'sales_mv' is the name of the materialized view.
• 'C' specifies that it is a Complete Refresh.

Alternatively, you can use Force Refresh ('R'), which will attempt a fast refresh first and, if not possible, will fall back to a complete refresh.

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

 

4. Characteristics of Complete Refresh

• Rebuilds Entire View: The materialized view is completely rebuilt by re-running the query that defines it. All rows are re-selected from the base tables, so it will always reflect the most current state of the data.
• No Incremental Updates: Unlike fast refresh, which only applies changes, complete refresh discards the old data and reloads everything.
• Higher Resource Usage: Since it re-executes the query from scratch, a complete refresh consumes more resources (CPU, I/O) and takes longer than a fast refresh, especially for large datasets.

 

5. Complete Refresh Mechanism

When a complete refresh is initiated:

1. Query Execution: The system re-executes the SQL query that defines the materialized view.
2. Data Retrieval: All rows are retrieved from the base tables as per the query and inserted into the materialized view.
3. Old Data Replacement: The old data in the materialized view is replaced with the newly fetched data from the base tables.
4. No Dependency on Logs: No materialized view log is required for a complete refresh. It is completely independent of the changes (inserts, updates, deletes) made to the base tables.

 

6. Performance Considerations for Complete Refresh

• Higher Overhead: Since a complete refresh involves re-executing the entire query and retrieving all data again, it is more resource-intensive than a fast refresh. The time taken for a complete refresh can be significant, especially for large materialized views.
• Indexing: To improve the performance of a complete refresh, ensure that the materialized view and base tables are properly indexed. This can reduce the time needed to retrieve the data.
• Off-Peak Scheduling: Due to the high resource usage, it's common to schedule complete refreshes during off-peak hours to minimize the impact on system performance.

 

7. Limitations of Complete Refresh

• No Incremental Changes: Unlike Fast Refresh, which applies only the changes since the last refresh, Complete Refresh recalculates the entire view, potentially wasting resources when only a small part of the data has changed.
• Longer Refresh Time: The time required for a complete refresh can be much longer than a fast refresh, particularly if the materialized view is based on large tables or complex queries.
• Not Suitable for Real-Time Needs: If you need the materialized view to reflect real-time or near-real-time data changes, a complete refresh may not be practical because it cannot be done as frequently or quickly as a fast refresh.

 

8. Materialized View Refresh Strategies

You can combine Complete Refresh with other refresh strategies to optimize performance:

• Scheduled Refreshes: Use DBMS_SCHEDULER or DBMS_JOB to schedule the refresh to run at off-peak times, reducing the impact on system performance. For example, you might schedule a complete refresh to occur during nightly maintenance windows.

Example:

BEGIN

  DBMS_SCHEDULER.create_job (

    job_name        => 'refresh_sales_mv',

    job_type        => 'PLSQL_BLOCK',

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

    start_date      => SYSTIMESTAMP,

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

    enabled         => TRUE

  );

END;

/

• Mixing Fast and Complete Refresh: You can use Force Refresh ('R') to allow Oracle to first attempt a Fast Refresh and fall back to a Complete Refresh if the fast refresh is not feasible. This ensures that the refresh is optimized for performance while still ensuring data consistency.

 

9. Monitoring and Troubleshooting Complete Refresh

You can monitor the refresh process using views like DBA_MVIEW_REFRESH_TIMES or USER_MVIEW_REFRESH_TIMES, which provide information about the last refresh and whether it was complete or fast.

Example:

SELECT * FROM DBA_MVIEW_REFRESH_TIMES

WHERE mview_name = 'SALES_MV';

This query will show when the last refresh occurred, the type of refresh (complete or fast), and any errors or issues during the process.

 

10. Complete Refresh Best Practices

• Data Volatility: Use Complete Refresh if the underlying data changes in complex or non-incremental ways, or if fast refresh is not possible due to lack of a materialized view log or primary key.
• Scheduling: Schedule complete refreshes during off-peak times to avoid impacting system performance, especially for large datasets.
• Indexing and Optimization: Ensure that both the materialized view and the base tables are properly indexed to optimize query execution time during refresh.
• Use Force Refresh: If you are unsure whether fast refresh is possible, use Force Refresh to attempt a fast refresh first and fall back to a complete refresh if necessary.

 

Summary

• Complete Refresh recalculates the entire materialized view by re-executing the underlying query, ensuring the view is fully synchronized with the base data.
• It is suitable for complex queries, base tables without primary keys, or when a fast refresh isn't possible.
• It is more resource-intensive than a fast refresh, and may require scheduling during off-peak hours for optimal performance.