Materlized view Refresh Interval

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • AJ
    #1

    Materlized view Refresh Interval

    Hi,

    I have a materialized view in oracle which is a complex view of 4
    tables which
    belongs to different schemas..

    create materialized view materialized_su m
    refresh complete
    start with sysdate
    next sysdate+1/24
    as
    select ano,sno,count(i d) assigned,
    count(decode(ss tno,70,1))compl etes,count(deco de(sstno,75,1)) ftq,
    (count(start_da te)-count(complete_ date))breakoffs
    from
    tab1,tab2,tab3, tab4
    where tab1.id=tab2.id and
    tab4.pkey=tab3. pkey
    and tab2.fkey=tab3. pkey
    group by ano,sno;

    Now the problem is the refresh interval...Is there any way I can
    specify a fixed refresh interval...What happens is after the first
    refresh let us say at 10.00.00 am ..next refresh should be at 11.00
    am..instead of that, the interval slips by 30-60 seconds..so the next
    refresh would be at 11.00.30 am..

    I have a dependent job which runs after the view is refreshed....


    Any help is greatly appreciated..

    Thanks
    Arpit
    Tags: None
    • Frank van Bortel
      #2
      Re: Materlized view Refresh Interval

      AJ wrote:
      Hi,
      >
      I have a materialized view in oracle which is a complex view of 4
      tables which
      belongs to different schemas..
      >
      create materialized view materialized_su m
      refresh complete
      start with sysdate
      next sysdate+1/24
      as
      select ano,sno,count(i d) assigned,
      count(decode(ss tno,70,1))compl etes,count(deco de(sstno,75,1)) ftq,
      (count(start_da te)-count(complete_ date))breakoffs
      from
      tab1,tab2,tab3, tab4
      where tab1.id=tab2.id and
      tab4.pkey=tab3. pkey
      and tab2.fkey=tab3. pkey
      group by ano,sno;
      >
      Now the problem is the refresh interval...Is there any way I can
      specify a fixed refresh interval...What happens is after the first
      refresh let us say at 10.00.00 am ..next refresh should be at 11.00
      am..instead of that, the interval slips by 30-60 seconds..so the next
      refresh would be at 11.00.30 am..
      >
      I have a dependent job which runs after the view is refreshed....
      >
      >
      Any help is greatly appreciated..
      >
      Thanks
      Arpit
      ....and you reschedule it every hour... The point is,
      jobs get reschedyled AFTER they complete; looks asif
      your job runs for about 30 secs.
      You better reschedule using trunc(sysdate) plus a fixed
      interval - you may round down to an hour, a minute, whatever.

      --

      Regards,
      Frank van Bortel

        Comment

        • Dave
          #3
          Re: Materlized view Refresh Interval

          aj70000@hotmail .com (AJ) wrote in message news:<6097f505. 0404281355.6d08 95ac@posting.go ogle.com>...

          It looks like the next interval is being calculated after the refresh
          (perhaps for transactional reasons.)

          Just a guess, but how about trying this ....

          create materialized view materialized_su m
          refresh complete
          start with sysdate
          next to_date(to_char (sysdate, 'YYYYMMDDHH24') , 'YYYYMMDDHH24') +1/24
          Hi,
          >
          I have a materialized view in oracle which is a complex view of 4
          tables which
          belongs to different schemas..
          >
          create materialized view materialized_su m
          refresh complete
          start with sysdate
          next sysdate+1/24
          as
          select ano,sno,count(i d) assigned,
          count(decode(ss tno,70,1))compl etes,count(deco de(sstno,75,1)) ftq,
          (count(start_da te)-count(complete_ date))breakoffs
          from
          tab1,tab2,tab3, tab4
          where tab1.id=tab2.id and
          tab4.pkey=tab3. pkey
          and tab2.fkey=tab3. pkey
          group by ano,sno;
          >
          Now the problem is the refresh interval...Is there any way I can
          specify a fixed refresh interval...What happens is after the first
          refresh let us say at 10.00.00 am ..next refresh should be at 11.00
          am..instead of that, the interval slips by 30-60 seconds..so the next
          refresh would be at 11.00.30 am..
          >
          I have a dependent job which runs after the view is refreshed....
          >
          >
          Any help is greatly appreciated..
          >
          Thanks
          Arpit

            Comment

            Previous template Next
            Working...