1. What is a COMPLETE Refresh?
COMPLETE Refresh rebuilds the MV by:
- Deleting all rows
- Re-executing the query on base table(s)
Unlike FAST refresh:
- All data is recalculated
- No incremental tracking is needed
Works with any query regardless of complexity.
2. How to create a COMPLETE Refresh MV?
CREATE MATERIALIZED VIEW mv_sales_summary
BUILD IMMEDIATE
REFRESH COMPLETE
ON DEMAND
AS
SELECT region_id, SUM(amount) total_sales
FROM sales
GROUP BY region_id;
- REFRESH COMPLETE → rebuilds MV
- ON DEMAND → manual refresh via DBMS_MVIEW.REFRESH
3. When to use COMPLETE Refresh?
- Query too complex for FAST refresh
- Base tables lack MV logs
- Aggregates/joins not compatible with FAST refresh
- Small tables where performance is acceptable
4. Manual refresh
EXEC DBMS_MVIEW.REFRESH('mv_sales_summary', 'C');
- 'C' = COMPLETE
- Deletes and repopulates MV
5. Scheduled automatic refresh
CREATE MATERIALIZED VIEW mv_sales_summary
REFRESH COMPLETE
START WITH SYSDATE
NEXT SYSDATE + 1
AS
SELECT region_id, SUM(amount) total_sales
FROM sales
GROUP BY region_id;
- Refresh occurs at scheduled time
6. MV Log requirement
- COMPLETE refresh does not require MV logs
- Rebuilds MV from scratch
7. Internal working
1. Deletes all rows in MV
2. Executes MV query on base tables
3. Inserts results into MV
4. Updates MV metadata and refresh timestamp
8. Types of COMPLETE Refresh
1. ON DEMAND – manual or scheduled
2. ON COMMIT – after each commit (not common for large tables)
9. Advantages
- Works with any query
- Simple implementation
- Accurate and consistent
- No MV logs needed
10. Disadvantages
- Slow for large tables
- High CPU and I/O
- MV may be locked during refresh
- Inefficient for frequent refreshes
11. Performance considerations
- Avoid for very large tables if incremental refresh possible
- Schedule during low system usage
- Use indexes on base tables
- Partition large tables if applicable
12. COMPLETE vs FAST Refresh
|
Feature |
COMPLETE Refresh |
FAST Refresh |
|
Data processed |
Entire table |
Only changed rows |
|
Speed |
Slow for large tables |
Fast |
|
Requires MV log |
No |
Yes |
|
Resource usage |
High |
Lower |
|
Query complexity |
No restrictions |
Some restrictions |
|
Recommended for |
Small tables / complex queries |
Large tables with simple changes |
13. Common mistakes
- Using FAST syntax without MV logs
- Refreshing too frequently on large tables
- Not monitoring long-running refreshes
- Ignoring locks on MV during refresh
14. Partitioned tables
- Works with partitions
- Entire MV rebuilt, slower than partition-wise FAST refresh
15. Locks
- Base tables not locked
- MV table may be locked during refresh
- Long refresh may block queries on MV
16. Example
- sales table: 50M rows
- MV aggregates daily sales per region
EXEC DBMS_MVIEW.REFRESH('mv_sales_summary', 'C');
- Deletes all MV rows
- Recalculates SUM(amount) per region
- Re-inserts results
17. Best Practices
- Use ON DEMAND for large tables
- Schedule refresh off-peak
- Index base tables and MV
- Monitor refresh duration and system resources
- Use FAST refresh if incremental updates possible
18. Interview Tip
“COMPLETE refresh rebuilds the MV from scratch by re-executing the query and replacing all rows. It works for any query, does not require MV logs, but can be slow for large tables. Suitable for small tables or complex queries where FAST refresh is not possible.”
Additional resources:
- COMPLETE vs FAST vs FORCE diagram
- Performance tuning checklist
- Case study: large vs small table refresh
No comments:
Post a Comment