Oracle Materialized View Logs FAQs with Examples

A materialized view log (MV log) is a database object created on a base table to record changes made to that table.

MV logs are especially important for FAST refreshable materialized views because Oracle can use the recorded changes instead of rereading the entire base table.

The key idea is:

Base Table
↓
Materialized View Log
↓
Records relevant changes
↓
FAST refresh
↓
Materialized View updated

1. What is a Materialized View Log?

A materialized view log is a table-like Oracle object that stores information about changes to a master/base table.

Example:

CREATE MATERIALIZED VIEW LOG ON sales
WITH ROWID, SEQUENCE
INCLUDING NEW VALUES;

Suppose:

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

UPDATE sales
SET amount = 600
WHERE sale_id = 1;

DELETE FROM sales
WHERE sale_id = 1;

Oracle can record information about these changes in the MV log.

That information can later be used for FAST refresh.

2. Why do we need Materialized View Logs?

Without a suitable MV log, Oracle may have to examine much more data to determine what changed.

With an MV log:

Base table
↓
Only changes are recorded
↓
FAST refresh
↓
Use changes to update MV

This is especially useful when:

Base table = millions/billions of rows
Changes    = relatively small

Instead of recalculating everything, Oracle can process the changes.

3. Is an MV log itself a Materialized View?

No.

These are different objects.

Materialized View

Stores the result of a query.

CREATE MATERIALIZED VIEW mv_sales
AS
SELECT product_id,
SUM(amount)
FROM sales
GROUP BY product_id;

Materialized View Log

Stores change information about the base table.

CREATE MATERIALIZED VIEW LOG ON sales
WITH ROWID, SEQUENCE;

Remember:

MV      = stores query result

MV Log  = stores information about base-table changes

4. On which table is the MV log created?

You create the MV log on the base/master table, not on the materialized view.

SALES
↓
MV LOG ON SALES
↓
MV_SALES

SQL:

CREATE MATERIALIZED VIEW LOG ON sales
WITH ROWID, SEQUENCE;

Not:

CREATE MATERIALIZED VIEW LOG ON mv_sales;

5. What is the basic syntax?

A simplified form is:

CREATE MATERIALIZED VIEW LOG ON table_name
[WITH ...]
[INCLUDING NEW VALUES]
;

For example:

CREATE MATERIALIZED VIEW LOG ON sales
WITH ROWID, SEQUENCE
INCLUDING NEW VALUES;

The exact clauses depend on the requirements of the materialized view and refresh method.

6. What does WITH ROWID mean?

WITH ROWID tells Oracle to record the base table's ROWID in the materialized view log.

Example:

CREATE MATERIALIZED VIEW LOG ON sales
WITH ROWID;

This can be useful when Oracle needs ROWID information for FAST refresh.

Think:

WITH ROWID
↓
Record row location information

7. What does WITH PRIMARY KEY mean?

It tells Oracle to record information based on the table's primary key.

Example:

CREATE MATERIALIZED VIEW LOG ON sales
WITH PRIMARY KEY;

If:

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

then Oracle can use the primary-key information for refresh processing.

Conceptually:

WITH PRIMARY KEY
↓
Identify changed rows using primary-key information

8. ROWID vs PRIMARY KEY

This is a common interview question.

ROWID

CREATE MATERIALIZED VIEW LOG ON sales
WITH ROWID;

Uses ROWID information.

PRIMARY KEY

CREATE MATERIALIZED VIEW LOG ON sales
WITH PRIMARY KEY;

Uses primary-key information.

Comparison:

Feature	ROWID	PRIMARY KEY
Identifies row	ROWID	Primary key
Requires PK	❌	✅
Common use	FAST refresh	FAST refresh
Works with PK-based MV	Depends on definition	Yes

As a general design principle, use the form required by your MV's refresh eligibility rather than choosing arbitrarily.

9. Can an MV log contain columns?

Yes.

Suppose:

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

You can create a log containing specific columns:

CREATE MATERIALIZED VIEW LOG ON sales
WITH PRIMARY KEY
(product_id, customer_id, amount);

These columns provide information needed by a FAST-refreshable MV.

10. Why would I specify columns in the MV log?

Suppose your materialized view uses:

SELECT product_id,
SUM(amount)
FROM sales
GROUP BY product_id;

Oracle may need information about:

product_id
amount

for incremental refresh.

A log can therefore include the required columns.

Example:

CREATE MATERIALIZED VIEW LOG ON sales
WITH PRIMARY KEY
(product_id, amount);

The exact columns required depend on the MV query and Oracle's FAST refresh rules.

11. What does INCLUDING NEW VALUES mean?

This is important for UPDATE operations.

Example:

CREATE MATERIALIZED VIEW LOG ON sales
WITH PRIMARY KEY
(amount)
INCLUDING NEW VALUES;

INCLUDING NEW VALUES tells Oracle to capture the new values of columns when they are updated.

For example:

UPDATE sales
SET amount = 1000
WHERE sale_id = 10;

Conceptually, the log can contain information about the new value:

OLD amount → 500
NEW amount → 1000

This can be required for certain FAST refresh scenarios.

12. Why are old and new values important?

Consider:

UPDATE sales
SET amount = 1000
WHERE sale_id = 10;

An aggregate MV might previously contain:

SUM(amount) = 5000

After the update:

500 → 1000

The aggregate needs to account for:

Remove old value = 500
Add new value    = 1000

Therefore, knowing the new value can be important for incremental maintenance.

13. Does every MV log need INCLUDING NEW VALUES?

No.

It depends on the MV definition and FAST refresh requirements.

Don't memorize:

FAST refresh → always INCLUDING NEW VALUES

Instead:

Use INCLUDING NEW VALUES when the materialized view's FAST refresh requirements need updated column values.

14. What happens when data changes in the base table?

Suppose:

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

Oracle records the relevant change in the MV log.

Conceptually:

SALES
|
+---- INSERT
|
↓
MV LOG
|
+---- change information

Later:

DBMS_MVIEW.REFRESH('MV_PRODUCT_SALES');

Oracle can use that change information for FAST refresh.

15. Does an MV log contain the entire base table?

No.

This is an important distinction.

The base table:

SALES

---

1
2
3
4
5
...
1,000,000

The MV log records changes, for example:

SALE_ID   DML

---

100       INSERT
250       UPDATE
700       DELETE

Conceptually:

Base table = current data

MV log = change information

16. Does an MV log replace the base table?

No.

It is an auxiliary object used to support materialized-view maintenance.

Base table
↓
MV log
↓
FAST refresh
↓
Materialized view

The base table remains the source of truth.

17. Example: Creating an MV log

Let's start with:

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

Insert data:

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

COMMIT;

Create MV log:

CREATE MATERIALIZED VIEW LOG ON sales
WITH PRIMARY KEY
(product_id, amount)
INCLUDING NEW VALUES;

Now Oracle can record relevant changes for supported FAST refresh scenarios.

18. Example: MV using the MV log

Create:

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

Now:

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

COMMIT;

The MV isn't automatically refreshed because it is:

ON DEMAND

Later:

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

The FAST refresh can use the MV log.

19. Can an MV log be used with ON COMMIT?

Yes.

A common pattern is:

CREATE MATERIALIZED VIEW LOG ON sales
WITH PRIMARY KEY
(product_id, amount)
INCLUDING NEW VALUES;

Then:

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

Conceptually:

DML
↓
MV log records change
↓
COMMIT
↓
FAST refresh
↓
MV updated

20. What is the difference between MV log and ON DEMAND?

They are completely different concepts.

MV log

WHAT?
↓
Records changes

ON DEMAND

WHEN?
↓
Refresh when requested

For example:

CREATE MATERIALIZED VIEW LOG ON sales
WITH PRIMARY KEY;

and:

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

Here:

MV Log       → records changes
FAST         → incremental refresh
ON DEMAND    → refresh when requested

21. What is the difference between MV log and materialized view?

MV log

SALES
↓
Changes
↓
MV_LOG

Materialized view

SALES
↓
SELECT query
↓
MV_SALES

Think:

MV = result
MV log = change history needed for refresh

22. Does an MV log improve query performance?

Not directly.

The MV log exists primarily to support materialized view maintenance, especially FAST refresh.

It isn't designed as a reporting table.

Don't use:

MV LOG

as a replacement for:

INDEX

or:

MATERIALIZED VIEW

23. Does an MV log increase storage usage?

Yes.

Every change that needs to be recorded can add data to the MV log.

For a high-volume table:

SALES
Millions of changes
↓
MV LOG
Large amount of change information

Therefore, MV logs require storage and maintenance.

24. Does an MV log increase DML overhead?

Yes, potentially.

When a row changes:

INSERT/UPDATE/DELETE
↓
Base table modified
+
MV log information recorded

So there is additional work associated with maintaining the log.

This should be considered when designing high-volume OLTP systems.

25. Does an MV log get automatically purged?

Oracle manages MV log data based on materialized-view refresh requirements, but the exact purge behavior depends on the materialized views using the log and their refresh state.

The important idea is:

MV log
↓
Changes needed by MV refresh
↓
After changes are no longer needed
↓
Oracle can purge eligible log data

You should not assume that the MV log behaves like an ordinary application audit table.

26. Can multiple materialized views use the same MV log?

Yes.

A single base table can have a materialized view log that supports multiple materialized views, provided the log contains the information needed by those MVs.

Example:

SALES
|
+------+------+
|             |
↓             ↓
MV LOG        MV LOG data
|
+-----+-----+
|           |
↓           ↓
MV_SALES1   MV_SALES2

You don't normally create a separate MV log for every MV on the same base table.

27. Can an MV log be created on a table with a primary key?

Yes.

Example:

CREATE TABLE customers (
    customer_id NUMBER PRIMARY KEY,
    name        VARCHAR2(100),
    city        VARCHAR2(100)
);

Create:

CREATE MATERIALIZED VIEW LOG ON customers
WITH PRIMARY KEY;

28. Can an MV log be created on a table without a primary key?

Yes, depending on the refresh requirements.

For example:

CREATE MATERIALIZED VIEW LOG ON sales
WITH ROWID;

This records ROWID information instead of relying on a primary key.

But the MV definition must be compatible with the chosen refresh mechanism.

29. What is SEQUENCE in an MV log?

You may see:

CREATE MATERIALIZED VIEW LOG ON sales
WITH ROWID, SEQUENCE;

SEQUENCE provides sequencing information for changes recorded in the log.

This can be useful for certain FAST refresh scenarios, particularly where ordering/change information matters.

Don't confuse:

SEQUENCE

with:

CREATE SEQUENCE ...

in the normal application sense.

30. Why do I sometimes see ROWID, SEQUENCE together?

A common MV log definition is:

CREATE MATERIALIZED VIEW LOG ON sales
WITH ROWID, SEQUENCE
INCLUDING NEW VALUES;

Each part has a purpose:

ROWID
↓
Row identification

SEQUENCE
↓
Change sequencing information

INCLUDING NEW VALUES
↓
Capture new values for relevant updates

The exact required combination depends on the MV's FAST refresh requirements.

31. Can I add an MV log after creating the materialized view?

Yes, but you need to consider whether the existing MV can then become FAST-refreshable and whether it needs a complete refresh first.

Typical sequence:

Create base table
↓
Create MV log
↓
Create FAST-refreshable MV

For an existing MV, changing the log configuration may require additional administration and validation.

32. How do I check MV logs?

You can query Oracle's data dictionary.

For example:

SELECT log_owner,
master,
log_table
FROM all_mview_logs;

For your own schema:

SELECT log_owner,
master,
log_table
FROM user_mview_logs;

This helps identify:

Base table
↓
MV log

33. How do I see the columns in an MV log?

You can inspect the MV log table itself using the data dictionary.

For example:

SELECT column_name,
data_type
FROM user_tab_columns
WHERE table_name = 'MLOG$_SALES';

Oracle commonly gives MV log tables names such as:

MLOG$_SALES

Don't hard-code this naming convention into application logic; use the dictionary views when you need to identify the log.

34. What is MLOG$_SALES?

When you create:

CREATE MATERIALIZED VIEW LOG ON sales
...

Oracle creates an internal log table, commonly named something like:

MLOG$_SALES

Conceptually:

SALES
↓
MLOG$_SALES

This is the physical object that stores the logged change information.

35. Can I directly query MLOG$_SALES?

Technically, depending on privileges, the underlying log table may be visible, but you generally should not treat it as an application table.

Instead, use Oracle's documented materialized-view log mechanisms and data dictionary views.

For administration:

SELECT *
FROM user_mview_logs;

is preferable to depending on internal naming.

36. What happens if I drop an MV log?

If a materialized view depends on that log for FAST refresh, dropping the log can make FAST refresh unavailable or cause subsequent refresh operations to fail.

Example:

DROP MATERIALIZED VIEW LOG ON sales;

Afterward:

MV
↓
FAST refresh
↓
Required log missing
↓
FAST refresh may fail

Therefore, don't drop an MV log without checking which MVs depend on it.

37. What happens if I truncate the MV log?

You should not treat an MV log like a normal staging table and truncate it arbitrarily.

The logged changes may be required by one or more materialized views for their next FAST refresh.

If the required change information is removed prematurely:

MV LOG
↓
Required changes deleted
↓
FAST refresh may no longer be possible

Oracle's materialized-view refresh mechanism manages log data as part of MV maintenance.

38. Does COMPLETE refresh need an MV log?

No, not for the purpose of complete refresh.

A COMPLETE refresh recomputes the materialized view query.

Conceptually:

COMPLETE
↓
Run MV query again
↓
Rebuild result

Therefore:

FAST refresh → MV log often important
COMPLETE      → MV log isn't needed to calculate the complete result

39. Does FORCE refresh need an MV log?

FORCE tries FAST refresh when possible and otherwise uses COMPLETE refresh.

Therefore:

FORCE
↓
Try FAST
↓
If FAST possible
↓
Use MV log/change information as required

If FAST not possible
↓
COMPLETE

So an MV log can be important if you want FORCE to take the FAST path.

40. Can MV logs record INSERT, UPDATE and DELETE?

Yes.

Conceptually:

INSERT → log change
UPDATE → log change
DELETE → log change

For example:

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

UPDATE sales
SET amount = 600
WHERE sale_id = 10;

DELETE FROM sales
WHERE sale_id = 10;

The log supports the refresh mechanism in determining what happened.

41. What is a common MV log example for an aggregate MV?

Base table:

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

MV log:

CREATE MATERIALIZED VIEW LOG ON sales
WITH PRIMARY KEY
(product_id, amount)
INCLUDING NEW VALUES;

MV:

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

Refresh:

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

Conceptually:

SALES
↓
MATERIALIZED VIEW LOG
↓
Changes since last refresh
↓
FAST refresh
↓
MV_PRODUCT_SALES

42. What happens when an UPDATE changes an aggregated column?

Suppose:

Product 101
Total = 1000

Then:

UPDATE sales
SET amount = amount + 100
WHERE sale_id = 1;

For FAST refresh, Oracle needs enough information to determine how the aggregate changes.

Conceptually:

Old value = 500
New value = 600

Aggregate:
1000 - 500 + 600
↓
1100

The MV log provides the change information required by the refresh mechanism.

43. What is the relationship between MV logs and FAST refresh?

This is the most important concept:

FAST REFRESH
|
↓
What changed?
|
↓
MV LOG / logs
|
↓
Apply incremental changes
|
↓
MV

Without the necessary change information, Oracle may not be able to perform FAST refresh.

44. What is the relationship between MV logs and ON COMMIT?

For a typical FAST + ON COMMIT design:

Base table
↓
DML
↓
MV log records change
↓
COMMIT
↓
FAST refresh
↓
Materialized view

So:

MV log       = WHAT changed
ON COMMIT    = WHEN to refresh
FAST         = HOW to refresh

This three-part distinction is excellent for interviews.

45. Can an MV log be used with ON DEMAND?

Yes.

Example:

CREATE MATERIALIZED VIEW LOG ON sales
WITH PRIMARY KEY
(product_id, amount)
INCLUDING NEW VALUES;

Then:

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

The MV log supports FAST refresh, while ON DEMAND controls when refresh is requested.

46. MV Log vs Trigger

This is another useful interview comparison.

Trigger

DML
↓
Trigger executes
↓
Custom PL/SQL

MV Log

DML
↓
Oracle records change information
↓
Used by MV refresh mechanism

MV logs are specifically designed to support materialized-view maintenance.

47. Can I use a trigger instead of an MV log?

Not as a simple replacement.

You could build custom change-tracking logic with triggers, but that doesn't mean Oracle's FAST refresh mechanism can simply use your custom table as an MV log.

For Oracle FAST refresh, use the supported:

CREATE MATERIALIZED VIEW LOG

mechanism.

48. What are the disadvantages of MV logs?

Main considerations:

49. Additional storage

Base table changes
↓
MV log rows
↓
Storage usage

50. DML overhead

Changes must be logged.

51. Administration

You need to monitor:

• Log size
• Refresh frequency
• Dependent MVs
• Refresh failures

52. Design complexity

The log must contain information appropriate for the MV's FAST refresh requirements.

53. What are the advantages?

FAST refresh

Instead of recalculating the complete MV:

Entire base table
↓
Recalculate everything

Oracle can use:

Changes since last refresh
↓
Incremental maintenance

Better for large datasets.

Especially when:

Large base table
-
Small number of changes
Supports frequent refresh

It can be particularly useful for:

FAST + ON COMMIT

or:

FAST + ON DEMAND

50. Common MV Log interview traps

Trap 1

Is an MV log the same as a materialized view?

❌ No.

MV      = query result
MV Log  = change information

Trap 2

Is an MV log required for every materialized view?

❌ No.

It is primarily associated with FAST refresh requirements.

Trap 3

Does COMPLETE refresh require an MV log?

❌ No.

Complete refresh can recalculate the MV query.

Trap 4

Does ON DEMAND require an MV log?

❌ No.

ON DEMAND only specifies refresh timing.

Trap 5

Does ON COMMIT itself mean an MV log is always required?

❌ Don't generalize that way.

The refresh method and MV definition determine what information is required. Typical FAST + ON COMMIT designs use an MV log.

Trap 6

Does INCLUDING NEW VALUES mean the MV log stores only new rows?

❌ No.

It refers to capturing new column values for relevant UPDATE operations.

Trap 7

Can I truncate an MV log whenever I want?

❌ No.

The logged changes may still be required for FAST refresh.

51. Important MV Log clauses

Remember these:

CREATE MATERIALIZED VIEW LOG ON sales
WITH ROWID, SEQUENCE
INCLUDING NEW VALUES;

WITH ROWID

Record ROWID information

WITH PRIMARY KEY

Record primary-key information

SEQUENCE

Record sequencing information for changes

Column list

Specify columns needed for refresh

Example:

(product_id, amount)

INCLUDING NEW VALUES

Capture new values for relevant UPDATE operations

52. How do I monitor MV logs?

Start with:

SELECT log_owner,
master,
log_table
FROM user_mview_logs;

Then inspect the log table metadata:

SELECT column_name,
data_type
FROM user_tab_columns
WHERE table_name = 'MLOG$_SALES';

You can also monitor your materialized views:

SELECT mview_name,
refresh_mode,
refresh_method,
staleness,
last_refresh_type,
last_refresh_date
FROM user_mviews;

This gives you the relationship:

MV definition
+
MV log
+
Refresh status

53. Complete example: FAST + ON DEMAND

Base table

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

MV log

CREATE MATERIALIZED VIEW LOG ON sales
WITH PRIMARY KEY
(product_id, amount)
INCLUDING NEW VALUES;

Materialized view

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

Change data

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

COMMIT;

Refresh

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

Conceptually:

INSERT/UPDATE/DELETE
↓
MV LOG
↓
DBMS_MVIEW.REFRESH
↓
FAST refresh
↓
MV

54. Complete example: FAST + ON COMMIT

Create the log:

CREATE MATERIALIZED VIEW LOG ON sales
WITH PRIMARY KEY
(product_id, amount)
INCLUDING NEW VALUES;

Create the MV:

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

Then:

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

COMMIT;

Conceptually:

INSERT
↓
MV LOG
↓
COMMIT
↓
FAST refresh
↓
MV updated

55. The most important concept to memorize

Think of an MV refresh as answering three questions:

56. WHAT changed?

↓
MV LOG

57. HOW should the MV be refreshed?

↓
FAST / COMPLETE / FORCE

58. WHEN should it be refreshed?

↓
ON COMMIT / ON DEMAND

For example:

CREATE MATERIALIZED VIEW LOG ON sales
WITH PRIMARY KEY
(product_id, amount)
INCLUDING NEW VALUES;

then:

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

means:

MV LOG
↓
WHAT changed?

FAST
↓
HOW to refresh?

ON DEMAND
↓
WHEN to refresh?

56. Final MV Log cheat sheet

Concept	Meaning
MV Log	Records base-table changes
WITH ROWID	Records ROWID information
WITH PRIMARY KEY	Records PK information
SEQUENCE	Records change sequencing information
Column list	Records specified columns needed for refresh
INCLUDING NEW VALUES	Captures new values for relevant updates
FAST refresh	Uses change information for incremental refresh
COMPLETE refresh	Recomputes the MV
ON COMMIT	Refresh associated with commit
ON DEMAND	Refresh when requested
MLOG$_...	Common physical name of an MV log table
MV Log ≠ MV	Log stores changes; MV stores query result

One-line interview answer

An Oracle Materialized View Log is a change-tracking object on a base table that records information required by materialized views, primarily to enable efficient FAST refresh instead of recomputing the entire materialized view.

The ultimate memory trick

BASE TABLE
|
| DML
↓
MV LOG
"WHAT changed?"
|
↓
FAST REFRESH
"HOW to update?"
|
↓
MATERIALIZED VIEW

And:

ON COMMIT  → WHEN? At commit
ON DEMAND  → WHEN? When requested
MV LOG     → WHAT changed?
FAST       → HOW to refresh?

Oracle Materialized View ON COMMIT FAQs with Examples

An Oracle materialized view with ON COMMIT refresh is refreshed automatically when a transaction that modifies the relevant base table(s) commits, provided the materialized view definition and refresh method satisfy Oracle's requirements.

The key idea is:

DML on base table
↓
COMMIT
↓
Materialized View refresh
↓
MV reflects committed changes

The easiest way to remember it:

ON COMMIT controls WHEN the MV is refreshed — at commit time.

1. What is ON COMMIT refresh?

ON COMMIT tells Oracle to refresh the materialized view as part of the commit processing for transactions that modify its underlying tables.

Example:

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

Conceptually:

INSERT/UPDATE/DELETE
↓
COMMIT
↓
MV refresh

So the MV can become current as part of the committing transaction.

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

This is one of the most important interview questions.

ON COMMIT

DML
↓
COMMIT
↓
MV refresh

ON DEMAND

DML
↓
COMMIT
↓
MV not automatically refreshed
↓
Later DBMS_MVIEW.REFRESH

Feature	ON COMMIT	ON DEMAND
Refresh trigger	Commit	Explicit refresh request
Automatic at commit	✅	❌
Transaction overhead	Can be higher	Lower
Freshness	More immediate	Depends on refresh schedule
Manual/scheduled refresh	Not the normal model	✅
Good for large, frequently changing OLTP tables	Often unsuitable	Often better

Memory trick:

ON COMMIT  = refresh with COMMIT
ON DEMAND  = refresh when requested

3. Can ON COMMIT be used with FAST refresh?

Yes. In practice, ON COMMIT is generally associated with materialized views that support the required fast-refresh capabilities.

Example:

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

When the base table changes:

DML
↓
COMMIT
↓
FAST MV refresh

The exact eligibility requirements must be satisfied.

4. Can ON COMMIT be used with COMPLETE refresh?

This is an important distinction.

ON COMMIT is not a general-purpose "complete refresh at every commit" option. Commit-time refresh has restrictions, and practical ON COMMIT materialized-view designs normally rely on FAST refresh capabilities.

Don't think of this as:

ON COMMIT + COMPLETE

being interchangeable with:

ON DEMAND + COMPLETE

For complete refreshes, the common pattern is:

REFRESH COMPLETE
ON DEMAND

5. Does ON COMMIT mean the MV refreshes after every DML statement?

No.

The important word is commit.

Suppose:

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

INSERT INTO sales VALUES (2, 101, 300);

INSERT INTO sales VALUES (3, 102, 1000);

COMMIT;

Conceptually:

INSERT
INSERT
INSERT
↓
COMMIT
↓
MV refresh

It isn't:

INSERT → MV refresh
INSERT → MV refresh
INSERT → MV refresh

for each individual statement.

6. What happens if I perform DML but don't COMMIT?

Suppose:

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

-- No COMMIT yet

The change is still part of the current uncommitted transaction.

The ON COMMIT refresh isn't triggered yet.

Conceptually:

INSERT
↓
Uncommitted transaction
↓
No commit
↓
No ON COMMIT refresh yet

Then:

COMMIT;

causes the commit-time processing.

7. What happens if I ROLLBACK?

Suppose:

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

ROLLBACK;

The change is discarded.

Therefore:

INSERT
↓
ROLLBACK
↓
Change disappears
↓
No committed change for the MV to reflect

This is an important difference from simply running DML.

8. Does ON COMMIT make the MV real-time?

Not exactly.

It provides commit-time refresh, but it isn't the same thing as a continuously updated table.

For example:

10:00:00 → INSERT
10:00:01 → COMMIT
10:00:01 → MV refresh processing

The MV reflects the committed data after the refresh completes.

So:

ON COMMIT ≠ instantaneous

It means refresh is associated with commit processing.

9. Does ON COMMIT increase transaction time?

It can.

This is one of the biggest practical considerations.

Suppose:

Application
↓
INSERT 100 rows
↓
COMMIT
↓
MV refresh work
↓
Commit completes

The refresh can add work to the transaction's commit path.

Therefore, ON COMMIT should be used carefully for high-volume OLTP systems.

10. Why can ON COMMIT be expensive?

Imagine:

SALES
1 billion rows

and a materialized view:

SELECT product_id,
       SUM(amount)
FROM sales
GROUP BY product_id;

If the MV can be fast refreshed, Oracle can maintain the result incrementally.

But if the refresh requires substantial work, that work can affect the transaction that is committing.

Conceptually:

Application DML
↓
COMMIT
↓
MV maintenance
↓
Commit completes

This can increase commit latency.

11. Why is FAST refresh commonly used with ON COMMIT?

Because ON COMMIT is intended to keep the MV relatively current without requiring a full recomputation at every commit.

Conceptually:

ON COMMIT + FAST
↓
Only maintain the affected MV information
↓
More practical

rather than:

ON COMMIT + COMPLETE
↓
Recalculate entire MV at commit
↓
Potentially enormous overhead

12. What is a materialized view log?

A materialized view log records information about changes to a base table so that Oracle can perform incremental FAST refreshes when the MV definition requires it.

Example:

CREATE MATERIALIZED VIEW LOG ON sales
WITH ROWID, SEQUENCE
INCLUDING NEW VALUES;

Then you can create an eligible MV:

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

The MV log supports the incremental-refresh mechanism.

13. Does every ON COMMIT MV require an MV log?

Don't memorize simply:

ON COMMIT → MV log always required

The correct concept is:

ON COMMIT
↓
Requires a supported refresh definition
↓
FAST refresh capabilities may require MV logs

Whether a log is required depends on the specific MV definition and refresh capabilities.

For many practical FAST-refreshable MVs, an appropriate MV log is required.

14. Can I create an ON COMMIT MV without a log?

It depends on the MV definition and Oracle's refresh capabilities.

For a typical aggregate FAST-refresh scenario, you generally create the necessary MV log first:

CREATE MATERIALIZED VIEW LOG ON sales
WITH ROWID, SEQUENCE
INCLUDING NEW VALUES;

Then:

CREATE MATERIALIZED VIEW mv_product_sales
REFRESH FAST
ON COMMIT
AS
SELECT product_id,
       SUM(amount) AS total_sales
FROM sales
GROUP BY product_id;

If the required FAST-refresh capabilities aren't available, the MV creation/refresh may fail.

15. Can I use FORCE ON COMMIT?

FORCE and ON COMMIT answer different questions:

FORCE
↓
How should refresh be performed?

ON COMMIT
↓
When should refresh happen?

However, ON COMMIT has refresh-method restrictions, so you should not assume that every combination of:

FORCE + ON COMMIT

is valid for every MV definition.

For practical Oracle designs, verify the MV's refresh capabilities rather than assuming FORCE can simply fall back to COMPLETE at commit time.

16. What happens if the MV isn't eligible for ON COMMIT refresh?

Oracle can reject the materialized-view definition or refresh configuration.

For example, if your MV definition doesn't satisfy the required refresh capabilities, you can't assume Oracle will simply do:

ON COMMIT
↓
FAST failed
↓
COMPLETE

This is an important distinction from:

FORCE + ON DEMAND

where COMPLETE is a normal fallback option.

17. What is a practical example?

Suppose:

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

Create an MV log:

CREATE MATERIALIZED VIEW LOG ON sales
WITH ROWID, SEQUENCE
INCLUDING NEW VALUES;

Create the MV:

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

Insert:

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

COMMIT;

Conceptually:

INSERT
↓
COMMIT
↓
FAST refresh
↓
MV reflects committed change

18. Example with multiple INSERT statements

Suppose:

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

INSERT INTO sales VALUES (2, 101, 300);

INSERT INTO sales VALUES (3, 102, 1000);

COMMIT;

The conceptual flow is:

INSERT 1
↓
INSERT 2
↓
INSERT 3
↓
COMMIT
↓
MV maintenance/refresh

After the commit, the MV could show:

PRODUCT_ID	SALE_COUNT	TOTAL_AMOUNT
101	2	800
102	1	1000

19. What happens with UPDATE?

Suppose:

UPDATE sales
SET amount = 600
WHERE sale_id = 1;

COMMIT;

The committed change can be reflected in the ON COMMIT MV if the MV is eligible for the required refresh.

Conceptually:

Old:
sale_id 1 → 500

UPDATE:
500 → 600

COMMIT
↓
MV maintenance
↓
New aggregate reflects 600

20. What happens with DELETE?

Suppose:

DELETE FROM sales
WHERE sale_id = 1;

COMMIT;

The corresponding MV result is maintained during the commit-time refresh when the MV supports the required refresh method.

For example:

Before:
Product 101 → 800

Delete sale of 500

After commit:
Product 101 → 300

21. What happens if a transaction rolls back?

Example:

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

ROLLBACK;

Since the transaction wasn't committed:

New row → discarded

The MV does not need to reflect that rolled-back change.

22. Does ON COMMIT refresh happen for every user session?

The refresh is associated with commits affecting the relevant base objects.

Imagine:

Session A
↓
INSERT
↓
COMMIT
↓
MV refresh processing

Session B
↓
INSERT
↓
COMMIT
↓
MV refresh processing

This is one reason why high-frequency OLTP workloads can make ON COMMIT expensive.

23. Is ON COMMIT suitable for large OLTP tables?

Usually, you should evaluate it carefully.

Consider:

Orders table
50 million rows
10,000 transactions/minute

If an MV is refreshed at commit time for all these transactions:

10,000 transactions
↓
10,000 commit-related MV refresh operations

That can add substantial overhead.

For reporting systems, a common alternative is:

REFRESH ... ON DEMAND

with periodic FAST or COMPLETE refresh.

24. When is ON COMMIT useful?

It can be useful when:

• The MV needs to remain close to current.
• Transactions don't occur at extremely high volume.
• The MV supports efficient FAST refresh.
• Users need more current summarized data.
• Commit-time overhead is acceptable.

Example:

Order system
↓
Small number of transactions
↓
Commit
↓
MV updated
↓
Operational dashboard

25. When should I prefer ON DEMAND?

Use ON DEMAND when you don't need the MV updated after every commit.

Example:

Large sales system
↓
Millions of transactions/day
↓
Nightly reporting
↓
ON DEMAND
↓
Refresh at 2 AM

This avoids putting MV refresh work directly into every transaction's commit path.

26. ON COMMIT vs ON DEMAND — real-world example

Suppose a company has:

SALES = 100 million rows

Requirement A

Dashboard must reflect committed sales almost immediately.

Potential approach:

FAST
+
ON COMMIT

provided the MV satisfies the necessary capabilities.

Requirement B

Management only needs a daily report.

Better candidate:

COMPLETE/FAST
+
ON DEMAND
+
Scheduled refresh

The choice depends on freshness requirements and workload.

27. Can I manually refresh an ON COMMIT MV?

You can invoke refresh operations through Oracle's materialized-view refresh facilities, but the important point is that the MV's configured refresh behavior is commit-based.

For example, Oracle provides:

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

For troubleshooting and administration, explicit refresh operations can be useful.

28. How do I check the MV refresh configuration?

Use:

SELECT mview_name,
       refresh_mode,
       refresh_method,
       staleness,
       last_refresh_type,
       last_refresh_date
FROM user_mviews;

For example:

MVIEW_NAME       REFRESH_MODE   REFRESH_METHOD
-------------------------------------------------
COMMIT

which indicates commit-based refresh.

29. How do I check whether the MV is fresh?

Use:

SELECT mview_name,
       staleness,
       last_refresh_type,
       last_refresh_date
FROM user_mviews;

Example:

MVIEW_NAME       STALENESS   LAST_REFRESH_TYPE
-------------------------------------------------
MV_PRODUCT_SALES FRESH       FAST

The exact state can depend on the transaction and refresh situation.

30. Can ON COMMIT refresh fail?

Yes.

A commit can encounter errors related to MV maintenance if the MV cannot be refreshed successfully.

This is another reason why ON COMMIT must be designed carefully.

Compare:

ON COMMIT
↓
MV maintenance is tied closely to transaction processing

versus:

ON DEMAND
↓
Refresh happens separately

With ON DEMAND, a refresh failure doesn't normally become part of the original application's commit path.

31. Why is ON COMMIT more sensitive to MV design?

Because the MV refresh is tied to the transaction.

A poorly designed or expensive MV can cause:

Application DML
↓
COMMIT
↓
Expensive MV maintenance
↓
Slow commit

Therefore, before using ON COMMIT, you should consider:

• MV size
• DML volume
• FAST refresh eligibility
• MV logs
• Indexes
• Commit frequency
• Query complexity
• Business freshness requirements

32. Can ON COMMIT be used for aggregate MVs?

Yes, provided the aggregate MV meets Oracle's requirements for the selected refresh method.

Example:

CREATE MATERIALIZED VIEW LOG ON sales
WITH ROWID, SEQUENCE
INCLUDING NEW VALUES;

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

This is a classic aggregate-MV pattern.

33. What is the difference between MV log and ON COMMIT?

Don't confuse these two concepts.

MV log

Records information about changes to a base table.

Base table
↓
MV log
↓
Information about changes

ON COMMIT

Controls when the MV refresh is triggered.

DML
↓
COMMIT
↓
Refresh

So:

MV log = CHANGE INFORMATION
ON COMMIT = REFRESH TIMING

34. What is the difference between FAST and ON COMMIT?

Again, they answer different questions.

FAST

FAST
↓
HOW?
Incremental refresh

ON COMMIT

ON COMMIT
↓
WHEN?
At commit

Therefore:

REFRESH FAST
ON COMMIT

means:

HOW?  → FAST
WHEN? → COMMIT

35. What is the difference between COMPLETE and ON COMMIT?

Similarly:

COMPLETE
↓
HOW?
Recalculate entire MV

ON COMMIT
↓
WHEN?
At commit

However, not every refresh-method/timing combination is supported. In particular, don't assume a complete refresh can simply be attached to every ON COMMIT MV.

36. BUILD IMMEDIATE + ON COMMIT

Example:

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

Meaning:

BUILD IMMEDIATE
↓
Populate MV now

FAST
↓
Use incremental refresh

ON COMMIT
↓
Refresh at commit time

37. Can BUILD DEFERRED be used with ON COMMIT?

The initial population and commit-time refresh are separate concepts, but ON COMMIT has restrictions around how the MV is initially populated and subsequently maintained.

For interview purposes, the common pattern to remember is:

BUILD IMMEDIATE
+
FAST
+
ON COMMIT

For unusual combinations, verify the exact Oracle version and MV capabilities rather than assuming every BUILD/refresh combination is supported.

38. Common ON COMMIT interview traps

Trap 1

Does ON COMMIT mean refresh after every INSERT?

❌ No.

The key event is the commit.

Trap 2

Does ON COMMIT mean COMPLETE refresh?

❌ No.

ON COMMIT controls timing, not the refresh algorithm.

Trap 3

Is ON COMMIT the same as ON DEMAND?

❌ No.

ON COMMIT  → commit-based refresh
ON DEMAND  → requested refresh

Trap 4

Does ON COMMIT require FAST refresh capabilities?

For practical commit-time MV designs, yes, you need a supported refresh method/definition; ON COMMIT is commonly used with FAST-refreshable MVs.

Trap 5

Does ON COMMIT make the MV real-time?

❌ Not exactly.

It provides commit-time maintenance, not continuously synchronized data.

Trap 6

Can ON COMMIT increase application commit time?

✅ Yes.

MV maintenance can add work to the transaction.

Trap 7

Is ON COMMIT always better than ON DEMAND?

❌ No.

The choice depends on freshness requirements and workload.

39. FAST + ON COMMIT vs FAST + ON DEMAND

This is a very useful comparison:

FAST
|
+------+------+
|             |
↓             ↓
ON COMMIT       ON DEMAND
|             |
↓             ↓
At COMMIT      When requested
|             |
↓             ↓
More current      More control
but possible      over refresh
commit overhead

40. Complete example

Step 1 — Create base table

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

Step 2 — Insert initial 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 log

CREATE MATERIALIZED VIEW LOG ON sales
WITH ROWID, SEQUENCE
INCLUDING NEW VALUES;

Step 4 — Create ON COMMIT MV

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

Step 5 — Insert new sale

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

At this point:

INSERT
↓
Transaction still open

Step 6 — Commit

COMMIT;

Conceptually:

COMMIT
↓
ON COMMIT refresh
↓
FAST maintenance
↓
MV reflects committed data

The result for product 101 becomes:

SALE_COUNT = 3
TOTAL      = 1000

41. When should you choose ON COMMIT?

A good rule is:

Choose ON COMMIT when:

Need relatively current MV
+
MV supports efficient commit-time refresh
+
Commit overhead is acceptable

Example:

Small/medium transactional workload
↓
FAST-refreshable MV
↓
Dashboard needs current summaries
↓
ON COMMIT

Prefer ON DEMAND when:

High-volume OLTP
+
Large MV
+
Reports can tolerate some staleness

Then:

ON DEMAND
+
Scheduled FAST/COMPLETE refresh

is often a better architecture.

42. Final cheat sheet

MATERIALIZED VIEW
|
+---------+---------+
|                   |
↓                   ↓
HOW?                 WHEN?
|                   |
+----+----+         +----+----+
|    |    |         |         |
FAST COMPLETE FORCE ON COMMIT ON DEMAND
|    |    |         |         |
↓    ↓    ↓         ↓         ↓
Incremental Complete   Commit    Requested
FAST if
possible
otherwise
COMPLETE

Remember:

FAST       = HOW? Incremental
COMPLETE   = HOW? Recalculate all
FORCE      = HOW? FAST, otherwise COMPLETE

ON COMMIT  = WHEN? At commit
ON DEMAND  = WHEN? When requested

43. Most important interview answers

What is ON COMMIT?

ON COMMIT tells Oracle to refresh a supported materialized view as part of commit processing for changes to its underlying tables.

ON COMMIT vs ON DEMAND?

ON COMMIT refreshes at commit time; ON DEMAND refreshes when an explicit or scheduled refresh request is made.

Does ON COMMIT mean FAST?

No. They describe different dimensions: ON COMMIT is refresh timing, while FAST describes the refresh method. In practical Oracle designs, ON COMMIT is commonly paired with FAST refresh.

Does ON COMMIT require an MV log?

Not simply because it is ON COMMIT. However, FAST-refreshable definitions commonly require appropriate materialized view logs.

Does ON COMMIT increase transaction overhead?

Yes, it can, because MV maintenance is associated with commit processing.

Does ROLLBACK refresh the MV?

No committed change exists after a rollback, so the rolled-back change isn't reflected in the MV.

Best memory trick:

ON COMMIT = refresh with the commit; ON DEMAND = refresh when you ask for it.

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.