DEV Community

Pranav Bakare
Pranav Bakare

Posted on

Materialized Views in Oracle with Fast Refresh | mrcaption49

Part 1 – Implementation: Creating Materialized View with FAST REFRESH

To enable FAST REFRESH in a materialized view, Oracle requires the existence of a Materialized View Log (MV log) on the base table. This log records the row-level changes (INSERT/UPDATE/DELETE) that occur after the materialized view is created. First, create the MV log using the CREATE MATERIALIZED VIEW LOG command on the base table, including primary key or rowid for tracking. Then, define the materialized view with REFRESH FAST option and link it to the base table. You can also schedule the refresh using START WITH and NEXT clauses or run it manually. The materialized view captures only the delta changes using the MV log and avoids full-table scans. Fast refresh reduces system overhead and improves performance. It’s most suitable for scenarios needing near real-time data synchronization. Oracle verifies the compatibility of the view definition with fast refresh rules before creation. If conditions aren't met, fast refresh won't be possible, and full refresh will be used instead.

Part 2 – Runtime Behavior: How Fast Refresh Works Using MV Log

When a materialized view is created with FAST REFRESH, Oracle relies on a materialized view log (MV log) to track changes (INSERT, UPDATE, DELETE) on the base table. This log captures only incremental modifications, enabling efficient delta refreshes without re-querying the entire source table. The MV log is a physical table (MLOG$_) containing metadata like DMLTYPE$$, SNAPTIME$$, and ROWID. When a DML operation occurs on the base table, it's recorded in the log. Upon triggering a fast refresh (manually or via a scheduled job), Oracle reads the log to update only the changed rows in the materialized view. Once the refresh completes, the log entries are purged automatically, ensuring it’s ready to capture the next set of changes. This keeps the MV log light and avoids stale data. The log won’t grow endlessly and is reused across refresh cycles. This mechanism significantly boosts performance in data warehousing and reporting systems.

🚀 Creating and Managing FAST Refresh Materialized Views in Oracle

In Oracle, Materialized Views (MVs) allow storing results of a query physically for faster access. Unlike views, which execute the query every time you select from them, MVs store the result and can be refreshed as needed.

The FAST REFRESH option enables Oracle to incrementally update the materialized view using only the rows that have changed — instead of re-running the entire query. This is extremely efficient for large data sets.

📘 Part 1 – Implementation: Setting Up a Fast Refresh MV

✅ Step 1: Create a Base Table
You created a simple employee table:

CREATE TABLE emp699 (
  emp_id    NUMBER PRIMARY KEY,
  emp_name  VARCHAR2(100),
  dept_id   NUMBER,
  salary    NUMBER
);
Enter fullscreen mode Exit fullscreen mode

Why it matters:
This table acts as the base data source for the materialized view. All DML (INSERT, UPDATE, DELETE) operations will be tracked here for incremental refresh.

✅ Step 2: Create the Materialized View Log

CREATE MATERIALIZED VIEW LOG ON emp699
WITH PRIMARY KEY, SEQUENCE
INCLUDING NEW VALUES;
Enter fullscreen mode Exit fullscreen mode

Explanation:

  • WITH PRIMARY KEY: Enables identification of rows uniquely for refresh.
  • SEQUENCE: Adds a monotonically increasing number to keep track of the order of changes.
  • INCLUDING NEW VALUES: Logs new values of changed rows, needed for REFRESH FAST.
  • 📌 This log is stored in a physical table like MLOG$_EMP699.

✅ Step 3: Create the Materialized View

CREATE MATERIALIZED VIEW emp699_mv
BUILD IMMEDIATE
REFRESH FAST ON DEMAND
AS
SELECT emp_id, emp_name, salary
FROM emp699
WHERE dept_id = 10;
Enter fullscreen mode Exit fullscreen mode

Explanation:

  • BUILD IMMEDIATE: Materialized view is populated immediately.
  • REFRESH FAST: Only changes (deltas) are applied, not the full query.
  • ON DEMAND: Refresh is manual or controlled via PL/SQL/scheduler.
  • WHERE dept_id = 10: Only rows with dept_id = 10 are included.

🔁 Part 2 – Runtime: How Oracle Tracks Changes and Refreshes MV

1️⃣ Data Change Tracking via MV Logs
When you insert or update a row:

INSERT INTO emp699 VALUES (5, 'Testing5', 10, 5000);
COMMIT;
Enter fullscreen mode Exit fullscreen mode
  • Oracle adds the DML to MLOG$_EMP699 with these columns:
  • DMLTYPE$$: I = Insert, U = Update, D = Delete
  • SNAPTIME$$: Timestamp of change
  • SEQUENCE$$: Order of changes
  • EMP_ID, SALARY, etc.: Logged columns (new values)

You can inspect the log via:

SELECT * FROM MLOG$_EMP699;
Enter fullscreen mode Exit fullscreen mode

2️⃣ Manual Refresh (Apply Changes)

BEGIN
  DBMS_MVIEW.REFRESH('EMP699_MV', 'F');
END;
Enter fullscreen mode Exit fullscreen mode

Explanation:

'F': Means FAST refresh — Oracle reads only the delta 
(changed rows) from MLOG$_EMP699.
Enter fullscreen mode Exit fullscreen mode

✅ After the refresh:

SELECT * FROM emp699_mv;
You will now see the new row with emp_id = 5.
Enter fullscreen mode Exit fullscreen mode

3️⃣ Auto Log Cleanup
Once the refresh is complete, Oracle automatically purges rows from the log (MLOG$_EMP699), keeping only unprocessed changes.

To verify:

SELECT COUNT(*) FROM MLOG$_EMP699;
Enter fullscreen mode Exit fullscreen mode

You should get 0 if everything has been applied.

🛡️ Best Practices

  • Practice Why It Matters
  • Use PRIMARY KEY or ROWID in MV log Required for fast refresh tracking.
  • Keep SELECT in MV simple Joins, subqueries, and aggregates can disqualify fast refresh.
  • Monitor MLOG size Prevent bloat; refresh regularly.
  • Use ON COMMIT for instant refresh If you want immediate updates (alternative to ON DEMAND).
  • Schedule refresh with DBMS_SCHEDULER Automate refresh for dashboards or reports.

✅ Summary Table

  • Feature Description
  • MV Refresh Type FAST
  • Triggered by Manual (DBMS_MVIEW.REFRESH) or Scheduled
  • MV Log Cleanup Automatic
  • Log Table MLOG$_
  • Performance High (only delta rows processed)
  • Real Example emp699, emp699_mv

📌 Final Thoughts

  • Materialized Views with FAST REFRESH are ideal for near real-time reporting where only a subset of data changes. With proper MV log setup, you ensure efficient, lightweight, and consistent synchronization — which can massively reduce CPU and I/O pressure.

✅ Fast Refresh & Materialized View Logs — Explained

When we create a Materialized View (MV) with FAST REFRESH, Oracle does not scan the entire base table during refresh. Instead, it uses Materialized View Logs (MV Logs) — which are special transactional log tables that capture row-level changes (INSERT, UPDATE, DELETE) made to the base table since the last refresh.

🔹 Key points:

  1. MV Log Creation: You need to manually create the materialized view log on the base table using:

CREATE MATERIALIZED VIEW LOG ON your_base_table WITH ROWID;

  1. Automatic Tracking:
    Once created, Oracle automatically maintains this log by storing the changes to the base table.

  2. During Fast Refresh:
    Oracle reads from the MV log to identify only the changes (deltas) and applies them to the materialized view — which makes it much faster than a full refresh.

  3. Post Refresh Cleanup:
    After a successful fast refresh, Oracle automatically purges the log entries that are no longer needed (i.e., those changes that have been applied).

  4. Efficiency:
    This approach saves time and minimizes I/O — making it ideal for reporting systems where data is refreshed frequently.

So yes — while you manually create the log, Oracle takes care of populating it, using it during refresh, and cleaning it afterward. This is why fast refresh is only possible if MV logs exist on the base tables.

Top comments (0)