Materialized View Log in Oracle 19c (ON COMMIT)

In Oracle Database 19c, REFRESH FAST ON COMMIT allows a Materialized View (MV) to be refreshed automatically and incrementally every time a transaction commits on the master table.

This feature provides near real-time synchronization between the base table and the materialized view — but it must be used carefully in production systems.

1. What Is REFRESH FAST ON COMMIT?

FAST ON COMMIT means:

• Refresh happens automatically at COMMIT time
• Only changed rows are applied (incremental refresh)
• Requires a Materialized View Log
• Occurs within the same user transaction

In other words:

When a user commits a DML operation, Oracle refreshes the MV immediately before completing the commit.

2. Architecture Overview

The process works as follows:

1. User performs INSERT/UPDATE/DELETE on master table
2. Changes are recorded in the Materialized View Log (MLOG$ table)
3. User executes COMMIT
4. Oracle triggers incremental refresh
5. Commit completes

This ensures transactional consistency.

3. Prerequisites

To use FAST ON COMMIT, you must:

• Have a primary key (recommended)
• Create a Materialized View Log
• Ensure query supports FAST refresh

4. Example

Step 1 – Create Master Table

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

Step 2 – Create Materialized View Log

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

INCLUDING NEW VALUES is mandatory for UPDATE support.

Step 3 – Create Materialized View

CREATE MATERIALIZED VIEW mv_sales
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS
SELECT sale_id, customer_id, amount
FROM sales;

Now, every COMMIT on sales automatically refreshes mv_sales.

5. How to Verify Refresh Type

SELECT mview_name, refresh_mode, refresh_method FROM dba_mviews WHERE mview_name = 'MV_SALES';

6. Performance Impact

Because refresh occurs during COMMIT:

• Commit time increases
• Redo/Undo increases
• Transaction latency rises
• High concurrency may suffer

In heavy OLTP systems, this can become a bottleneck.

7. Conclusion

REFRESH FAST ON COMMIT in Oracle 19c provides real-time materialized view synchronization but introduces transaction overhead. It should be implemented only when immediate consistency outweighs performance cost.