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