Oracle Materialized View BUILD DEFERRED FAQs with Examples

In Oracle, BUILD DEFERRED means the materialized view is created without immediately populating its data.

The key idea is:

BUILD IMMEDIATE
    ↓
Create MV
    ↓
Populate data immediately

BUILD DEFERRED
    ↓
Create MV definition
    ↓
Data population is deferred
    ↓
Refresh/build later

1. What is BUILD DEFERRED in a materialized view?

BUILD DEFERRED tells Oracle not to populate the materialized view when it is created.

CREATE MATERIALIZED VIEW mv_emp_summary
BUILD DEFERRED
REFRESH COMPLETE
ON DEMAND
AS
SELECT department_id,
       COUNT(*) AS employee_count,
       SUM(salary) AS total_salary
FROM employees
GROUP BY department_id;

The materialized view is created, but its initial data population is deferred.

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

This is a common interview question.

BUILD IMMEDIATE

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

Oracle:

CREATE MV
   ↓
Execute query
   ↓
Populate MV
   ↓
Ready with data

BUILD DEFERRED

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

Oracle:

CREATE MV
   ↓
Create definition
   ↓
Do NOT populate result immediately
   ↓
Populate later

Simple comparison

Feature BUILD IMMEDIATE BUILD DEFERRED
MV created ✅ ✅
Initial data populated immediately ✅ ❌
Initial query execution Immediately Deferred
Useful for Ready-to-use MV Delayed initialization

3. Why would we use BUILD DEFERRED?

It is useful when you want to create the materialized-view definition now, but don't want to perform the potentially expensive initial population immediately.

For example, suppose:

SALES
= 1 billion rows

Creating:

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

could require a large initial operation.

With:

BUILD DEFERRED

you can separate:

MV definition creation
        ↓
Later initialization/refresh

This can be useful during deployment or when you want to control when the expensive initial population occurs.

4. Does BUILD DEFERRED mean the materialized view has no data?

Initially, its data is not populated by the creation operation.

Think of it as:

BUILD DEFERRED
       ↓
MV definition exists
       ↓
Initial population hasn't happened

You should not interpret BUILD DEFERRED as:

"This MV will never contain data."

It means the initial build is postponed.

5. Can I query a BUILD DEFERRED materialized view immediately?

You should not expect it to contain the normal query result immediately.

For example:

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

Immediately after creation, the MV has not been populated by that creation operation.

The intended next step is to perform the appropriate refresh/build operation.

6. How do I populate a BUILD DEFERRED materialized view?

You can use the materialized-view refresh mechanism.

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

Conceptually:

BUILD DEFERRED
      ↓
MV definition created
      ↓
DBMS_MVIEW.REFRESH
      ↓
Query executed
      ↓
MV populated

7. Can I use BUILD DEFERRED with REFRESH COMPLETE?

Yes.

CREATE MATERIALIZED VIEW mv_dept_salary
BUILD DEFERRED
REFRESH COMPLETE
ON DEMAND
AS
SELECT department_id,
       SUM(salary) AS total_salary
FROM employees
GROUP BY department_id;

Later:

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

The complete refresh populates/rebuilds the materialized-view data.

8. Can I use BUILD DEFERRED with REFRESH FAST?

You can specify refresh characteristics, but fast refresh eligibility is a separate issue.

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

However, Oracle must determine whether the materialized view satisfies the requirements for fast refresh.

If the MV is not fast-refreshable, specifying REFRESH FAST does not magically make it eligible.

Interview point

BUILD DEFERRED
        ≠
FAST REFRESH

They solve different problems.

BUILD DEFERRED → When to perform initial population

REFRESH FAST   → How eligible changes are applied

9. What does BUILD DEFERRED actually defer?

It defers the initial population/build of the materialized-view data.

It does not mean:

"Refresh this MV later automatically."

That is controlled separately by refresh settings such as:

ON DEMAND
ON COMMIT
scheduled refresh

So don't confuse:

BUILD DEFERRED

with:

ON DEMAND

10. BUILD DEFERRED vs ON DEMAND

This is a very common interview question.

BUILD DEFERRED

Controls:

When is the MV initially populated?

ON DEMAND

Controls:

When is the MV refreshed after it has been populated?

Example:

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

Here:

BUILD DEFERRED
      ↓
Don't populate immediately

ON DEMAND
      ↓
Refresh when explicitly requested/scheduled

They are independent concepts.

11. Can I use BUILD DEFERRED with ON COMMIT?

The combination is subject to Oracle's materialized-view restrictions and refresh requirements.

More importantly, remember the conceptual difference:

BUILD DEFERRED
    ↓
Initial population timing

ON COMMIT
    ↓
Subsequent refresh timing

For practical designs, verify that the complete materialized-view definition is eligible for the requested refresh mode.

12. What happens if I create an MV with BUILD DEFERRED and never refresh it?

The materialized view remains unpopulated/not initialized for its intended use.

Example:

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

If you never perform the required initial build/refresh:

MV definition
     ↓
Exists

MV result data
     ↓
Not initialized/populated

So BUILD DEFERRED should generally be followed by a planned initial refresh/build.

13. Why is BUILD DEFERRED useful during deployment?

Imagine a production deployment:

Step 1 → Create MV
Step 2 → Application deployment
Step 3 → Large data load
Step 4 → Populate MV during maintenance window

If you use:

BUILD IMMEDIATE

the expensive population occurs during Step 1.

With:

BUILD DEFERRED

you can separate creation from the expensive initial population.

For large systems, this can make deployment planning easier.

14. Example with a large sales table

Suppose:

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

There are 500 million rows.

Create:

CREATE MATERIALIZED VIEW mv_monthly_sales
BUILD DEFERRED
REFRESH COMPLETE
ON DEMAND
AS
SELECT TRUNC(sale_date, 'MM') AS sales_month,
       product_id,
       SUM(amount) AS total_amount
FROM sales
GROUP BY TRUNC(sale_date, 'MM'),
         product_id;

At creation:

MV definition created
        ↓
500 million rows are NOT immediately aggregated

Later, during a maintenance window:

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

Then:

500 million rows
       ↓
Aggregation
       ↓
MV populated

15. Does BUILD DEFERRED improve query performance?

Not by itself.

This is an important interview trap.

BUILD DEFERRED is about when the initial MV population occurs, not about query optimization.

The performance benefit comes from the materialized view itself, because it stores precomputed results.

For example:

BUILD DEFERRED
       ↓
Controls initialization

Materialized View
       ↓
Can improve expensive query performance

16. Does BUILD DEFERRED save storage?

Not permanently.

It postpones population, but once the materialized view is populated, it requires storage for its data.

Think:

Before initial refresh
       ↓
No populated result

After refresh
       ↓
Stored MV data
       ↓
Storage required

17. Can I create indexes after the deferred build?

Yes, and this can be useful in deployment scenarios.

For example, after the MV has been populated:

CREATE INDEX idx_mv_product
ON mv_product_sales(product_id);

The exact deployment sequence depends on your performance and refresh requirements.

A common strategy is:

Create MV
   ↓
Initial refresh
   ↓
Create indexes
   ↓
Run reports

For very large MVs, index creation itself can be an expensive operation, so it should be planned appropriately.

18. What is the difference between BUILD DEFERRED and REFRESH COMPLETE?

They are not alternatives.

They describe different things.

BUILD DEFERRED
    ↓
Initial build is postponed

REFRESH COMPLETE
    ↓
When refreshed, rebuild the MV result completely

Example:

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

Interpretation:

BUILD DEFERRED
     +
REFRESH COMPLETE
     +
ON DEMAND

means:

Don't populate at CREATE time
             ↓
When refresh is requested
             ↓
Perform a complete refresh

19. BUILD IMMEDIATE vs BUILD DEFERRED interview example

Question

You have a table containing 2 billion rows and want to create a materialized view containing an expensive aggregation. You don't want the initial aggregation to run during deployment.

What would you use?

Answer

BUILD DEFERRED

Example:

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

Then populate it later:

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

20. Common BUILD DEFERRED interview traps

Trap 1

Does BUILD DEFERRED mean the MV is never populated?

❌ No.

It means the initial population is postponed.

Trap 2

Does BUILD DEFERRED mean ON DEMAND?

❌ No.

They control different things.

BUILD DEFERRED → initial population

ON DEMAND       → refresh control

Trap 3

Does BUILD DEFERRED mean FAST refresh?

❌ No.

BUILD DEFERRED → when initial build occurs

FAST           → how refresh can be performed

Trap 4

Does BUILD DEFERRED eliminate storage requirements?

❌ No.

It only postpones the initial population.

Trap 5

Does BUILD DEFERRED automatically schedule the initial refresh?

❌ No.

You need an appropriate refresh/build operation or schedule.

21. Quick comparison

Property BUILD IMMEDIATE BUILD DEFERRED
Create MV definition ✅ ✅
Populate during CREATE ✅ ❌
Initial query execution Immediate Deferred
Requires later population Usually no ✅
Controls refresh method ❌ ❌
Controls initial build ✅ ✅

22. Most important distinction to memorize

Think about three separate concepts:

             MATERIALIZED VIEW
                    |
        +-----------+-----------+
        |           |           |
        ↓           ↓           ↓
      BUILD       REFRESH     REFRESH
      timing      method       timing
        |           |           |
        ↓           ↓           ↓
 IMMEDIATE/     FAST/       ON DEMAND/
 DEFERRED       COMPLETE    ON COMMIT

BUILD

Answers:

When should the MV initially be populated?

REFRESH METHOD

Answers:

How should the MV data be refreshed?

Examples:

FAST
COMPLETE
FORCE

REFRESH TIMING

Answers:

When should refresh happen?

Examples:

ON DEMAND
ON COMMIT
scheduled refresh

23. One-line interview answer

BUILD DEFERRED in an Oracle materialized view postpones the initial population of the materialized-view data, allowing the MV definition to be created now and its data to be populated later through an appropriate refresh/build operation.

Easy memory trick

BUILD DEFERRED
      ↓
"Create now,
populate later."

That is the key point.

```

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)