469,167 Members | 1,279 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,167 developers. It's quick & easy.

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
Jul 19 '05 #1
2 12010
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

Jul 19 '05 #2
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

Jul 19 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Conax | last post: by
3 posts views Thread by M. Mehta | last post: by
7 posts views Thread by Willem | last post: by
43 posts views Thread by dan baker | last post: by
12 posts views Thread by martin1 | last post: by
3 posts views Thread by marik | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.