Materlized view Refresh Interval

[AJ]

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(ss tno,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

[Frank van Bortel]

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_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(ss tno,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

....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

[Dave]

aj*****@hotmail.com (AJ) wrote in message news:<60**************************@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(ss tno,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