FORCE Refresh FAQS

1. What is FORCE Refresh?

FORCE Refresh automatically chooses the best method:

• Uses FAST refresh if possible (MV logs exist, query supports incremental updates)
• Falls back to COMPLETE refresh if FAST is not possible

Ensures the MV is always up-to-date.

2. Creating a FORCE Refresh MV

CREATE MATERIALIZED VIEW mv_sales_summary

BUILD IMMEDIATE

REFRESH FORCE

ON DEMAND

AS

SELECT region_id, SUM(amount) total_sales

FROM sales

GROUP BY region_id;

• REFRESH FORCE → Oracle selects method
• ON DEMAND → manual refresh

3. When to use FORCE Refresh

• Automatic fallback to COMPLETE if FAST is unavailable
• Unsure whether MV logs exist
• Query sometimes allows incremental updates

4. Manual refresh

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

• 'F' = FORCE
• Oracle attempts FAST; otherwise, COMPLETE

5. Internal working

1.    Check if FAST refresh is feasible (MV logs exist, query compatible)

2.    If yes → FAST refresh

3.    If no → COMPLETE refresh

4.    Update MV metadata and timestamp

6. MV Log requirement

• Not strictly required
• If MV logs exist → FAST refresh
• Otherwise → COMPLETE refresh

7. Scheduled automatic refresh

CREATE MATERIALIZED VIEW mv_sales_summary

REFRESH FORCE

START WITH SYSDATE

NEXT SYSDATE + 1

AS

SELECT region_id, SUM(amount) total_sales

FROM sales

GROUP BY region_id;

• Refresh occurs automatically
• Oracle chooses FAST or COMPLETE

8. Advantages

• Ensures MV is always refreshed
• Combines speed of FAST and robustness of COMPLETE
• Reduces manual intervention
• Works with varied queries

9. Disadvantages

• Slower than pure FAST if fallback occurs
• Slight overhead checking FAST feasibility
• MV may lock during COMPLETE fallback

10. Performance considerations

• Use for medium-sized tables or mixed workloads
• Schedule off-peak
• Maintain MV logs to favor FAST
• Monitor refresh duration

11. FORCE vs FAST vs COMPLETE

Feature	FAST Refresh	COMPLETE Refresh	FORCE Refresh
Refresh method	Incremental	Full rebuild	FAST if possible, else COMPLETE
Requires MV log	Yes	No	Optional
Speed	Fast	Slow for large tables	Fast if FAST possible
Query restrictions	Some	None	Same as FAST + fallback
Recommended use	Frequent incremental updates	Complex or small tables	Unknown or mixed scenarios

12. Common mistakes

• Assuming FORCE always uses FAST
• No MV logs → silent fallback to COMPLETE
• Refreshing too frequently on large tables
• Ignoring locks during COMPLETE fallback

13. Example

• sales table has some MV logs
• MV aggregates total sales per region

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

• Oracle attempts FAST refresh if possible
• Falls back to COMPLETE if necessary

14. Best Practices

• Maintain MV logs on frequently updated tables
• Use ON DEMAND for large tables
• Monitor refresh times and resource usage
• Index base tables for faster COMPLETE fallback
• Consider partitioning large tables

15. Interview Tip

“FORCE refresh attempts FAST first, then COMPLETE if FAST is not possible. It ensures the MV is always updated, combining speed and robustness.”