materialized view replication [message #432649] |
Wed, 25 November 2009 08:27 |
nasir_mughal
Messages: 122 Registered: April 2007 Location: Karachi
|
Senior Member |
|
|
Hi
I have decided to use "Materialized View Replication" to replicate my database. Before going for it please help on this.
1) If table to be replicated has three foreign keys columns, do i need to create index on each foreign key column.?
2) Do i need to create index on such foreign key column which is part of composite primary key of table to be replicated.?
3) We have six branches (Materialized view Sites). Our requirement is that each branch should only see records related to them, So i will use "branch_id" of branch in where clause to create materialized views at each site.
Do i need to create separate master group for each materialized view site?
4) I am not finding Replication Management Tool in Oracle Enterprise Manager 10g. Where can i found it?
Kindly help me
[Updated on: Wed, 25 November 2009 09:43] Report message to a moderator
|
|
|
|
Re: materialized view replication [message #432770 is a reply to message #432692] |
Thu, 26 November 2009 03:47 |
nasir_mughal
Messages: 122 Registered: April 2007 Location: Karachi
|
Senior Member |
|
|
Thanks for reply Sir,
i will create updateable materialized views at all our branches. In other words all materialized view sites are updateable.
And i want to use "branch_id" so that each branch could only see records related to them and to improve query performance at materialized view site.
So Kindly help me do i need to create separate master group for each materialized view site at master site.?
Thanks.
[Updated on: Thu, 26 November 2009 03:52] Report message to a moderator
|
|
|
Re: materialized view replication [message #432844 is a reply to message #432770] |
Thu, 26 November 2009 11:30 |
babuknb
Messages: 1736 Registered: December 2005 Location: NJ
|
Senior Member |
|
|
>>i will create updateable materialized views at all our branches. In other words all materialized view sites are updateable.
Then you need go with Advanced Replication.
>>And i want to use "branch_id" so that each branch could only see records related to them and to improve query performance at materialized view site
Sorry to say; still not able to get you; Let me explain. In Replication Environment; You need to use CREATED_DATE,LAST_UPDATE_DATE, MODIFED_DATE Using this column only we can configure Conflict Resolution. As per your requirement NOT able to understand what exactly you're looking for.
>>So kindly help me do i need to create separate master group for each materialized view site at master site?
Yes. You need to create separate master group for each <<materialized view>> site.
- Babu
[Updated on: Thu, 26 November 2009 14:18] Report message to a moderator
|
|
|
Re: materialized view replication [message #432931 is a reply to message #432844] |
Fri, 27 November 2009 06:47 |
nasir_mughal
Messages: 122 Registered: April 2007 Location: Karachi
|
Senior Member |
|
|
Sorry i could not explain my problem properly.
Actualy in "branch_id" column of table i am storing branch_id of branch there is no point in using it to configure conflit resolution.
I am considering two options to implement advance replication
Option 1:
Use branch_id column in where clause when creating materialized views at materialized view sites (branches), so that branch persons could see records they inserted and are related to them.
like at our first branch (mview_site_01) with branch_id='01'.
CREATE MATERIALIZED VIEW rss.transactions FOR UPDATE AS
SELECT * FROM rss.transactions@rss10g
WHERE branch_id='01';
CREATE MATERIALIZED VIEW rss.stock_transactions FOR UPDATE AS
SELECT * FROM rss.stock_transactions@rss10g
WHERE branch_id='01';
similarly at second branch (mview_site_02) with branch_id='02'
CREATE MATERIALIZED VIEW rss.transactions FOR UPDATE AS
SELECT * FROM rss.transactions@rss10g
WHERE branch_id='02';
CREATE MATERIALIZED VIEW rss.stock_transactions FOR UPDATE AS
SELECT * FROM rss.stock_transactions@rss10g
WHERE branch_id='02';
Option 2:
Don't use branch_id column and create materialized views at all branches as
CREATE MATERIALIZED VIEW rss.transactions FOR UPDATE AS
SELECT * FROM rss.transactions@rss10g;
CREATE MATERIALIZED VIEW rss.stock_transactions FOR UPDATE AS
SELECT * FROM rss.stock_transactions@rss10g;
Now i ask
1) Do i need to create different master group for each materialized view site at master site for both options (1/2)
2) Which option comparatively require minimum administration work.
3) In your opinion which option should i go for?
4) In 10g Where can i found Replication Management tool (Graphical Interface Tool) Which is shown in this link.
http://download.oracle.com/docs/cd/B10501_01/server.920/a96567/reprepman.htm
Thanks in advance.
[Updated on: Fri, 27 November 2009 09:02] Report message to a moderator
|
|
|
|