select count(trunc(nvl(last_refresh_date,sysdate-1)))
into l_mv_date
from all_mviews
where owner = I.source_owner
and mview_name = I.source_name
and trunc(nvl(last_refresh_date,sysdate-1)) < trunc(sysdate);
IF l_mv_date > 0 THEN
dbms\_output.put\_line('Refreshing old MV: '||I.source\_owner||'.'||I.source\_name);
dbms\_mview.refresh(I.source\_owner||'.'||I.source\_name,
atomic\_refresh => FALSE);
ELSE
dbms_output.put_line('MV Refresh Not Required');
END IF;
am using this code to refresh the MVs, i need 3 MVs to refresh every day but all three together taking 9-10 hrs to refresh, the MVs built based on two selects with a union all three Mvs are same
there are no any indexes on Mvs, but would like to reduce the refresh time
Oracle version : Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
need some suggestions on improving the refresh performance, please help here community