Oracle Materialized View BUILD IMMEDIATE FAQs with Examples

In Oracle, BUILD IMMEDIATE means the materialized view is created and populated immediately when the CREATE MATERIALIZED VIEW statement executes.

The key idea is:

BUILD IMMEDIATE
       ↓
Create MV
       ↓
Execute MV query
       ↓
Store result immediately
       ↓
MV is populated

It is the opposite of BUILD DEFERRED.

1. What is BUILD IMMEDIATE?

BUILD IMMEDIATE tells Oracle to populate the materialized view immediately when it is created.

Example:

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;

When Oracle executes this statement:

CREATE MV
   ↓
Run SELECT
   ↓
Calculate summary
   ↓
Store result in MV

The materialized view is populated as part of its creation.

2. What is the difference between BUILD IMMEDIATE and BUILD DEFERRED?

This is one of the most common interview questions.

BUILD IMMEDIATE

CREATE MATERIALIZED VIEW mv_emp
BUILD IMMEDIATE
AS
SELECT *
FROM employees;

Conceptually:

CREATE MV
   ↓
Populate data immediately
   ↓
MV ready

BUILD DEFERRED

CREATE MATERIALIZED VIEW mv_emp
BUILD DEFERRED
AS
SELECT *
FROM employees;

Conceptually:

CREATE MV
   ↓
Create definition
   ↓
Don't populate immediately
   ↓
Populate later

Comparison

Feature BUILD IMMEDIATE BUILD DEFERRED
MV definition created ✅ ✅
Initial data populated immediately ✅ ❌
Initial query execution Immediate Deferred
Ready for normal querying immediately ✅ ❌ until populated
Large initial build cost At creation Delayed
Opposite option BUILD DEFERRED BUILD IMMEDIATE

3. Is BUILD IMMEDIATE the default?

Yes. If you don't specify a build clause, the materialized view is normally built immediately.

For example:

CREATE MATERIALIZED VIEW mv_emp
AS
SELECT *
FROM employees;

is effectively using immediate build behavior.

You can explicitly write:

CREATE MATERIALIZED VIEW mv_emp
BUILD IMMEDIATE
AS
SELECT *
FROM employees;

Being explicit can make the intention clearer.

4. Why would I use BUILD IMMEDIATE?

Use BUILD IMMEDIATE when you want the materialized view to be populated and ready for use immediately after creation.

For example:

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

After creation:

SELECT *
FROM mv_product_sales;

can immediately access the populated materialized-view result.

5. Does BUILD IMMEDIATE execute the materialized-view query?

Yes.

Suppose:

CREATE MATERIALIZED VIEW mv_dept_salary
BUILD IMMEDIATE
AS
SELECT department_id,
       SUM(salary) AS total_salary
FROM employees
GROUP BY department_id;

Oracle needs to populate the MV, so conceptually:

employees
    ↓
SELECT + GROUP BY + SUM
    ↓
Result
    ↓
mv_dept_salary

For a large underlying table, this initial operation can be expensive.

6. Does BUILD IMMEDIATE mean the materialized view will always have current data?

No.

This is an important interview trap.

BUILD IMMEDIATE only controls the initial population.

It does not control subsequent refreshes.

For example:

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

The sequence is:

CREATE MV
    ↓
Populate immediately
    ↓
Base table changes
    ↓
MV can become stale
    ↓
Refresh later

So:

BUILD IMMEDIATE
        ≠
Always up-to-date

7. What controls subsequent refreshes?

Refresh settings control what happens after the initial build.

For example:

REFRESH COMPLETE ON DEMAND

means:

BUILD IMMEDIATE
     ↓
Populate now

ON DEMAND
     ↓
Refresh later when requested/scheduled

Another example:

REFRESH FAST ON COMMIT

means:

BUILD IMMEDIATE
     ↓
Populate now

ON COMMIT
     ↓
Attempt refresh at transaction commit,
subject to MV eligibility/restrictions

8. BUILD IMMEDIATE vs ON COMMIT

Don't confuse these two.

BUILD IMMEDIATE

Answers:

When should the MV initially be populated?

ON COMMIT

Answers:

When should subsequent refreshes occur?

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:

CREATE
  ↓
BUILD IMMEDIATE
  ↓
Initial data populated
  ↓
Future DML
  ↓
COMMIT
  ↓
Refresh, if the MV is eligible

9. BUILD IMMEDIATE vs ON DEMAND

Example:

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;

This means:

BUILD IMMEDIATE
      ↓
Populate when created

ON DEMAND
      ↓
Future refresh is explicitly requested

For example:

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

10. Can BUILD IMMEDIATE be used with REFRESH COMPLETE?

Yes.

Example:

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;

Interpretation:

BUILD IMMEDIATE
    ↓
Populate now

REFRESH COMPLETE
    ↓
Future refreshes rebuild the result

ON DEMAND
    ↓
Future refresh happens when requested

These clauses control different aspects of the MV lifecycle.

11. Can BUILD IMMEDIATE be used with REFRESH FAST?

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

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;

If the MV is eligible for fast refresh, Oracle can use the fast-refresh mechanism when it is refreshed later.

Remember:

BUILD IMMEDIATE
      ↓
Initial population timing

REFRESH FAST
      ↓
Subsequent refresh method

12. Does BUILD IMMEDIATE require a materialized view log?

No, not simply because you use BUILD IMMEDIATE.

A materialized view log is generally relevant to fast refresh, not to the concept of immediate initial building itself.

For example:

CREATE MATERIALIZED VIEW mv_emp
BUILD IMMEDIATE
REFRESH COMPLETE
ON DEMAND
AS
SELECT department_id,
       COUNT(*)
FROM employees
GROUP BY department_id;

can be created without a materialized view log.

For fast-refresh scenarios, Oracle may require an appropriate materialized view log depending on the query and refresh requirements.

13. What happens if the base table contains 100 million rows?

Suppose:

EMPLOYEES = 100 million rows

You create:

CREATE MATERIALIZED VIEW mv_emp_summary
BUILD IMMEDIATE
AS
SELECT department_id,
       COUNT(*),
       SUM(salary)
FROM employees
GROUP BY department_id;

Oracle has to initially populate the MV.

Conceptually:

100 million rows
       ↓
SELECT
       ↓
GROUP BY
       ↓
SUM / COUNT
       ↓
Materialized View

Therefore, the CREATE MATERIALIZED VIEW operation can take significant time and resources.

If you don't want that operation during creation/deployment, BUILD DEFERRED may be more appropriate.

14. Does BUILD IMMEDIATE improve query performance immediately?

Potentially, yes—once the materialized view has been populated and you query the MV or Oracle can use it through query rewrite.

Example:

CREATE MATERIALIZED VIEW mv_product_sales
BUILD IMMEDIATE
ENABLE QUERY REWRITE
AS
SELECT product_id,
       SUM(amount) AS total_sales
FROM sales
GROUP BY product_id;

The MV is populated during creation.

After that, suitable reporting queries may benefit from the precomputed data.

15. What is the advantage of BUILD IMMEDIATE?

The main advantage is simplicity:

CREATE
  ↓
Populate
  ↓
Ready

You don't need a separate initial population step.

It's especially convenient when:

  • The underlying table is not huge.
  • You need the MV immediately.
  • Deployment can tolerate the initial build.
  • Reports must use the MV right away.

16. What is the disadvantage of BUILD IMMEDIATE?

The main disadvantage is the initial build cost.

Suppose the MV query is:

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

and sales contains billions of rows.

With:

BUILD IMMEDIATE

the expensive aggregation happens during MV creation.

Conceptually:

CREATE MATERIALIZED VIEW
          ↓
Expensive query
          ↓
CPU + I/O + TEMP + time
          ↓
MV populated

For a large production system, this may be undesirable during deployment.

17. When should I use BUILD DEFERRED instead?

Use BUILD DEFERRED when you want to create the MV definition now but populate it later.

Example:

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

Then later:

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

Think:

BUILD IMMEDIATE
    → "Populate now."

BUILD DEFERRED
    → "Populate later."

18. Can I use BUILD IMMEDIATE for reporting MVs?

Yes.

For example:

CREATE MATERIALIZED VIEW mv_monthly_sales
BUILD IMMEDIATE
REFRESH COMPLETE
ON DEMAND
ENABLE QUERY REWRITE
AS
SELECT TRUNC(sale_date, 'MM') AS sales_month,
       product_id,
       SUM(amount) AS total_sales
FROM sales
GROUP BY TRUNC(sale_date, 'MM'),
         product_id;

This is a typical reporting/data-warehouse style use case.

19. Can BUILD IMMEDIATE and ENABLE QUERY REWRITE be used together?

Yes.

Example:

CREATE MATERIALIZED VIEW mv_dept_sales
BUILD IMMEDIATE
ENABLE QUERY REWRITE
AS
SELECT department_id,
       SUM(amount) AS total_sales
FROM sales
GROUP BY department_id;

Here:

BUILD IMMEDIATE
       ↓
Populate MV immediately

ENABLE QUERY REWRITE
       ↓
Allow optimizer to consider MV for eligible queries

ENABLE QUERY REWRITE does not force Oracle to use the MV.

20. Does BUILD IMMEDIATE guarantee query rewrite?

No.

These are separate concepts.

BUILD IMMEDIATE
      ↓
Initial population

QUERY REWRITE
      ↓
Optimizer may use MV

Even if:

ENABLE QUERY REWRITE

is specified, Oracle's optimizer decides whether the MV can and should be used for a particular query.

21. Can I create indexes on a BUILD IMMEDIATE materialized view?

Yes.

Because the MV stores physical data, you can create indexes on it.

Example:

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

Then:

CREATE INDEX idx_mv_product_sales
ON mv_product_sales(product_id);

The initial MV population happens during creation, and the index can then be created on the populated result.

22. What happens if the MV query fails during BUILD IMMEDIATE?

Since Oracle must populate the MV during creation, an error encountered while creating/populating the materialized view can cause the creation operation to fail.

For example, if the defining query has an invalid reference:

CREATE MATERIALIZED VIEW mv_test
BUILD IMMEDIATE
AS
SELECT nonexistent_column
FROM employees;

the statement will fail.

With BUILD DEFERRED, the initial population is postponed, which changes when certain query execution/build issues are encountered.

23. Is BUILD IMMEDIATE the same as REFRESH COMPLETE?

No.

This is one of the most important distinctions.

BUILD IMMEDIATE
       ↓
Initial population timing

REFRESH COMPLETE
       ↓
Method used for a complete refresh

Example:

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

Means:

Create
 ↓
Populate immediately
 ↓
Later refreshes are COMPLETE
 ↓
Refresh occurs ON DEMAND

Three different concepts:

BUILD IMMEDIATE
REFRESH COMPLETE
ON DEMAND

24. What is a 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,
       SUM(quantity) AS total_quantity,
       SUM(amount) AS total_amount
FROM sales
GROUP BY product_id;

During the CREATE statement:

SALES
  ↓
SELECT + GROUP BY
  ↓
MV populated immediately

Step 4: Query the MV

SELECT *
FROM mv_product_sales;

Result:

PRODUCT_ID   TOTAL_QUANTITY   TOTAL_AMOUNT
----------   --------------   ------------
101          8                800
102          10               1000

The MV is already populated because we used:

BUILD IMMEDIATE

25. What happens after the base table changes?

Suppose:

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

COMMIT;

The MV was initially populated, but because we used:

ON DEMAND

it is not automatically refreshed just because the base table changed.

So the MV may still show:

101 → 800

Refresh it:

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

Now:

101 → 1000

because:

800 + 200 = 1000

26. Common BUILD IMMEDIATE interview traps

Trap 1

Does BUILD IMMEDIATE mean the MV is always current?

❌ No.

It only means the initial build occurs immediately.

Trap 2

Does BUILD IMMEDIATE mean ON COMMIT?

❌ No.

BUILD IMMEDIATE → initial population

ON COMMIT       → subsequent refresh timing

Trap 3

Does BUILD IMMEDIATE mean COMPLETE refresh?

❌ No.

BUILD IMMEDIATE → initial build timing

COMPLETE         → refresh method

Trap 4

Does BUILD IMMEDIATE require an MV log?

❌ No.

MV logs are mainly relevant to fast-refresh scenarios.

Trap 5

Does BUILD IMMEDIATE force query rewrite?

❌ No.

Query rewrite is a separate optimizer feature.

Trap 6

Does BUILD IMMEDIATE mean the MV query executes during every SELECT?

❌ No.

The whole purpose of the materialized view is to store the result. The defining query is used during initial build and refresh operations, rather than being rerun from scratch for every ordinary query against the MV.

27. BUILD IMMEDIATE vs BUILD DEFERRED cheat sheet

                 MATERIALIZED VIEW BUILD
                         |
              +----------+----------+
              |                     |
              ↓                     ↓
       BUILD IMMEDIATE       BUILD DEFERRED
              |                     |
              ↓                     ↓
       Populate now          Populate later
              |                     |
              ↓                     ↓
        Ready immediately     Needs later build/
                              refresh

BUILD IMMEDIATE

Create and populate now.

BUILD DEFERRED

Create now and populate later.

28. Complete MV lifecycle to remember

A useful interview model is:

             CREATE MATERIALIZED VIEW
                       |
                       ↓
                 BUILD OPTION
                  /          \
                 /            \
                ↓              ↓
        IMMEDIATE          DEFERRED
            |                  |
            ↓                  ↓
       Populate now       Populate later
            |                  |
            +--------+---------+
                     ↓
              REFRESH METHOD
               /      |      \
              ↓       ↓       ↓
            FAST   COMPLETE  FORCE
                     |
                     ↓
              REFRESH TIMING
                /         \
               ↓           ↓
          ON DEMAND     ON COMMIT

29. One-line interview answer

BUILD IMMEDIATE tells Oracle to create and populate the materialized view immediately, whereas BUILD DEFERRED creates the materialized-view definition but postpones its initial population.

Easy memory trick

BUILD IMMEDIATE
       ↓
"Create now + fill now"

BUILD DEFERRED
       ↓
"Create now + fill later"

The most important point is that BUILD IMMEDIATE controls the initial population, not the subsequent refresh schedule or refresh method.

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)