ON COMMIT Refresh FAQS

1. What is ON COMMIT refresh?

ON COMMIT refresh means the materialized view (MV) is automatically refreshed whenever a transaction commits on the base table(s).

• Ensures the MV always has the latest committed data
• Ideal for real-time reporting or summary tables

2. Creating an ON COMMIT MV

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;

• REFRESH FAST ON COMMIT → incremental, automatic refresh
• Requires MV logs on base tables

3. ON COMMIT vs ON DEMAND

 

Feature	ON COMMIT	ON DEMAND
Triggered by	Automatic at COMMIT	Manual execution
Data freshness	Always up-to-date	May be stale until refresh
Performance impact	Adds overhead to DML commits	No impact until refresh
Use case	Real-time summaries	Large tables, batch reporting

4. Prerequisites

• MV logs must exist on all base tables
• MV must be FAST refreshable
• Supports DML operations (INSERT, UPDATE, DELETE)

Example MV log:

CREATE MATERIALIZED VIEW LOG ON sales

WITH ROWID, SEQUENCE (region_id, amount)

INCLUDING NEW VALUES;

5. How it works internally

1.    DML occurs on base table

2.    Oracle records changes in MV log

3.    On commit, Oracle updates the MV automatically

4.    MV is consistent with committed base table data

6. Can ON COMMIT use COMPLETE refresh?

• No, ON COMMIT requires FAST refresh
• COMPLETE refresh rebuilds the MV entirely and is too heavy

7. Advantages

• MV always has fresh data
• Ideal for real-time reporting
• Works with transactional applications

8. Disadvantages

• Adds overhead to every commit
• May slow DML for large tables
• Only supports FAST refreshable MVs
• Cannot handle very complex queries

9. Performance considerations

• Use for small to medium tables
• Large tables with frequent DML → slows transactions
• Optimize indexes and MV logs
• Monitor commit times

10. Check last refresh

SELECT last_refresh_type, last_refresh_date

FROM user_mviews

WHERE mview_name = 'MV_SALES_SUMMARY';

• last_refresh_type → FAST
• last_refresh_date → timestamp of last refresh

11. Common mistakes

• Missing MV logs → FAST refresh fails
• Using ON COMMIT with large tables
• Expecting non-FAST refreshable queries to work
• Not monitoring commit performance

12. Best practices

• Use when real-time data is critical
• Maintain minimal MV log columns
• Avoid complex joins on high DML tables
• Monitor system performance
• Combine with ON DEMAND MVs for large summary reporting

13. Interview Tip

“ON COMMIT refresh updates the materialized view automatically whenever a transaction commits on its base table. It ensures fresh data using FAST refresh and MV logs. Ideal for real-time reporting but may slow DML on large tables.”