Immediate Refresh FAQs

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

Immediate Refresh refers to:

REFRESH ... ON COMMIT

• MV is refreshed automatically
• Occurs immediately after each COMMIT on the base table
• Keeps MV data synchronized with base tables

Also called real-time or commit-time refresh.

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

CREATE MATERIALIZED VIEW mv_sales_summary

BUILD IMMEDIATE

REFRESH FAST

ON COMMIT

AS

SELECT region_id, SUM(amount) total_sales

FROM sales

GROUP BY region_id;

• BUILD IMMEDIATE → Populates MV immediately
• REFRESH FAST → Incremental refresh
• ON COMMIT → Refreshes after every commit

3. BUILD IMMEDIATE vs REFRESH ON COMMIT

• BUILD IMMEDIATE – Populates MV at creation
• REFRESH ON COMMIT – Controls refresh timing after data changes

Can be combined:

BUILD IMMEDIATE

REFRESH FAST ON COMMIT

4. How Immediate Refresh works internally

1.    Oracle detects changes in base tables

2.    Reads materialized view logs

3.    Applies incremental changes to MV

4.    Completes refresh before commit returns

• User commit waits until MV refresh finishes
• Increases transaction time

5. Requirements for ON COMMIT refresh

• MV log must exist
• FAST refresh must be possible
• MV must satisfy fast refresh restrictions
• No unsupported constructs

Example MV log:

CREATE MATERIALIZED VIEW LOG ON sales

WITH PRIMARY KEY INCLUDING NEW VALUES;

6. ON COMMIT vs ON DEMAND

Feature	ON COMMIT (Immediate)	ON DEMAND (Deferred)
Refresh timing	After every commit	Manual/scheduled
Data freshness	Immediate	Delayed
Transaction impact	High	Low
OLTP suitability	Limited	Recommended

7. When to use Immediate Refresh

Use ON COMMIT when:

• Tables are small
• Low transaction volume
• Real-time reporting required
• Strict consistency needed
• Dimension tables with minimal updates

Avoid ON COMMIT for heavy OLTP or large fact tables.

8. Does ON COMMIT slow transactions?

Yes.

• MV refresh runs inside commit
• Commit waits for refresh
• Additional redo/undo and CPU usage
• Large MVs may cause significant performance impact

9. Can COMPLETE refresh be used with ON COMMIT?

No. ON COMMIT requires FAST refresh.
COMPLETE refresh after each commit is inefficient.

10. What if FAST refresh is not possible?

• ON COMMIT refresh fails
• Commit may throw error
• MV unusable until corrected

11. Does ON COMMIT lock base tables?

• No long-term locks
• Commit duration increases
• Brief internal locking may occur
• High concurrency can cause contention

12. How to check refresh method

SELECT mview_name, refresh_method, refresh_mode

FROM user_mviews;

Refresh modes: COMMIT or DEMAND

13. Limitations of ON COMMIT refresh

• No complex joins in some cases
• Unsupported aggregates not allowed
• Must support FAST refresh
• Certain outer joins and remote tables often restricted

14. Real-World Scenarios

• HR system – small tables, low update frequency → ON COMMIT works well
• E-commerce – high transaction volume → ON COMMIT causes slowdown; not recommended

15. Performance Considerations

• Optimize MV logs
• Use proper indexing
• Keep MV query simple
• Monitor redo generation
• Test under load
• Avoid large fact tables

16. Real-Time Materialized View

• Supports query rewrite with delta changes
• Allows fresh results before refresh
• Different from standard ON COMMIT refresh

17. Common Mistakes

• Using ON COMMIT in high-volume OLTP
• Forgetting MV logs
• Using complex queries
• Ignoring commit latency and performance overhead

18. Immediate vs Deferred Refresh Summary

Feature	Immediate (ON COMMIT)	Deferred (ON DEMAND)
Refresh timing	Automatic	Manual/scheduled
Transaction impact	High	Low
Data accuracy	Real-time	Near real-time
Best for	Small lookup tables	Data warehouse
Scalability	Limited	High

19. Best Practices

• Use ON COMMIT only when needed
• Prefer ON DEMAND for large systems
• Keep MV definition simple
• Ensure FAST refresh compatibility
• Monitor commit time
• Test performance in staging

20. Interview Tip

“What is Immediate refresh in Materialized Views?”

Answer:

“Immediate refresh refers to ON COMMIT refresh, where the materialized view is refreshed automatically every time a transaction commits on the base table. It ensures real-time data consistency but increases commit time and should be used carefully in high-volume systems.”