Oracle Materialized View ON DEMAND FAQs with Examples

An Oracle materialized view with ON DEMAND refresh is refreshed when you explicitly request it, or when a job/scheduler calls the refresh operation.

The key idea is:

Base table changes
        ↓
MV may become STALE
        ↓
No automatic refresh just because of the DML
        ↓
Refresh requested
        ↓
MV refreshed

The easiest thing to remember is:

ON DEMAND
    ↓
CONTROLS WHEN the materialized view is refreshed

FAST / COMPLETE / FORCE
    ↓
CONTROL HOW the materialized view is refreshed

1. What is ON DEMAND in a materialized view?

ON DEMAND means Oracle does not automatically refresh the MV just because the underlying tables change.

CREATE MATERIALIZED VIEW mv_product_sales
BUILD IMMEDIATE
REFRESH COMPLETE
ON DEMAND
AS
SELECT product_id,
       SUM(amount) AS total_sales
FROM sales
GROUP BY product_id;

Here:

BUILD IMMEDIATE
        ↓
Populate the MV when it is created

COMPLETE
        ↓
Recalculate the entire result when refreshed

ON DEMAND
        ↓
Refresh when explicitly requested

2. Does ON DEMAND mean manual refresh only?

Not necessarily. ON DEMAND means the refresh is requested, rather than being automatically tied to a base-table commit.

You can refresh it:

• Manually
• Through DBMS_MVIEW.REFRESH
• Through Oracle Scheduler
• Through another application or job that invokes the refresh

BEGIN
    DBMS_MVIEW.REFRESH('MV_PRODUCT_SALES');
END;
/

So:

ON DEMAND
    ↓
Refresh when requested
    ↓
Human OR scheduled job OR application

3. What is the difference between ON DEMAND and ON COMMIT?

This is one of the most important interview questions.

ON DEMAND

DML on base table
        ↓
MV not immediately refreshed
        ↓
Refresh requested later


ON COMMIT

DML on base table
        ↓
COMMIT
        ↓
MV refresh associated with the commit

Feature	ON DEMAND	ON COMMIT
Refresh timing	When requested	Associated with commit
Automatic after commit	❌	✅ When supported
Application DML overhead	Lower	Can be higher
Good for reporting	Often	Less commonly
Good for near-current data	Less suitable	More suitable
Scheduling possible	✅	Not the same concept

4. Does ON DEMAND mean the MV is always stale?

No.

10:00 → MV refreshed
10:05 → Base table changes
10:06 → MV becomes stale
10:10 → MV refreshed

After 10:10 → MV can become FRESH again

So:

ON DEMAND ≠ ALWAYS STALE

It simply means refresh isn't automatically triggered by every commit.

5. How do I create an ON DEMAND materialized view?

CREATE MATERIALIZED VIEW mv_emp_summary
BUILD IMMEDIATE
REFRESH COMPLETE
ON DEMAND
AS
SELECT department_id,
       COUNT(*) AS employee_count,
       SUM(salary) AS total_salary
FROM employees
GROUP BY department_id;

Important parts:

REFRESH COMPLETE
        ↓
HOW?

ON DEMAND
        ↓
WHEN?

6. Can ON DEMAND be used with FAST refresh?

Yes.

CREATE MATERIALIZED VIEW mv_sales
BUILD IMMEDIATE
REFRESH FAST
ON DEMAND
AS
SELECT product_id,
       SUM(amount) AS total_sales
FROM sales
GROUP BY product_id;

Here:

FAST
    ↓
How to refresh?

ON DEMAND
    ↓
When to refresh?

When you request a refresh, Oracle attempts FAST refresh.

7. Can ON DEMAND be used with COMPLETE refresh?

Yes.

CREATE MATERIALIZED VIEW mv_sales
BUILD IMMEDIATE
REFRESH COMPLETE
ON DEMAND
AS
SELECT product_id,
       SUM(amount) AS total_sales
FROM sales
GROUP BY product_id;

When you request a refresh:

ON DEMAND
    ↓
Refresh requested
    ↓
COMPLETE
    ↓
Recalculate complete result

8. Can ON DEMAND be used with FORCE refresh?

Yes.

CREATE MATERIALIZED VIEW mv_sales
BUILD IMMEDIATE
REFRESH FORCE
ON DEMAND
AS
SELECT product_id,
       SUM(amount) AS total_sales
FROM sales
GROUP BY product_id;

When refreshed:

FORCE
    ↓
Try FAST
    ↓
If FAST isn't possible
    ↓
COMPLETE

The refresh isn't triggered simply because the base table changed.

9. What is the most important distinction to remember?

Memorize:

REFRESH FAST
REFRESH COMPLETE
REFRESH FORCE
        ↓
       HOW?

Whereas:

ON DEMAND
ON COMMIT
        ↓
       WHEN?

For example:

CREATE MATERIALIZED VIEW mv_sales
REFRESH FORCE
ON DEMAND
AS
SELECT ...;

means:

FORCE
    ↓
HOW?
FAST if possible, otherwise COMPLETE

ON DEMAND
    ↓
WHEN?
When refresh is requested

10. How do I manually refresh an ON DEMAND MV?

Use DBMS_MVIEW.REFRESH:

BEGIN
    DBMS_MVIEW.REFRESH('MV_SALES');
END;
/

For example:

BEGIN
    DBMS_MVIEW.REFRESH(
        list => 'MV_PRODUCT_SALES'
    );
END;
/

After this completes, the MV contains the refreshed result.

11. Can I explicitly request COMPLETE refresh?

Yes.

BEGIN
    DBMS_MVIEW.REFRESH(
        list   => 'MV_PRODUCT_SALES',
        method => 'C'
    );
END;
/

Here:

C = COMPLETE

12. Can I explicitly request FAST refresh?

Yes, when the MV is eligible.

BEGIN
    DBMS_MVIEW.REFRESH(
        list   => 'MV_PRODUCT_SALES',
        method => 'F'
    );
END;
/

Here:

F = FAST

If the MV doesn't satisfy the requirements for fast refresh, the operation can fail rather than automatically switching to COMPLETE.

13. What happens if I don't refresh an ON DEMAND MV?

Suppose:

10:00
MV refreshed

Then:

INSERT INTO sales
VALUES (100, 101, SYSDATE, 500);

COMMIT;

With REFRESH ... ON DEMAND, the MV isn't automatically refreshed simply because the transaction committed.

Base table = NEW DATA
MV         = OLD DATA

MV remains stale until refresh is performed.

14. Example: ON DEMAND in action

Step 1 — Create table

CREATE TABLE sales (
    sale_id     NUMBER PRIMARY KEY,
    product_id  NUMBER,
    amount      NUMBER
);

Step 2 — Insert data

INSERT INTO sales VALUES (1, 101, 500);
INSERT INTO sales VALUES (2, 101, 300);
INSERT INTO sales VALUES (3, 102, 1000);

COMMIT;

Step 3 — Create MV

CREATE MATERIALIZED VIEW mv_product_sales
BUILD IMMEDIATE
REFRESH COMPLETE
ON DEMAND
AS
SELECT product_id,
       COUNT(*) AS sale_count,
       SUM(amount) AS total_amount
FROM sales
GROUP BY product_id;

The initial result is:

PRODUCT_ID	SALE_COUNT	TOTAL_AMOUNT
101	2	800
102	1	1000

Step 4 — Insert new data

INSERT INTO sales
VALUES (4, 101, 200);

COMMIT;

The base table now has:

101 → 1000

But the MV can still contain:

101 → 800

because the MV hasn't been refreshed.

Step 5 — Refresh the MV

BEGIN
    DBMS_MVIEW.REFRESH('MV_PRODUCT_SALES');
END;
/

Now:

PRODUCT_ID	SALE_COUNT	TOTAL_AMOUNT
101	3	1000
102	1	1000

15. Does ON DEMAND affect the initial population?

Not by itself.

BUILD IMMEDIATE
REFRESH COMPLETE
ON DEMAND

means:

BUILD IMMEDIATE
    ↓
Initial data is populated immediately

Then:

ON DEMAND
    ↓
Future refreshes happen when requested

Compare that with:

BUILD DEFERRED
REFRESH COMPLETE
ON DEMAND

which means the initial population is deferred.

Therefore:

BUILD
    ↓
Initial population

REFRESH
    ↓
Refresh method

ON DEMAND
    ↓
Refresh timing

16. What is BUILD IMMEDIATE + ON DEMAND?

This is a very common combination:

CREATE MATERIALIZED VIEW mv_sales
BUILD IMMEDIATE
REFRESH COMPLETE
ON DEMAND
AS
SELECT ...
FROM sales;

It means:

CREATE MV
    ↓
Populate immediately
    ↓
Later changes occur
    ↓
MV may become stale
    ↓
Refresh only when requested

This is useful for reporting systems.

17. What is BUILD DEFERRED + ON DEMAND?

CREATE MATERIALIZED VIEW mv_sales
BUILD DEFERRED
REFRESH COMPLETE
ON DEMAND
AS
SELECT ...
FROM sales;

Conceptually:

Create definition
    ↓
Don't populate immediately
    ↓
Later refresh requested
    ↓
Populate/refresh MV

18. Can ON DEMAND be scheduled?

Yes. This is a very common production pattern.

Every day at 2:00 AM
        ↓
Oracle Scheduler job
        ↓
DBMS_MVIEW.REFRESH
        ↓
MV refreshed

The important distinction is:

ON DEMAND does not mean "someone must manually click refresh."

It means the refresh is invoked as a demand/request rather than automatically tied to base-table commits.

19. Example: nightly reporting refresh

Suppose management wants yesterday's sales report every morning.

Sales transactions
        ↓
Throughout the day
        ↓
MV becomes stale
        ↓
02:00 AM
        ↓
Scheduled refresh
        ↓
MV updated
        ↓
06:00 AM
        ↓
Reports query MV

The MV could be:

CREATE MATERIALIZED VIEW mv_daily_sales
BUILD IMMEDIATE
REFRESH COMPLETE
ON DEMAND
AS
SELECT TRUNC(sale_date) AS sale_day,
       product_id,
       SUM(amount) AS total_sales
FROM sales
GROUP BY TRUNC(sale_date),
         product_id;

This is a typical use case for ON DEMAND.

20. Is ON DEMAND good for reporting?

Yes, often.

Reporting systems frequently don't need transaction-by-transaction freshness.

Operational database
        ↓
Changes all day
        ↓
Refresh MV at 1 AM
        ↓
Reporting users
        ↓
Fresh daily data

Instead of continuously refreshing the MV after every transaction, you can refresh it periodically. This can reduce overhead on the OLTP workload.

21. Is ON DEMAND good for OLTP?

It depends on the requirement.

If users need the MV to reflect changes almost immediately, ON DEMAND may not be appropriate unless you have a very frequent refresh schedule.

User inserts order
        ↓
Needs dashboard updated immediately

An infrequently refreshed ON DEMAND MV
may not satisfy that requirement.

But a daily management report is a much better fit.

22. Does ON DEMAND reduce DML overhead?

It can, especially compared with commit-time refresh, because the MV isn't being refreshed as part of every relevant commit.

ON DEMAND

1000 transactions
        ↓
Base table changes
        ↓
No MV refresh after every transaction
        ↓
One scheduled refresh

Whereas commit-time refreshing can introduce additional work into the transaction path.

The trade-off is:

Less immediate freshness ↔ Potentially less transaction-time overhead

23. Can multiple MVs be refreshed together?

Yes.

BEGIN
    DBMS_MVIEW.REFRESH(
        list => 'MV_SALES,MV_CUSTOMERS,MV_PRODUCTS'
    );
END;
/

This is useful when several reporting MVs are maintained together.

24. Can I refresh an ON DEMAND MV from a procedure?

Yes.

CREATE OR REPLACE PROCEDURE refresh_reporting_mvs
IS
BEGIN

    DBMS_MVIEW.REFRESH(
        list => 'MV_SALES'
    );

    DBMS_MVIEW.REFRESH(
        list => 'MV_CUSTOMERS'
    );

END;
/

Then:

BEGIN
    refresh_reporting_mvs;
END;
/

A scheduler can also invoke such a procedure.

25. How do I check whether an MV is stale?

Use:

SELECT mview_name,
       staleness,
       last_refresh_type,
       last_refresh_date
FROM user_mviews;

Example:

MVIEW_NAME       STALENESS
----------------  ---------
MV_SALES          FRESH

After relevant base-table changes, it may become:

MVIEW_NAME       STALENESS
----------------  ---------
MV_SALES          STALE

After a successful refresh:

MVIEW_NAME       STALENESS
----------------  ---------
MV_SALES          FRESH

26. How do I check the last refresh date?

Use:

SELECT mview_name,
       last_refresh_date
FROM user_mviews;

Example:

MVIEW_NAME       LAST_REFRESH_DATE
----------------  -------------------
MV_SALES          12-AUG-26 02:00:15

This is particularly useful for monitoring scheduled ON DEMAND refreshes.

27. How do I check the last refresh method?

Use:

SELECT mview_name,
       last_refresh_type
FROM user_mviews;

Possible values include:

FAST
COMPLETE

This is useful if the MV uses:

REFRESH FORCE
ON DEMAND

because you can determine what refresh actually occurred.

28. Does ON DEMAND require a materialized view log?

No. It depends on the refresh method.

For example:

REFRESH COMPLETE
ON DEMAND

doesn't require an MV log simply because it is ON DEMAND.

If you use:

REFRESH FAST
ON DEMAND

then the requirements for FAST refresh must be satisfied, which may include appropriate materialized view logs.

Remember:

ON DEMAND
    ↓
WHEN?

FAST
    ↓
HOW?

29. Can I use FORCE ON DEMAND?

Yes.

CREATE MATERIALIZED VIEW mv_sales
BUILD IMMEDIATE
REFRESH FORCE
ON DEMAND
AS
SELECT product_id,
       SUM(amount) AS total_sales
FROM sales
GROUP BY product_id;

When refreshed:

FORCE
    ↓
Try FAST
    ↓
If FAST isn't possible
    ↓
COMPLETE

ON DEMAND
    ↓
Do this when refresh is requested

30. Can ON DEMAND be combined with COMPLETE?

Yes, and this is one of the simplest combinations.

CREATE MATERIALIZED VIEW mv_sales
BUILD IMMEDIATE
REFRESH COMPLETE
ON DEMAND
AS
SELECT product_id,
       SUM(amount)
FROM sales
GROUP BY product_id;

Meaning:

COMPLETE → HOW

ON DEMAND → WHEN

31. Can ON DEMAND be combined with FAST?

Yes:

CREATE MATERIALIZED VIEW mv_sales
BUILD IMMEDIATE
REFRESH FAST
ON DEMAND
AS
SELECT product_id,
       SUM(amount) AS total_sales
FROM sales
GROUP BY product_id;

Meaning:

FAST → Incremental refresh

ON DEMAND → Requested refresh

32. Can ON DEMAND be combined with FORCE?

Yes:

CREATE MATERIALIZED VIEW mv_sales
BUILD IMMEDIATE
REFRESH FORCE
ON DEMAND
AS
SELECT product_id,
       SUM(amount) AS total_sales
FROM sales
GROUP BY product_id;

Meaning:

FORCE
    ↓
FAST if possible
    ↓
COMPLETE otherwise

ON DEMAND
    ↓
Refresh when requested

33. What happens to the MV after a COMMIT?

With:

REFRESH ... ON DEMAND

a commit on the base table does not itself perform the MV refresh.

INSERT INTO sales VALUES (10, 101, 500);

COMMIT;

        ↓
Base table updated
        ↓
MV may become stale
        ↓
No ON DEMAND refresh yet

Later:

BEGIN
    DBMS_MVIEW.REFRESH('MV_SALES');
END;
/

Then the MV is refreshed.

34. What is a common real-world architecture?

A common reporting architecture is:

OLTP DATABASE
      |
      ↓
Base tables
      |
      ↓
Materialized views
      |
   ON DEMAND
      |
      ↓
Scheduled refresh
      |
      ↓
Reporting tools

For example:

01:00 AM
   ↓
Refresh MVs
   ↓
03:00 AM
   ↓
ETL/reporting completes
   ↓
08:00 AM
   ↓
Business users run reports

The users don't need the MV to change after every individual transaction.

35. ON DEMAND vs ON COMMIT — interview example

Suppose:

INSERT INTO sales
VALUES (100, 101, 500);

COMMIT;

ON DEMAND:

INSERT
  ↓
COMMIT
  ↓
Base table updated
  ↓
MV isn't automatically refreshed

Later:

DBMS_MVIEW.REFRESH('MV_SALES');

ON COMMIT:

INSERT
  ↓
COMMIT
  ↓
MV refresh associated with commit

Therefore:

ON DEMAND gives you control over when refresh occurs, while ON COMMIT ties refresh to transaction commits when the MV definition and refresh method support it.

36. Common ON DEMAND interview traps

Trap 1 — Does ON DEMAND mean manual only?

❌ No. A scheduled job can request the refresh.

Trap 2 — Does ON DEMAND mean the MV never refreshes automatically?

The key is that refresh isn't automatically triggered by base-table commits. A scheduler or application can still invoke the refresh.

Trap 3 — Does ON DEMAND mean COMPLETE refresh?

❌ No. You can use:

FAST + ON DEMAND
COMPLETE + ON DEMAND
FORCE + ON DEMAND

Trap 4 — Does ON DEMAND require an MV log?

❌ No. MV logs are related primarily to FAST-refresh requirements, not to ON DEMAND itself.

Trap 5 — Does ON DEMAND mean the MV is always stale?

❌ No. It can be fresh immediately after a successful refresh.

Trap 6 — Does BUILD IMMEDIATE mean automatic future refresh?

❌ No. It only controls the initial population.

37. ON DEMAND cheat sheet

MATERIALIZED VIEW
        |
   +----+----+
   |         |
   ↓         ↓
 HOW?       WHEN?
   |         |
   |      +--+------+
   |      |         |
   ↓      ↓         ↓
 FAST  DEMAND    COMMIT
 COMPLETE
 FORCE

The key combinations are:

REFRESH FAST + ON DEMAND

FAST + requested refresh

REFRESH COMPLETE + ON DEMAND

COMPLETE + requested refresh

REFRESH FORCE + ON DEMAND

FAST if possible
      ↓
COMPLETE otherwise
      ↓
when refresh is requested

38. Most important interview answers

Q: What is ON DEMAND?

ON DEMAND means the materialized view is refreshed when a refresh operation is requested rather than being refreshed automatically as part of each relevant base-table commit.

Q: Does ON DEMAND mean manual refresh?

No. Manual, scheduled, or application-driven refreshes can all request an ON DEMAND refresh.

Q: Does ON DEMAND determine FAST or COMPLETE?

No. ON DEMAND determines when; FAST, COMPLETE, or FORCE determines how.

Q: Does ON DEMAND require MV logs?

No. MV logs are relevant to FAST-refresh eligibility, not to ON DEMAND itself.

Q: How do I refresh it?

BEGIN
    DBMS_MVIEW.REFRESH('MV_SALES');
END;
/

Q: How do I check its status?

SELECT mview_name,
       staleness,
       last_refresh_type,
       last_refresh_date
FROM user_mviews;

39. Final memory trick

Remember these three questions:

40. BUILD?

BUILD
  ↓
When is the MV initially populated?

41. REFRESH?

REFRESH
  ↓
HOW is the MV refreshed?

FAST / COMPLETE / FORCE

42. ON DEMAND?

ON DEMAND
  ↓
WHEN is the refresh requested?

So this:

CREATE MATERIALIZED VIEW mv_sales
BUILD IMMEDIATE
REFRESH FORCE
ON DEMAND
AS
SELECT product_id,
       SUM(amount) AS total_sales
FROM sales
GROUP BY product_id;

means:

BUILD IMMEDIATE
        ↓
Populate it now

FORCE
        ↓
Try FAST
        ↓
Otherwise COMPLETE

ON DEMAND
        ↓
Don't refresh simply because base-table DML commits;
refresh when a refresh request/job invokes it

One-line interview answer:
Oracle MV ON DEMAND means the materialized view is refreshed when a refresh operation is requested, and it can be combined independently with FAST, COMPLETE, or FORCE to determine how that refresh is performed.