Oracle Materialized View. Build Deferred clause

Question

I created my materialized view with the BUILD DEFERRED clause

CREATE MATERIALIZED VIEW mcastfilm 
BUILD DEFERRED
REFRESH FORCE ON COMMIT
AS SELECT ms.name moviename,r.role,a.first_name, a.last_name FROM moviesmall ms, role r , actor a 
WHERE ms.ID = r.movie_id
 AND r.actor_id = a.id

It is empty for now. As I read from Oracle Documentation:

DEFERRED Specify DEFERRED to indicate that the materialized view is to be populated by the next REFRESH operation. The first (deferred) refresh must always be a complete refresh. Until then, the materialized view has a staleness value of UNUSABLE, so it cannot be used for query rewrite.

.. what I expect is the view to be populated at my first insert over one table:

insert into moviesmall VALUES (400001,'My movie',2050,null);
commit;

However, my view is still empty. I had to execute :

EXEC DBMS_MVIEW.REFRESH('mcastfilm');

.. to get my view updated.

My question is, what Oracle means with "The first (deferred) refresh must always be a complete refresh"?. As I understand, the "refresh" is specified by the clause "REFRESH FORCE ON COMMIT", so I expected my view to be updated when I commit one table.

Littlefoot Littlefoot · Accepted Answer · 2021-08-07T11:12:31

The way I understand what documentation says, it is exactly what you did:

EXEC DBMS_MVIEW.REFRESH('mcastfilm');

That's

The first (deferred) refresh must always be a complete refresh

Oracle Materialized View. Build Deferred clause

1 Answers