Creating materialized view that refreshes every 5 min

Question

I created a materialized view that refreshed every 5 min but when I do insert and perform select on materialized view I get same old data? Do I need to refresh manually?

CREATE MATERIALIZED VIEW MVW_TEST
REFRESH FORCE ON DEMAND 
START WITH TO_DATE('01-01-2009 00:01:00', 'DD-MM-YYYY HH24:MI:SS') NEXT SYSDATE + 1/1152 
As select * from TEST12

are your inserts on base tables comitted? If not, you will not see the data. Plus you'll have to wait of course. — Roger Cornejo, Apr 09 '12 at 13:06

score 30 · Accepted Answer · edited Aug 12 '13 at 11:59

30

I have demonstrated in steps where a materialized view refresh after every one minute ,for having a mv which refresh after 5 minute use next(sysdate+5/1440)

Step1:

Create table temp (A int);

Step2:

Create Materialized view temp_mv
      refresh complete start with (sysdate) next  (sysdate+1/1440) with rowid
        as select * from temp;

Step3:

select count(*) from temp;

       COUNT(*)
      ----------
          0

Step4:

select count(*) from temp_mv;

       COUNT(*)
      ----------
          0

Step5:

begin
      for i in 1..10 loop
         insert into temp values (i+1);
      end loop;
end;
/

Step6:

commit;

Step7:

select count(*) from temp;

       COUNT(*)
     ----------
        10

Step8:

select count(*) from temp_mv;

       COUNT(*)
       ----------
          0

Step9:

select to_char(sysdate,'hh:mi') from dual;

       TO_CH
       -----
       04:28

Step10:

select to_char(sysdate,'hh:mi') from dual;

       TO_CH
        -----
       04:29

Step11:

select count(*) from temp;

      COUNT(*)
     ----------
        10

Step12:

select count(*) from temp_mv;

      COUNT(*)
      ----------
         10

edited Aug 12 '13 at 11:59

René Nyffenegger

39,402
33
158
293

answered Apr 08 '12 at 11:02

Gaurav Soni

6,278
9
52
72

What if the query take more time than allocated time to run? – kinkajou Apr 08 '12 at 11:15
@Kitex:That you need to take care and based on that increase your refresh time . – Gaurav Soni Apr 08 '12 at 11:21
3

That's not right. The next refresh is scheduled based on when the current refresh *finishes*. – Andrew Spencer Mar 14 '18 at 15:52
I have a 36 materialized views: I do not want them all trying to update at the same time. I have tried many different things but the next run time is always getting set to 3 min after the hour. Here is an example where I want it to start at 45Min after midnight, but it ends up being just 3 min after midnight: START WITH TRUNC(SYSDATE+1) + 3/96 – Bill Worthington Mar 15 '18 at 10:09
@BillWorthington You want to ask a separate question for that – Andrew Spencer Mar 16 '18 at 13:24

score 7 · Answer 2 · answered Jun 11 '15 at 23:04

You can also create a scheduler job:

BEGIN
  SYS.DBMS_SCHEDULER.CREATE_JOB
    (
       job_name        => 'DBAPER.MVW_TEST_REFRESH'
      ,start_date      => TO_TIMESTAMP_TZ('2011/09/02 00:00:00.000000 US/Central','yyyy/mm/dd hh24:mi:ss.ff tzr')
      ,repeat_interval => 'FREQ=DAILY;BYHOUR=6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22;BYMINUTE=0,5,10,15,20,25,30,35,40,45,50,55'
      ,end_date        => NULL
      ,job_class       => 'DEFAULT_JOB_CLASS'
      ,job_type        => 'PLSQL_BLOCK'
      ,job_action      => 'BEGIN 
                            DBMS_MVIEW.REFRESH(''MVW_TEST'',''C'');
                            END;'
      ,comments        => 'Job to refresh materialized view MVW_TEST.'
    );
END;
/

That way you have more configuration options, like excluding night hours or weekends for example.

That's the most sensible way to do it. You have complete control over your refresh cycle, you can easily disable the refresh when required without changing the Materialized View definition, and you can monitor it using user_scheduler_XXX views. — Frank Schmitt, Jun 19 '18 at 08:40

Creating materialized view that refreshes every 5 min

2 Answers2

Linked