Deferred Refresh FAQs

1. What does “Deferred” mean in Materialized Views?

Deferred Refresh means the materialized view is not refreshed immediately after data changes.
It is refreshed:

• Manually
• On schedule (via job)
• When explicitly invoked

Syntax:

REFRESH ... ON DEMAND

Deferred refresh is the same as ON DEMAND refresh.

2. How do you create a Materialized View with Deferred Refresh?

CREATE MATERIALIZED VIEW mv_sales_summary

BUILD IMMEDIATE

REFRESH FAST

ON DEMAND

AS

SELECT region_id, SUM(amount) total_sales

FROM sales

GROUP BY region_id;

• BUILD IMMEDIATE → Populate now
• REFRESH FAST → Incremental refresh
• ON DEMAND → Deferred refresh

3. Deferred vs ON COMMIT

Feature	ON COMMIT	ON DEMAND (Deferred)
Refresh timing	Automatically after commit	Manual or scheduled
Impact on OLTP	High	Low
Data freshness	Immediate	Delayed
Performance	Slower transactions	Better performance

4. How do you manually refresh a Deferred MV?

EXEC DBMS_MVIEW.REFRESH('mv_sales_summary');

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

EXEC DBMS_MVIEW.REFRESH('mv_sales_summary', 'C'); -- Complete

5. Can Deferred refresh be scheduled automatically?

Yes, using DBMS_SCHEDULER:

BEGIN

   DBMS_SCHEDULER.CREATE_JOB (

      job_name        => 'mv_refresh_job',

      job_type        => 'PLSQL_BLOCK',

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

      start_date      => SYSTIMESTAMP,

      repeat_interval => 'FREQ=HOURLY',

      enabled         => TRUE

   );

END;

Schedules: Hourly, daily, weekly.

6. Refresh types in Deferred mode

• FAST Refresh – uses MV logs, incremental updates
• COMPLETE Refresh – rebuilds entire MV
• FORCE Refresh – tries FAST, falls back to COMPLETE

7. Requirement for FAST Deferred refresh

MV logs must exist:                                                                   

CREATE MATERIALIZED VIEW LOG ON sales

WITH PRIMARY KEY INCLUDING NEW VALUES;

8. Internal behavior during Deferred Refresh

1.    Oracle checks staleness

2.    Reads MV logs (FAST)

3.    Applies incremental changes or rebuilds

4.    Updates metadata

5.    Generates redo and undo

Until refresh occurs, MV may be STALE.

9. How to check if MV is stale?

SELECT mview_name, staleness

FROM user_mviews;

Values: FRESH, STALE, NEEDS_COMPILE

10. When should you use Deferred refresh?

Use when:

• OLTP performance is critical
• Large tables
• Real-time accuracy not required
• Data warehouse/reporting systems
• Nightly batch processing

11. Advantages of Deferred refresh

• Reduces OLTP overhead
• Better scalability
• Controlled refresh timing
• Easier batch management
• Improved system performance

12. Disadvantages

• Data may be outdated
• Manual refresh management
• Complex refresh logic in some cases
• FAST refresh restrictions

13. What is BUILD DEFERRED?

CREATE MATERIALIZED VIEW mv_test

BUILD DEFERRED

REFRESH COMPLETE

ON DEMAND

AS

SELECT * FROM employees;

• Does not populate MV at creation
• Data loaded only during first refresh
• Useful for large tables or minimal creation time

14. BUILD DEFERRED vs REFRESH ON DEMAND

Feature	BUILD DEFERRED	REFRESH ON DEMAND
Data populated at creation	No	Yes (if BUILD IMMEDIATE)
Controls refresh timing	No	Yes
First refresh required	Yes	No

15. Real-World Scenario

• E-commerce with 50M daily transactions
• ON COMMIT refresh slows every transaction
• Solution: REFRESH FAST ON DEMAND, schedule every 30 mins
• Result: Faster transactions, near real-time reporting, controlled system load

16. Does Deferred refresh lock base tables?

• Does not lock during normal DML
• Brief locks may occur during refresh
• FAST refresh minimizes locking

17. Can multiple MVs be refreshed together?

EXEC DBMS_MVIEW.REFRESH('mv1, mv2, mv3');

Supports single, multiple, or grouped refresh.

18. Performance considerations

• Use FAST refresh when possible
• Index MVs
• Partition large MVs
• Schedule refresh during low-traffic hours
• Monitor redo/undo
• Avoid overly complex queries

19. Common mistakes

• Forgetting MV logs for FAST refresh
• Refresh too frequently
• Ignoring stale status
• Using ON COMMIT in OLTP
• Not testing refresh duration

20. Interview Tip

“What is Deferred refresh in Materialized Views?”

Answer:

“Deferred refresh means the materialized view is refreshed manually or on schedule using ON DEMAND instead of automatically on commit. It reduces OLTP overhead and is used in data warehouse environments where near real-time data is acceptable.”