Oracle Materialized View COMPLETE REFRESH FAQs with Examples

An Oracle COMPLETE REFRESH completely recomputes the materialized view from its defining query.

The key idea is:

Base Tables
     ↓
Run MV defining query again
     ↓
Recalculate complete result
     ↓
Replace/refresh MV data

Unlike FAST REFRESH, which incrementally applies changes, COMPLETE REFRESH does not depend on incremental change information from materialized view logs.

1. What is COMPLETE REFRESH?

COMPLETE REFRESH tells Oracle to re-execute the materialized view's defining query and refresh the entire result.

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

When the MV is completely refreshed, Oracle essentially recalculates:

SALES
  ↓
SELECT + GROUP BY + SUM
  ↓
Complete result
  ↓
MV

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

This is one of the most common interview questions.

Feature FAST COMPLETE
Refresh approach Incremental Recalculate entire result
Uses MV logs Often required Not required for refresh method
Query re-executed Incrementally maintained Yes
Eligibility restrictions More Fewer
Good for small changes Usually Not usually
Good for complex MV definitions Sometimes difficult Often easier
Can work without MV log Some scenarios Yes

FAST

Existing MV
    +
Changes
    ↓
Incremental update

COMPLETE

Base tables
    ↓
Run defining query again
    ↓
Rebuild complete result

3. How do I create a COMPLETE-refresh 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;

Here:

BUILD IMMEDIATE
    ↓
Initial population happens now

REFRESH COMPLETE
    ↓
Future refreshes recompute the complete result

ON DEMAND
    ↓
Future refresh happens when requested

4. Does COMPLETE refresh require a materialized view log?

No.

This is an important interview point.

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

A materialized view log is not required merely because the MV uses:

REFRESH COMPLETE

Why?

Because Oracle doesn't need a record of individual changes to incrementally maintain the MV.

It simply runs the defining query again.

5. Why doesn't COMPLETE refresh need an MV log?

Suppose:

SALES = 100 million rows

After changes:

500 rows inserted

A FAST refresh needs information about those changes.

A COMPLETE refresh simply does:

SELECT ...
FROM SALES
GROUP BY ...

again.

Conceptually:

FAST

100 million existing rows
        +
500 changes
        ↓
MV log
        ↓
Incremental maintenance

versus:

COMPLETE

100 million rows
        ↓
Run defining query
        ↓
Recalculate everything

6. Can I use COMPLETE with ON DEMAND?

Yes.

This is a very common combination.

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;

The MV is initially populated because of:

BUILD IMMEDIATE

Future refreshes happen when requested.

For example:

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

7. Can I use COMPLETE with ON COMMIT?

Oracle supports ON COMMIT only for appropriate refresh scenarios; importantly, ON COMMIT is not a general-purpose option for a complete-refresh materialized view.

The common pattern for complete refresh is:

REFRESH COMPLETE ON DEMAND

For transaction-driven refresh, fast refresh is typically the relevant approach when the MV meets the required restrictions.

So don't memorize:

COMPLETE + ON COMMIT

as a normal design pattern.

8. What happens during COMPLETE refresh?

Suppose the MV contains:

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

Then the base table changes:

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

COMMIT;

The MV might still contain:

101 → 800

After:

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

Oracle reruns the MV's defining query.

The result becomes:

101 → 1000

The important point is:

Oracle doesn't just add 200 to the MV

Instead, it performs the complete refresh according to the MV definition.

9. Does COMPLETE refresh recalculate all rows?

Conceptually, yes.

If your MV is:

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

a complete refresh recomputes the complete result from the underlying data.

For example:

EMPLOYEES
   ↓
Read relevant base data
   ↓
GROUP BY department_id
   ↓
SUM(salary)
   ↓
Complete MV result

It is not an incremental change-by-change maintenance operation like FAST refresh.

10. Does COMPLETE refresh mean the MV is always current?

No.

This is a very common interview trap.

Suppose:

CREATE MATERIALIZED VIEW mv_sales
REFRESH COMPLETE
ON DEMAND
AS
SELECT ...

Then:

10:00 → MV refreshed
10:05 → Base table changes
10:10 → No refresh

The MV can be stale.

COMPLETE ≠ REAL-TIME

COMPLETE tells Oracle how to refresh.

ON DEMAND tells Oracle when to refresh.

11. What is the difference between BUILD IMMEDIATE and REFRESH COMPLETE?

Another very common interview question.

BUILD IMMEDIATE

Controls the initial population.

CREATE MV
   ↓
Populate immediately

REFRESH COMPLETE

Controls the refresh method.

Refresh requested
   ↓
Recalculate complete MV result

Example:

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

Meaning:

BUILD IMMEDIATE
    → Build it now

COMPLETE
    → Recalculate entire result during refresh

ON DEMAND
    → Refresh when requested

12. Can COMPLETE refresh be used with BUILD DEFERRED?

Yes.

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

This means:

Create MV definition
       ↓
Don't populate initially
       ↓
Later perform refresh
       ↓
Complete result is generated

For example:

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

13. COMPLETE vs FAST with a simple example

Suppose the MV contains:

PRODUCT_ID   TOTAL_SALES
----------   -----------
101          10000
102          20000
103          30000

A new sale arrives:

PRODUCT 101
AMOUNT  500

FAST

Existing 101 total = 10000
New amount         = +500
                     ----
New total          = 10500

COMPLETE

Read SALES
   ↓
Re-run aggregation
   ↓
Calculate 101 = 10500
Calculate 102 = 20000
Calculate 103 = 30000
...
   ↓
Replace/refresh complete MV result

That's the fundamental difference.

14. Can COMPLETE refresh be useful for complex queries?

Yes.

One advantage is that complete refresh has fewer fast-refresh eligibility restrictions.

For example, suppose your MV uses a query that isn't eligible for fast refresh:

CREATE MATERIALIZED VIEW mv_complex_report
REFRESH COMPLETE
ON DEMAND
AS
SELECT ...
FROM orders o
JOIN customers c
    ON o.customer_id = c.customer_id
JOIN products p
    ON o.product_id = p.product_id
WHERE ...
GROUP BY ...;

If the query is valid for a materialized view but doesn't satisfy fast-refresh requirements, complete refresh may still be appropriate.

15. Does COMPLETE refresh require FAST-refresh eligibility?

No.

That's one of its major advantages.

FAST refresh has many eligibility requirements.

COMPLETE refresh essentially needs Oracle to be able to execute the MV's defining query and refresh the MV.

FAST
 ↓
More restrictions

COMPLETE
 ↓
Generally fewer refresh-eligibility restrictions

16. Can COMPLETE refresh be slower?

Yes.

For large MVs, complete refresh can be expensive.

Suppose:

SALES = 5 billion rows

and the MV contains an aggregation:

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

A complete refresh may need to process a large amount of base data again.

5 billion rows
      ↓
Read/process data
      ↓
GROUP BY
      ↓
SUM
      ↓
Rebuild MV result

This can require substantial:

  • CPU
  • I/O
  • TEMP space
  • Time

17. When should I use COMPLETE refresh?

COMPLETE refresh is often appropriate when:

  • The MV query isn't eligible for FAST refresh.
  • The underlying data changes substantially between refreshes.
  • The MV is relatively small.
  • Refreshes happen infrequently.
  • Simplicity is more important than incremental refresh.
  • The cost of maintaining MV logs isn't justified.
  • A periodic rebuild is acceptable.

Example:

Daily reporting MV
       ↓
Refresh once every night
       ↓
COMPLETE refresh

can be perfectly reasonable depending on data volume.

18. When is FAST preferable?

Suppose:

Base table = 1 billion rows
Changes per hour = 10,000 rows

A fast-refreshable MV may be a good candidate:

FAST
 ↓
Process incremental changes

rather than:

COMPLETE
 ↓
Reprocess huge base table

But if:

Base table = 1 million rows
Changes = 700,000 rows

the performance advantage of FAST may be much smaller.

19. What is REFRESH FORCE?

FORCE tells Oracle to attempt FAST refresh and use COMPLETE refresh if FAST isn't possible.

Example:

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

Conceptually:

REFRESH FORCE
      ↓
Can FAST refresh?
   /       \
 Yes        No
  ↓          ↓
FAST      COMPLETE

So:

FAST     → specifically request FAST
COMPLETE → specifically use COMPLETE
FORCE    → try FAST, otherwise COMPLETE

20. How do I manually perform a COMPLETE refresh?

Use DBMS_MVIEW.REFRESH.

For example:

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

Here:

C = COMPLETE

21. Can I refresh multiple MVs?

Yes.

Example:

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

This requests complete refresh for the specified MVs.

22. How do I check the last refresh type?

You can query:

SELECT mview_name,
       staleness,
       last_refresh_type,
       last_refresh_date
FROM user_mviews;

For example:

MVIEW_NAME          STALENESS   LAST_REFRESH_TYPE
------------------  ---------   ------------------
MV_PRODUCT_SALES    FRESH       COMPLETE

This is useful when troubleshooting or checking scheduled refreshes.

23. How do I check when the MV was last refreshed?

Use:

SELECT mview_name,
       last_refresh_date
FROM user_mviews;

Example:

MVIEW_NAME          LAST_REFRESH_DATE
------------------  -------------------------
MV_PRODUCT_SALES    11-AUG-26 02:00:00

24. Does COMPLETE refresh make the MV FRESH?

After a successful complete refresh, the MV should reflect the data available at the time of the refresh, subject to transaction consistency and Oracle's refresh behavior.

You can check:

SELECT mview_name,
       staleness,
       last_refresh_type
FROM user_mviews;

Typically:

STALENESS = FRESH

after a successful refresh.

Later base-table changes can make it stale again.

25. Does COMPLETE refresh use the MV log if one exists?

The existence of an MV log does not turn a complete refresh into a fast refresh.

If you explicitly perform:

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

you are requesting a complete refresh.

The important distinction is:

MV log exists
     ↓
Doesn't automatically mean FAST

26. Can I create an MV without specifying REFRESH COMPLETE?

Yes.

For example:

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

If you want the refresh behavior to be explicit, write:

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

Explicit definitions are easier to understand and maintain.

27. What happens if no rows changed in the base table?

A complete refresh still performs the complete-refresh operation.

Unlike FAST refresh, it doesn't simply say:

"No changes → do nothing"

The defining query is still used to produce the complete result.

This is one reason why repeatedly doing COMPLETE refreshes on very large MVs can be expensive.

28. Can COMPLETE refresh be scheduled?

Yes.

A common pattern is:

Every night at 2 AM
       ↓
Run DBMS_MVIEW.REFRESH
       ↓
COMPLETE refresh

For example, you could use Oracle Scheduler to run a refresh procedure.

Conceptually:

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

and schedule that operation.

29. What is a practical reporting example?

Suppose you have:

SALES
ORDERS
CUSTOMERS
PRODUCTS

and management needs a daily summary:

CREATE MATERIALIZED VIEW mv_daily_sales
BUILD IMMEDIATE
REFRESH COMPLETE
ON DEMAND
AS
SELECT TRUNC(s.sale_date) AS sales_day,
       p.product_id,
       p.product_name,
       SUM(s.amount) AS total_sales
FROM sales s
JOIN products p
    ON p.product_id = s.product_id
GROUP BY TRUNC(s.sale_date),
         p.product_id,
         p.product_name;

You might refresh it every night:

12:00 AM
   ↓
Complete refresh
   ↓
Recalculate daily report
   ↓
6:00 AM
   ↓
Reports use refreshed MV

This is a common data-warehouse/reporting pattern.

30. Complete practical example

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

Because we used:

BUILD IMMEDIATE

the MV is populated immediately.

Step 4 — Query the MV

SELECT *
FROM mv_product_sales;

Possible result:

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

Step 5 — Change the base table

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

COMMIT;

The MV may still show:

101 → 800

because:

REFRESH COMPLETE
ON DEMAND

doesn't mean automatic refresh.

Step 6 — Complete refresh

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

Now:

SELECT *
FROM mv_product_sales;

returns:

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

The important point is that Oracle recomputed the complete materialized-view result.

31. FAST vs COMPLETE: real-world example

Imagine:

SALES = 1 billion rows

Scenario A — only 10,000 rows changed

FAST
 ↓
Incremental maintenance

may be attractive.

Scenario B — 500 million rows changed

FAST
 ↓
Huge amount of incremental work

A complete refresh might become competitive or preferable depending on the MV and workload.

So don't use this rule:

"FAST is always faster."

Instead:

FAST is designed to avoid recomputing the entire result, but its actual performance depends on the amount and nature of changes.

32. Common COMPLETE refresh interview traps

Trap 1

Does COMPLETE refresh require an MV log?

❌ No.

Trap 2

Does COMPLETE refresh mean the MV is always current?

❌ No.

Refresh timing is separate.

Trap 3

Does BUILD IMMEDIATE mean COMPLETE refresh?

❌ No.

BUILD IMMEDIATE → initial population
COMPLETE         → refresh method

Trap 4

Does COMPLETE refresh use only changed rows?

❌ No.

It recalculates the complete result.

Trap 5

Is COMPLETE always slower than FAST?

❌ Not necessarily.

The workload and amount of changed data matter.

Trap 6

Can COMPLETE refresh be used when FAST refresh isn't supported?

✅ Yes. This is one of its major advantages.

Trap 7

Does ON DEMAND mean the MV refreshes automatically?

❌ No.

It means the refresh is requested explicitly or through a scheduling mechanism.

33. COMPLETE vs FAST vs FORCE cheat sheet

                 MATERIALIZED VIEW REFRESH
                           |
          +----------------+----------------+
          |                |                |
          ↓                ↓                ↓
        FAST           COMPLETE           FORCE
          |                |                |
          ↓                ↓                ↓
   Incremental       Recalculate       Try FAST
    maintenance      entire result         |
                                           ↓
                                   If FAST impossible
                                           ↓
                                       COMPLETE

FAST

Incrementally maintain the MV.

COMPLETE

Recalculate the entire MV result.

FORCE

Try FAST; if FAST isn't possible, use COMPLETE.

34. BUILD vs REFRESH — memorize this

This is probably the most useful interview shortcut:

BUILD
  ↓
What happens when MV is CREATED?

IMMEDIATE
  ↓
Populate now

DEFERRED
  ↓
Populate later

Then:

REFRESH
  ↓
What happens when MV is REFRESHED?

FAST
  ↓
Incremental

COMPLETE
  ↓
Entire result

FORCE
  ↓
FAST if possible
COMPLETE otherwise

And:

WHEN?
  ↓
ON DEMAND
ON COMMIT

35. One-line interview answers

What is COMPLETE refresh?

A complete refresh re-executes the materialized view's defining query and rebuilds the complete materialized-view result.

Does COMPLETE require MV logs?

No. MV logs are not required simply for complete refresh.

FAST vs COMPLETE?

FAST incrementally maintains the MV using recorded changes; COMPLETE recomputes the entire result.

Is COMPLETE always slower?

No. Performance depends on data volume, change volume, query complexity, and system resources.

Does COMPLETE mean automatic refresh?

No. COMPLETE specifies how to refresh; ON DEMAND or another refresh mechanism determines when it happens.

What is the easiest memory trick?

FAST     = CHANGE ONLY
COMPLETE = CALCULATE ALL
FORCE    = TRY FAST, ELSE COMPLETE
The most important distinction to remember is:

COMPLETE describes the refresh method, while BUILD IMMEDIATE/DEFERRED describes initial population and ON DEMAND/ON COMMIT describes refresh timing.

```
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)