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.”

 

ON DEMAND Refresh FAQS

1. What is ON DEMAND refresh?

ON DEMAND refresh means the materialized view (MV) is refreshed only when explicitly requested.

• You control when the data is refreshed.
• Useful for large tables or reports that don’t need real-time data.

2. Creating an ON DEMAND MV

CREATE MATERIALIZED VIEW mv_sales_summary

BUILD IMMEDIATE

REFRESH ON DEMAND

AS

SELECT region_id, SUM(amount) total_sales

FROM sales

GROUP BY region_id;

• REFRESH ON DEMAND → no automatic refresh
• BUILD IMMEDIATE → initial data populated

3. Manual refresh

EXEC DBMS_MVIEW.REFRESH('mv_sales_summary');

• Specify method: 'F' = FORCE, 'C' = COMPLETE, 'S' = FAST

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

4. When to use ON DEMAND

• Data does not need real-time updating
• Large tables where automatic refresh is resource-intensive
• Reports or summaries updated periodically

5. ON DEMAND vs ON COMMIT

Feature	ON DEMAND	ON COMMIT
Triggered by	Manual execution	Automatic at COMMIT
Control	Full control	Automatic
Performance impact	Minimal until refresh	May slow DML
Use case	Large tables, batch reporting	Real-time summaries, small tables

6. Refresh methods

• FORCE ('F') → FAST if possible, else COMPLETE
• COMPLETE ('C') → full rebuild
• FAST ('S') → incremental; requires MV logs

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

7. How it works internally

1.    Issue refresh command

2.    Oracle checks refresh method: FAST, COMPLETE, or FORCE

3.    If FAST → applies incremental changes from MV logs

4.    If COMPLETE → rebuilds MV from base tables

5.    Updates MV metadata and LAST_REFRESH timestamp

8. Advantages

• Full control over system load
• Avoids automatic refresh during heavy DML
• Supports FORCE refresh to handle FAST and COMPLETE
• Ideal for batch reporting

9. Disadvantages

• MV may contain stale data until refreshed
• Requires user/application to manage refresh
• Not suitable for real-time reporting

10. Scheduling ON DEMAND refresh

Use DBMS_SCHEDULER or DBMS_JOB:

BEGIN

   DBMS_SCHEDULER.CREATE_JOB (

      job_name        => 'refresh_mv_sales',

      job_type        => 'PLSQL_BLOCK',

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

      start_date      => SYSTIMESTAMP,

      repeat_interval => 'FREQ=DAILY;BYHOUR=2',

      enabled         => TRUE

   );

END;

• Scheduler runs ON DEMAND refresh automatically

11. Performance considerations

• Large MVs may take time → schedule off-peak
• Prefer FAST refresh when possible
• Avoid too frequent refreshes

12. Common mistakes

• Forgetting to refresh → stale data
• Missing MV logs → FAST refresh fails
• Using ON DEMAND for real-time dashboards
• Refreshing during heavy DML → locks/performance issues

13. Best practices

• Use FORCE refresh to handle both FAST and COMPLETE
• Maintain MV logs for incremental refresh
• Schedule refresh during low-usage periods
• Monitor LAST_REFRESH timestamp
• Use indexes to speed up COMPLETE refresh

14. Interview Tip

“ON DEMAND refresh allows a materialized view to be refreshed only when explicitly requested. It provides full control over system load and timing. Refresh can be done manually using DBMS_MVIEW.REFRESH and supports FAST, COMPLETE, or FORCE methods. Ideal for large tables or batch reporting where real-time updates are not needed.”