Home » Server Options » Replication » materialized view replication (10.2.0.1.0)
materialized view replication [message #432649] Wed, 25 November 2009 08:27 Go to next message
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 #432692 is a reply to message #432649] Wed, 25 November 2009 14:44 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member


Hello,

Quote:
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.?


NO

Quote:
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.


Looks your looking read only replication for some branches. If your going to use ready only replication why you need "branch_id". Explain on this.

>> I am not finding Replication Management Tool in Oracle Enterprise Manager 10g. Where can i found it?

What about OEM Console??

http://download.oracle.com/docs/cd/B19306_01/server.102/b14227/toc.htm

- Babu
Re: materialized view replication [message #432770 is a reply to message #432692] Thu, 26 November 2009 03:47 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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

Re: materialized view replication [message #432970 is a reply to message #432931] Fri, 27 November 2009 14:54 Go to previous message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member

Hello,

Thanks for your explanation. Now I understood.

1. You can go with Option 1. Create materialized view using BRANCH_ID for each site.

2. Yes you need to create each materialized view group in each site.

3. Good for selecting advanced replication. As per your requirement (Option 1) if each site only having read only access. Then no issue. If each site having UPDATABLE materialized view then you need go with Conflict Resolution.

If it's Updatable materialized view then select last modified date type in conflict resolution.

4. Yes You can use GUI using Oracle 10g (As per your subject your database version oracle 10g) then install oracle 10g client. Using GUI You can configure advanced replication with out any issues. (But your link redirecting to 9i. Why your posting 9i link here??)

If you have free time check this. Here you'll understand replication concept.

- Babu

Previous Topic: Replication from 10.2.0.4 to 9.2.0.6
Next Topic: A question about CREATE PUBLIC DATABASE LINK and ORA-12154 error
Goto Forum:
  


Current Time: Thu Mar 28 05:16:03 CDT 2024