Forum Stats

  • 3,770,131 Users
  • 2,253,073 Discussions
  • 7,875,337 Comments

Discussions

Materialized view refresh is taking too much time

MKR
MKR Member Posts: 46 Red Ribbon
edited May 13, 2021 5:56AM in SQL & PL/SQL

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

Tagged:
«13

Answers