Re: Materlized view Refresh Interval

aj70000_at_hotmail.com (AJ) wrote in message news:<6097f505.0404281355.6d0895ac_at_posting.google.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_sum 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_sum
> refresh complete
> start with sysdate
> next sysdate+1/24
> as
> select ano,sno,count(id) assigned,
> count(decode(sstno,70,1))completes,count(decode(sstno,75,1)) ftq,
> (count(start_date)-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
Received on Fri Apr 30 2004 - 15:18:51 CEST