Home » Server Options » Replication » Setup a refresh group for nested materialized views (Oracle Database 10.2.0.3)
Setup a refresh group for nested materialized views [message #393955] Wed, 25 March 2009 03:35 Go to next message
summoner
Messages: 44
Registered: March 2009
Member
Suppose I have materialized view A,B,C. Now I have a new materialized view D which is created from A,B,C.
Can I put D into same refresh group?

I am not sure whether D will be refreshed first before A,B,C, resulting that D do not get any update
If that is the case, the result will be wrong

If we cannot use refresh group to update nested materialized view, what should we do?
Thank you for your help
Re: Setup a refresh group for nested materialized views [message #393995 is a reply to message #393955] Wed, 25 March 2009 05:15 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member


>>Can I put D into same refresh group?

Yes. You can configure same refresh group.

PS: I hope your all constraints created with DIFFERABLE

Babu
Re: Setup a refresh group for nested materialized views [message #394003 is a reply to message #393955] Wed, 25 March 2009 05:37 Go to previous messageGo to next message
summoner
Messages: 44
Registered: March 2009
Member
summoner wrote on Wed, 25 March 2009 03:35
Suppose I have materialized view A,B,C. Now I have a new materialized view D which is created from A,B,C.
Can I put D into same refresh group?

I am not sure whether D will be refreshed first before A,B,C, resulting that D do not get any update
If that is the case, the result will be wrong

If we cannot use refresh group to update nested materialized view, what should we do?
Thank you for your help


Thanks
What if there are no constraints? Do I need to build one to tell the system how to refresh in order?
Re: Setup a refresh group for nested materialized views [message #394009 is a reply to message #394003] Wed, 25 March 2009 05:59 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member


>>What if there are no constraints? Do I need to build one to tell the system how to refresh in order?

If you don't have any constraints (like foreign key constraints) in materialized view environment; Then no issues. Suppose if you have then you need look.

Are you using Basic/Advanced Replication?

Babu
Re: Setup a refresh group for nested materialized views [message #394161 is a reply to message #394003] Wed, 25 March 2009 21:49 Go to previous messageGo to next message
summoner
Messages: 44
Registered: March 2009
Member
We use the basic one

May I ask the mechanism of the refresh group?
If the system want to refresh D and then find that D is made from A,B,C, is that the system will turn to refresh A,B,C first?
Re: Setup a refresh group for nested materialized views [message #394248 is a reply to message #394161] Thu, 26 March 2009 05:23 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member

>>May I ask the mechanism of the refresh group?

I can't understand. Are you asking me Or you have one refresh group from OS level?

>>If the system want to refresh D and then find that D is made from A,B,C, is that the system will turn to refresh A,B,C first?

If you have refresh group script. Please post here.

>>We use the basic one

Ok. It's not A problem.

Babu
Re: Setup a refresh group for nested materialized views [message #394277 is a reply to message #394248] Thu, 26 March 2009 06:33 Go to previous messageGo to next message
summoner
Messages: 44
Registered: March 2009
Member
gentlebabu wrote on Thu, 26 March 2009 05:23
>>May I ask the mechanism of the refresh group?

I can't understand. Are you asking me Or you have one refresh group from OS level?

>>If the system want to refresh D and then find that D is made from A,B,C, is that the system will turn to refresh A,B,C first?

If you have refresh group script. Please post here.

>>We use the basic one

Ok. It's not A problem.

Babu


I mean if I have not tell Oracle the refresh order, how does Oracle know about it?
I create the refresh group through enterprise manager. Simply click by click and then assigns materialized views to the group. Not sure how to get the script

Thank you your help again
Re: Setup a refresh group for nested materialized views [message #394310 is a reply to message #394277] Thu, 26 March 2009 07:52 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member

ASAIK, In Advanced Replication; Oracle Dos't know abour refresh order; that's why I suggests Deferrable constraints (Once Refresh done. Then only commit all data)

In Basic Replication; there is NO Random refresh method. It's by Order only.

Babu
Re: Setup a refresh group for nested materialized views [message #394441 is a reply to message #394310] Fri, 27 March 2009 02:22 Go to previous messageGo to next message
summoner
Messages: 44
Registered: March 2009
Member
gentlebabu wrote on Thu, 26 March 2009 07:52
ASAIK, In Advanced Replication; Oracle Dos't know abour refresh order; that's why I suggests Deferrable constraints (Once Refresh done. Then only commit all data)

In Basic Replication; there is NO Random refresh method. It's by Order only.

Babu


How do we know the order for the refresh, and how do we modify the order?

I have removed some materialized views from a refresh group and then add them back. However, the position shown in enterprise manager is same as before

[Updated on: Fri, 27 March 2009 05:39]

Report message to a moderator

Re: Setup a refresh group for nested materialized views [message #394555 is a reply to message #394441] Fri, 27 March 2009 14:52 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member


I'm asking your refresh group script.

Okay. Post the below table details.
1/ DBA_REPGROUP

2/ DBA_REFRESH_CHILDREN
Re: Setup a refresh group for nested materialized views [message #394728 is a reply to message #394555] Sun, 29 March 2009 22:53 Go to previous messageGo to next message
summoner
Messages: 44
Registered: March 2009
Member
gentlebabu wrote on Fri, 27 March 2009 14:52

I'm asking your refresh group script.

Okay. Post the below table details.
1/ DBA_REPGROUP

2/ DBA_REFRESH_CHILDREN




DBA_REPGROUP returns nothing, i.e., no rows

The DBA_REFRESH_CHILDREN show something like below:

owner name type rowner rname refgroup implicit_destroy push_deferred_rpc refresh_after_errors rollback_seg job next_date interval broken purge_option parallelism heap_size
SC, TABLE_A, SNAPSHOT, SC, MVGRP_INCR, 41, N, N, N, 31/3/2009 04:00:27, /*1:Days*/ sysdate + 1, N, , , ,
SC, TABLE_C, SNAPSHOT, SC, MVGRP_INCR, 41, N, N, N, 31/3/2009 04:00:27, /*1:Days*/ sysdate + 1, N, , , ,
SC, TABLE_D, SNAPSHOT, SC, MVGRP_INCR, 41, N, N, N, 31/3/2009 04:00:27, /*1:Days*/ sysdate + 1, N, , , ,
SC, TABLE_B, SNAPSHOT, SC, MVGRP_INCR, 41, N, N, N, 31/3/2009 04:00:27, /*1:Days*/ sysdate + 1, N, , , ,



If the system refresh the view one by one, then I think I should remove the row for TABLE_D and then add it back. Is it correct?

[Updated on: Sun, 29 March 2009 22:55]

Report message to a moderator

Re: Setup a refresh group for nested materialized views [message #394760 is a reply to message #394728] Mon, 30 March 2009 04:00 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/

Quote:

owner name type rowner rname refgroup implicit_destroy push_deferred_rpc refresh_after_errors rollback_seg job next_date interval broken purge_option parallelism heap_size
SC, TABLE_A, SNAPSHOT, SC, MVGRP_INCR, 41, N, N, N, 31/3/2009 04:00:27, /*1:Days*/ sysdate + 1, N, , , ,
SC, TABLE_C, SNAPSHOT, SC, MVGRP_INCR, 41, N, N, N, 31/3/2009 04:00:27, /*1:Days*/ sysdate + 1, N, , , ,
SC, TABLE_D, SNAPSHOT, SC, MVGRP_INCR, 41, N, N, N, 31/3/2009 04:00:27, /*1:Days*/ sysdate + 1, N, , , ,
SC, TABLE_B, SNAPSHOT, SC, MVGRP_INCR, 41, N, N, N, 31/3/2009 04:00:27, /*1:Days*/ sysdate + 1, N, , , ,


Use Code tag for posting SQL output.

According to DBA_REFRESH_CHILDREN report only your materialized view refresh run (I mean this is the order of your materialized view refresh)

>>If the system refresh the view one by one, then I think I should remove the row for TABLE_D and then add it back. Is it correct?

I can't understand.

Babu
Re: Setup a refresh group for nested materialized views [message #394774 is a reply to message #394760] Mon, 30 March 2009 04:57 Go to previous messageGo to next message
summoner
Messages: 44
Registered: March 2009
Member
Sorry that I have not replied in correct format.

gentlebabu wrote on Mon, 30 March 2009 04:00

According to DBA_REFRESH_CHILDREN report only your materialized view refresh run (I mean this is the order of your materialized view refresh)

>>If the system refresh the view one by one, then I think I should remove the row for TABLE_D and then add it back. Is it correct?

I can't understand.

Babu


The output shows that the record TABLE_D is before the record TABLE_B. Therefore, I guest TABLE_D will be refreshed before TABLE_B because the system refresh the views one by one.

If that's the case, I remove the record TABLE_D and then insert it back. The system should insert the record to the bottom of the table. Then the refresh tables are placed in correct order.

[Updated on: Mon, 30 March 2009 04:58]

Report message to a moderator

Re: Setup a refresh group for nested materialized views [message #394782 is a reply to message #394774] Mon, 30 March 2009 05:58 Go to previous message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member

>>If that's the case, I remove the record TABLE_D and then insert it back. The system should insert the record to the bottom of the table. Then the refresh tables are placed in correct order.

Yes. It;s correct.

Babu
Previous Topic: materialized view question.
Next Topic: Sub-query in Materialized Views
Goto Forum:
  


Current Time: Thu Mar 28 12:22:19 CDT 2024