Re: Oracle multi-master replicaiton: index generation necessary?

Hi Nuno,

Just a little snippet from the Oracle documentation:

When replicating tables with foreign key referential constraints, Oracle Corporation recommends that you always index foreign key columns and replicate these indexes, unless no updates and deletes are allowed in the parent table. *Indexes are not replicated automatically*.

Note it then discusses adding *indexes* objects to a Master Group.

Also note the list of object types that can be replicated: FUNCTION, *INDEX* ,INDEXTYPE,OPERATOR ,PACKAGE ,PACKAGE BODY ,PROCEDURE ,SYNONYM ,TABLE ,TRIGGER ,TYPE ,TYPE BODY ,VIEW So your comment that indexes are not replicated is not strictly speaking correct.

Also note that it's not good practice as such to have PK on replicated tables. You *must* have a PK if a table is to be replicated, else you get an error trying to add the table to a replicated group.

Cheers

Richard

"Nuno Souto" <nsouto_at_optushome.com.au.nospam> wrote in message news:3d6a0eb0$0$28864$afc38c87_at_news.optusnet.com.au...
> In article <3tka9.15283$g9.48096_at_newsfeeds.bigpond.com>, you said (and I
> quote):
> >
> > I don't believe you *must* replicate indexes (although you have the
option
> > to specify which indexes you might wish to add to a replication group).
The
> > exception to this is the PK which all replicated tables must have else
> > Oracle will turn on you angrily.
>
> AFAIK, indexes are not replicated. What is replicated (sent back and
> forwards) is the contents of a row (columns) with an instruction that
> says "delete, insert or update this" depending on the original op and the
> row's PK. It's all done by triggers that fire off packaged procedures.
> The column values are passed around, not the data blocks.
>
> So, it doesn't matter what indexes you have anywhere, the
> INSERT/UPDATE/DELETE statement fired off by the local replication code
> will do whatever needs to be done to the local indexes just like any
> other statement. Transparently. Like you say, it's good practice to
> have a PK for these tables, but only because Oracle will chuck a wobblie
> if it can't find the row during an update or delete.
>
> HTH
> --
> Cheers
> Nuno Souto
> nsouto_at_optushome.com.au.nospam
Received on Mon Aug 26 2002 - 07:19:38 CDT