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  |
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 #394003 is a reply to message #393955] |
Wed, 25 March 2009 05:37  |
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 #394277 is a reply to message #394248] |
Thu, 26 March 2009 06:33 |
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 #394441 is a reply to message #394310] |
Fri, 27 March 2009 02:22 |
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 #394728 is a reply to message #394555] |
Sun, 29 March 2009 22:53 |
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 |
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 |
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 |
|
|
|
|
|
Goto Forum:
Current Time: Thu Nov 21 11:19:31 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
