[SOLVED] Materialized view needs to be compiled or refresh

Materialized view needs to be compiled or refresh

I have below script to create Materialized view with two tables involved.

I see data is automatically reflected when there a new data inserted in source tables after 24 hours as i mentioned REFRESH in my script .

Do i still need to refresh the Materialized view manually or This script is enough to take care of auto refresh . I am in confusion , please advise .

CREATE MATERIALIZED VIEW MVW_ITEM (Item_id ,Item_cost )
BUILD IMMEDIATE
USING INDEX
REFRESH COMPLETE START WITH SYSDATE+0 next SYSDATE + 1
USING DEFAULT LOCAL ROLLBACK SEGMENT
USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE

select Item_id , Item_cost from item_dict , item_dtls

where item_dict.item_id = item_dtls.item_id;

Solution

Using start with ... next ... in the MV definition automatically creates a job to refresh it for you.

So yes, the database will automatically refresh the MV for you every day given using START WITH SYSDATE+0 next SYSDATE + 1 (though there's no need to add zero to the start). No further action is needed from you.

You can view this job by querying *_scheduler_jobs (*_jobs on older versions).

create table t (
  c1 int
);

create materialized view mv 
refresh start with sysdate next sysdate + 1
as 
  select count(*) from t;
  
select job_name, job_action, start_date, next_run_date 
from   user_scheduler_jobs;

JOB_NAME           JOB_ACTION                               START_DATE                                      NEXT_RUN_DATE                         
MV_RF$J_0_S_172    dbms_refresh.refresh('"CHRIS"."MV"');    20-JUN-2023 09.57.13.000000000 EUROPE/LONDON    21-JUN-2023 09.57.13.000000000 GMT