Oracle SQL Experts: 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.

Home

Oracle Materialized View ON COMMIT FAQs with Examples

1. What is ON COMMIT refresh?

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

3. Can ON COMMIT be used with FAST refresh?

4. Can ON COMMIT be used with COMPLETE refresh?

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

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

7. What happens if I ROLLBACK?

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

9. Does ON COMMIT increase transaction time?

10. Why can ON COMMIT be expensive?

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

12. What is a materialized view log?

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

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

15. Can I use FORCE ON COMMIT?

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

17. What is a practical example?

18. Example with multiple INSERT statements

19. What happens with UPDATE?

20. What happens with DELETE?

21. What happens if a transaction rolls back?

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

23. Is ON COMMIT suitable for large OLTP tables?

24. When is ON COMMIT useful?

25. When should I prefer ON DEMAND?

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

27. Can I manually refresh an ON COMMIT MV?

28. How do I check the MV refresh configuration?

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

30. Can ON COMMIT refresh fail?

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

32. Can ON COMMIT be used for aggregate MVs?

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

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

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

36. BUILD IMMEDIATE + ON COMMIT

37. Can BUILD DEFERRED be used with ON COMMIT?

38. Common ON COMMIT interview traps

39. FAST + ON COMMIT vs FAST + ON DEMAND

40. Complete example

41. When should you choose ON COMMIT?

42. Final cheat sheet

43. Most important interview answers

No comments:

Post a Comment