By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,704 Members | 1,076 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,704 IT Pros & Developers. It's quick & easy.

Materlized view Refresh Interval

P: n/a
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
Share this Question
Share on Google+
2 Replies


P: n/a
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

P: n/a
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.