Oracle Materialized View FAST REFRESH FAQs with Examples

An Oracle FAST REFRESH updates a materialized view using the changes made to the underlying tables, rather than rebuilding the entire materialized-view result.

The key idea is:

Base Table
↓
DML changes
↓
Materialized View Log
↓
FAST REFRESH
↓
Apply only relevant changes
↓
Updated Materialized View

This is different from COMPLETE REFRESH, which recomputes the materialized-view result.

1. What is FAST REFRESH?

FAST REFRESH attempts to refresh the materialized view incrementally.

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

When the base table changes, Oracle can use information recorded about those changes to update the MV.

Conceptually:

1 billion rows in SALES
↓
Only 100 rows changed
↓
FAST REFRESH
↓
Apply relevant changes
↓
MV updated

Instead of:

1 billion rows
↓
Recalculate everything
↓
COMPLETE REFRESH

2. What is the difference between FAST and COMPLETE refresh?

This is one of the most important interview questions.

Feature FAST COMPLETE
Incremental refresh ✅ ❌
Recomputes entire result Usually ❌ ✅
Uses change information ✅ ❌
Usually faster for small changes ✅ ❌
Eligibility requirements More Fewer
MV logs often required ✅ Not necessarily

FAST

Base table
↓
Changes
↓
Apply changes
↓
MV

COMPLETE

Base table
↓
Run defining query again
↓
Rebuild MV result

3. Does FAST refresh always work?

No.

This is a major interview trap.

Just writing:

REFRESH FAST

doesn't guarantee that Oracle can perform a fast refresh.

The materialized view must satisfy Oracle's fast-refresh eligibility requirements.

These depend on things such as:

  • The MV query
  • Joins
  • Aggregations
  • GROUP BY
  • Base tables
  • Materialized view logs
  • Required columns
  • Refresh type

So:

REFRESH FAST
≠
FAST REFRESH GUARANTEED

4. What is a materialized view log?

A materialized view log records changes made to a base table so Oracle can use those changes during fast refresh.

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

Then:

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

Conceptually:

SALES
 |
 +---- DML
 |
 ↓
MV LOG
 |
 ↓
FAST REFRESH
 |
 ↓
MV

5. Why is the materialized view log needed?

Suppose:

SALES = 500 million rows

Only:
200 rows changed

Without change information, determining the new MV result can require significant work.

A materialized view log records relevant changes.

500 million original rows
+
200 changes
↓
MV LOG
↓
FAST REFRESH

This allows Oracle to perform incremental maintenance when the MV is eligible.

6. Does every FAST refresh require an MV log?

No, not universally.

But materialized view logs are commonly required for fast refresh of materialized views based on base tables, depending on the MV definition and refresh scenario.

For interview purposes:

Materialized view logs are used to record changes needed for fast refresh.

Don't say:

"Every FAST refresh always requires a materialized view log."

There are exceptions and different fast-refresh mechanisms/scenarios.

7. What is WITH PRIMARY KEY?

A materialized view log can track changes using primary keys.

CREATE MATERIALIZED VIEW LOG ON customers
WITH PRIMARY KEY;

Conceptually:

Base table
↓
Primary key identifies changed rows
↓
MV log
↓
FAST REFRESH

This is useful when the materialized view is designed around primary-key-based refresh.

8. What is WITH ROWID?

A materialized view log can record row identifiers.

CREATE MATERIALIZED VIEW LOG ON sales
WITH ROWID;

Conceptually:

SALES
↓
ROWID information
↓
MV LOG
↓
FAST REFRESH

The appropriate log configuration depends on the MV definition and Oracle's fast-refresh requirements.

9. What does INCLUDING NEW VALUES mean?

Example:

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

For updates, this allows the materialized view log to record the new values of relevant columns.

This can be required for particular fast-refresh scenarios, especially when updated values are needed to calculate the new materialized-view result.

10. Can FAST refresh be used with aggregate queries?

Yes, provided the materialized view satisfies Oracle's fast-refresh requirements.

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

Then:

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;

This can allow incremental maintenance when the necessary requirements are satisfied.

11. Can FAST refresh be used with joins?

Yes, many join materialized views can be fast refreshed, provided they meet Oracle's eligibility rules.

CREATE MATERIALIZED VIEW LOG ON customers
WITH PRIMARY KEY;

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

Possible MV:

CREATE MATERIALIZED VIEW mv_customer_sales
REFRESH FAST
ON DEMAND
AS
SELECT c.customer_id,
       c.customer_name,
       SUM(s.amount) AS total_sales
FROM customers c
JOIN sales s
ON c.customer_id = s.customer_id
GROUP BY c.customer_id,
         c.customer_name;

Whether this exact definition is fast-refreshable depends on the complete Oracle version-specific eligibility rules.

12. How do I check whether a materialized view is fast-refreshable?

Oracle provides materialized-view analysis facilities.

A common approach is:

BEGIN
    DBMS_MVIEW.EXPLAIN_MVIEW(
        'SELECT product_id,
                SUM(amount)
         FROM sales
         GROUP BY product_id'
    );
END;
/

The results can be inspected through the appropriate materialized-view capability table, depending on your Oracle setup.

For example, you may examine:

SELECT *
FROM MV_CAPABILITIES_TABLE;

This can help identify why a particular MV is or isn't eligible for fast refresh.

13. What if FAST refresh is not possible?

If you explicitly request:

REFRESH FAST

but the MV doesn't meet the requirements, the refresh can fail rather than simply behaving like a complete refresh.

If you want Oracle to attempt fast refresh and fall back to complete refresh, use an appropriate FORCE refresh configuration.

Conceptually:

FORCE
↓
FAST possible?
├── Yes → FAST
└── No  → COMPLETE

14. What is REFRESH FORCE?

FORCE tells Oracle to use fast refresh when possible and otherwise use complete refresh.

CREATE MATERIALIZED VIEW mv_product_sales
REFRESH FORCE
ON DEMAND
AS
SELECT product_id,
       SUM(amount) AS total_amount
FROM sales
GROUP BY product_id;

Think:

FORCE
 |
 +-- FAST possible → FAST
 |
 +-- FAST impossible → COMPLETE

15. What is FAST ON DEMAND?

Example:

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

This means:

BUILD IMMEDIATE
↓
Populate initially

FAST
↓
Use incremental refresh when eligible

ON DEMAND
↓
Refresh when explicitly requested/scheduled

To refresh:

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

16. What is FAST ON COMMIT?

You can specify:

REFRESH FAST
ON COMMIT

Example:

CREATE MATERIALIZED VIEW mv_emp_summary
BUILD IMMEDIATE
REFRESH FAST
ON COMMIT
AS
SELECT department_id,
       COUNT(*) AS employee_count
FROM employees
GROUP BY department_id;

Conceptually:

INSERT / UPDATE / DELETE
↓
Transaction
↓
COMMIT
↓
FAST refresh

This can provide relatively fresh MV data, but maintaining the MV can add overhead to DML and commit processing.

The MV must also satisfy the requirements for the requested refresh mode.

17. What is the difference between FAST ON COMMIT and FAST ON DEMAND?

Feature FAST ON COMMIT FAST ON DEMAND
Refresh method FAST FAST
Refresh timing Commit Explicit/scheduled
Automatic ✅ ❌
DML/commit overhead Higher Usually lower
User controls refresh ❌ ✅

Remember:

FAST = HOW
ON COMMIT / ON DEMAND = WHEN

18. Does FAST refresh mean only changed rows are copied?

Not necessarily.

This is a subtle but important point.

FAST refresh means Oracle uses incremental change information rather than simply rebuilding the entire materialized-view result.

For an aggregate MV, Oracle may need to:

  • Add values
  • Subtract old values
  • Recalculate aggregates
  • Update affected groups

So think:

Incremental maintenance, not simply "copy changed rows."

19. Example of FAST refresh with an aggregate

Suppose:

SALES
contains:

PRODUCT_ID   AMOUNT
-------------------
101          500
101          300
102          1000

MV:

PRODUCT_ID   TOTAL_AMOUNT
------------------------
101          800
102          1000

Now insert:

INSERT INTO sales
VALUES (4, 101, DATE '2026-08-03', 2, 200);

COMMIT;

A fast refresh can conceptually perform:

Old total for 101 = 800
New sale          = +200
-----------------------
New total         = 1000

So:

MV
101 → 800

becomes:

MV
101 → 1000

without simply rebuilding the entire aggregation from scratch.

20. What happens with UPDATE during FAST refresh?

Suppose:

UPDATE sales
SET amount = 700
WHERE sale_id = 1;

The old value might be:

500

and the new value:

700

The logical aggregate change is:

-500
+700
----
+200

The MV's aggregate can therefore be adjusted accordingly when the MV is eligible for fast refresh and the required change information is available.

This is why updated values may be required in the materialized view log.

21. What happens with DELETE during FAST refresh?

Suppose:

DELETE FROM sales
WHERE sale_id = 1;

If the deleted sale was:

PRODUCT_ID = 101
AMOUNT     = 500

the aggregate can conceptually be adjusted:

Old total = 1000
Delete    = -500
----------------
New total = 500

Again, this is the idea behind incremental maintenance.

22. Does FAST refresh make the MV always current?

No.

FAST describes how the MV is refreshed.

It doesn't necessarily determine when it is refreshed.

For example:

REFRESH FAST ON DEMAND

Base table changes
↓
MV becomes stale
↓
No automatic refresh
↓
Explicit refresh
↓
FAST refresh

So:

FAST ≠ real-time

23. Can a FAST-refresh MV become stale?

Yes.

Example:

10:00 → MV refreshed
10:05 → SALES changed
10:06 → MV not refreshed

The MV can now be:

STALENESS = STALE

Check:

SELECT mview_name,
       staleness,
       last_refresh_type,
       last_refresh_date
FROM user_mviews;

24. How do I manually perform a FAST refresh?

If the MV is defined for fast refresh:

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

Here:

F = FAST

For a complete refresh:

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

Here:

C = COMPLETE

25. Can I refresh multiple materialized views?

Yes.

For example:

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

This requests fast refresh for the listed materialized views.

26. What is the difference between FAST and FORCE in DBMS_MVIEW.REFRESH?

Conceptually:

FAST
↓
Must perform fast refresh

FORCE
↓
Try FAST
↓
If not possible
↓
COMPLETE

The exact API syntax and behavior should be checked against the Oracle version being used, but the interview distinction is:

FAST requests incremental refresh; FORCE allows Oracle to choose FAST or COMPLETE.

27. What are common reasons a materialized view cannot FAST refresh?

Typical reasons include:

  • MV query isn't fast-refreshable
  • Required MV logs are missing
  • Required columns aren't recorded
  • Unsupported query constructs
  • Required keys aren't available
  • Certain joins/aggregations don't meet requirements
  • MV definition violates fast-refresh rules

The exact rules depend on the materialized-view type and Oracle version.

When troubleshooting, use:

DBMS_MVIEW.EXPLAIN_MVIEW

to determine capabilities.

28. What is the role of primary keys in FAST refresh?

Primary keys can help Oracle identify changed rows.

Example:

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

Create the log:

CREATE MATERIALIZED VIEW LOG ON customers
WITH PRIMARY KEY;

Now Oracle can use primary-key information for applicable fast-refresh operations.

29. What is the role of ROWID in FAST refresh?

ROWID can identify the physical row that changed.

Example:

CREATE MATERIALIZED VIEW LOG ON sales
WITH ROWID;

Conceptually:

SALE row
↓
ROWID
↓
MV LOG
↓
FAST REFRESH

Whether ROWID or primary keys are appropriate depends on the MV design.

30. Can FAST refresh work without GROUP BY?

Yes.

For example:

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

CREATE MATERIALIZED VIEW mv_sales
REFRESH FAST
ON DEMAND
AS
SELECT product_id,
       amount
FROM sales;

Whether this exact MV is fast-refreshable depends on the full definition and Oracle's eligibility rules.

FAST refresh is not limited only to aggregate MVs.

31. Can FAST refresh work with joins?

Yes, for supported join materialized views.

Example:

CREATE MATERIALIZED VIEW LOG ON customers
WITH PRIMARY KEY;

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

Then an appropriate join MV may be eligible for fast refresh.

However, don't memorize:

"All joins support fast refresh."

That's incorrect.

The specific query structure and logs determine eligibility.

32. Can FAST refresh work with COUNT?

Yes, aggregate materialized views can be fast-refreshable when the necessary requirements are met.

Example:

CREATE MATERIALIZED VIEW mv_dept_stats
REFRESH FAST
ON DEMAND
AS
SELECT department_id,
       COUNT(*) AS employee_count,
       SUM(salary) AS total_salary
FROM employees
GROUP BY department_id;

The exact MV log requirements depend on the query.

33. Why is COUNT(*) important in aggregate fast refresh?

For certain aggregate fast-refresh scenarios, Oracle requires sufficient information to maintain aggregates correctly.

For example:

SELECT department_id,
       COUNT(*),
       SUM(salary)
FROM employees
GROUP BY department_id;

The COUNT(*) can be important for tracking changes to groups, particularly when rows are inserted or deleted.

The precise eligibility rules should be verified with DBMS_MVIEW.EXPLAIN_MVIEW.

34. Does FAST refresh always perform less work than COMPLETE refresh?

Not necessarily.

FAST refresh is generally advantageous when the amount of changed data is relatively small.

For example:

Base data = 1 billion rows
Changes   = 100 rows

FAST → potentially excellent

But:

Base data = 1 billion rows
Changes   = 800 million rows

The benefit of incremental maintenance may be much smaller.

The best refresh strategy depends on the workload and MV design.

35. What is a real-world FAST refresh use case?

Suppose a data warehouse has:

SALES
= 2 billion rows

Management needs:

Daily product sales

Create:

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

Then:

CREATE MATERIALIZED VIEW mv_daily_product_sales
BUILD IMMEDIATE
REFRESH FAST
ON DEMAND
ENABLE QUERY REWRITE
AS
SELECT TRUNC(sale_date) AS sales_day,
       product_id,
       SUM(amount) AS total_sales
FROM sales
GROUP BY TRUNC(sale_date),
         product_id;

During the day:

2 billion existing rows
+
1 million new/changed rows
↓
MV log
↓
FAST refresh
↓
Update affected summaries

This can be much more efficient than completely recomputing the entire summary every time, assuming the MV is eligible for fast refresh.

36. FAST refresh interview traps

Trap 1

Does REFRESH FAST guarantee a fast refresh?

❌ No.

The MV must be eligible.

Trap 2

Does FAST mean real-time data?

❌ No.

FAST means incremental refresh method, not refresh frequency.

Trap 3

Does FAST mean only changed rows are copied?

❌ Not exactly.

It means Oracle uses incremental change information to maintain the MV.

Trap 4

Is a materialized view log always required?

❌ Not universally.

But logs are commonly required for fast-refresh scenarios.

Trap 5

Does FAST refresh automatically happen after INSERT?

❌ Not necessarily.

For example:

REFRESH FAST ON DEMAND

requires an explicit or scheduled refresh.

Trap 6

Is FAST the same as FORCE?

❌ No.

FAST
↓
Use fast refresh

FORCE
↓
Try fast
↓
Otherwise complete

Trap 7

Does FAST refresh always outperform COMPLETE refresh?

❌ No.

The amount of changed data and the MV/query design matter.

37. FAST vs COMPLETE vs FORCE

Feature FAST COMPLETE FORCE
Incremental ✅ ❌ If possible
Rebuild full result ❌ ✅ If FAST unavailable
MV log often useful/required ✅ ❌ If FAST chosen
Eligibility restrictions More Fewer More flexible
Fallback ❌ N/A COMPLETE

Easy memory:

FAST
↓
"Incrementally update"

COMPLETE
↓
"Rebuild everything"

FORCE
↓
"Try FAST, otherwise COMPLETE"

38. FAST refresh lifecycle

The easiest way to understand it is:

BASE TABLE
|
↓
INSERT/UPDATE/
DELETE
|
↓
MATERIALIZED VIEW
LOG
|
Changes recorded
|
↓
FAST REFRESH
|
Apply incremental
changes
|
↓
MATERIALIZED VIEW

For example:

SALES
100 million rows

↓

Only 1,000 rows changed

↓

MV LOG records changes

↓

FAST REFRESH

↓

MV updated incrementally

39. Complete practical example

Step 1 — Create the base table

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

Step 2 — Insert data

INSERT INTO sales
VALUES (1, 101, DATE '2026-08-01', 5, 500);

INSERT INTO sales
VALUES (2, 101, DATE '2026-08-02', 3, 300);

INSERT INTO sales
VALUES (3, 102, DATE '2026-08-02', 10, 1000);

COMMIT;

Step 3 — Create the materialized view log

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

Step 4 — Create the 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;

Step 5 — Check the result

SELECT *
FROM mv_product_sales;

Possible result:

PRODUCT_ID   SALE_COUNT   TOTAL_AMOUNT
---------------------------------------
101          2            800
102          1            1000

Step 6 — Insert another sale

INSERT INTO sales
VALUES (
    4,
    101,
    DATE '2026-08-03',
    2,
    200
);

COMMIT;

Because this MV is:

REFRESH FAST
ON DEMAND

the MV isn't necessarily refreshed immediately.

Step 7 — Perform FAST refresh

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

Now:

PRODUCT_ID   SALE_COUNT   TOTAL_AMOUNT
---------------------------------------
101          3            1000
102          1            1000

The important concept is:

101:
Old count   = 2
New sale    = +1
New count   = 3

Old total   = 800
New amount  = +200
New total   = 1000

40. How do I troubleshoot FAST refresh problems?

A good interview/production approach is:

Step 1 — Check the MV

SELECT mview_name,
       staleness,
       last_refresh_type,
       last_refresh_date
FROM user_mviews;

Step 2 — Check MV logs

SELECT log_owner,
       master,
       log_table
FROM user_mview_logs;

Step 3 — Analyze MV capabilities

Use:

BEGIN
    DBMS_MVIEW.EXPLAIN_MVIEW(
        'SELECT product_id,
                SUM(amount)
         FROM sales
         GROUP BY product_id'
    );
END;
/

Then inspect:

SELECT *
FROM MV_CAPABILITIES_TABLE;

This helps identify whether:

FAST_REFRESH = Y/N

and why.

41. Most important FAST refresh interview questions

Q: What is FAST refresh?

Incremental maintenance of a materialized view using changes in the underlying data rather than rebuilding the entire result.

Q: What is usually used to track those changes?

Materialized view logs, when required by the refresh scenario.

Q: Does FAST always work?

No. The materialized view must satisfy Oracle's fast-refresh eligibility requirements.

Q: FAST vs COMPLETE?

FAST applies incremental changes; COMPLETE rebuilds the materialized-view result.

Q: FAST vs FORCE?

FAST requests incremental refresh; FORCE tries FAST and can fall back to COMPLETE.

Q: FAST vs ON DEMAND?

FAST specifies how to refresh; ON DEMAND specifies when to refresh.

Q: Does FAST mean real-time?

No. A FAST-refresh MV can still be stale if it isn't refreshed.

42. Final FAST refresh cheat sheet

MATERIALIZED VIEW
|
↓
REFRESH FAST
|
+-----------+-----------+
|                       |
↓                       ↓
MV LOG /                Incremental
change tracking         maintenance
|                       |
+-----------+-----------+
            ↓
       Updated MV

Remember these four words:

FAST = HOW
LOG  = CHANGES
ON DEMAND = WHEN
COMPLETE = REBUILD

And the most important interview statement:

Oracle FAST refresh incrementally maintains an eligible materialized view using recorded changes from its base tables, often through materialized view logs, instead of recomputing the entire materialized-view result.

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)