Re: Mirroring a table to remote instances

stephen O'D wrote:
> Say I have a lookup table in a database that looks like
>
> ID (PK)
> datafield1
> datafield2
> ...
> datafieldx
>
> It will only ever have inserts and updates applied to it, never
> deletes.
>
> There will probably be close to 30Million records in this table.
>
> I need to mirror changes to this table to mutliple sites through the
> day (probably every 15 minutes or so) without copying the whole table
> each time. What is the best way of doing this?
>
> One option I considered was to add a flag (replicated N or NULL) to the
> table for each 'replication site' and when a row is inserted or updated
> change the flag from null to 'N'. Then use the merge statement to
> merge only those records that been added/changed. What I don't like
> about this solution is
>
> 1. You need a column per replication site
> 2. I don't think there is anyway for me to merge and update the flag to
> null while having a consistent view of the data - ie merge where flag =
> N would see different data than update set Flag = NULL where flag = N
> meaning I would have to do something like:-
>
> begin
> for rec in (select * from table where flag = N)
> update/insert remote
> update local
> end loop;
> end;
>
> I know I could set the transaction to serializable, but I fear the
> volume of updates on this table could see a lot of failed transactions
> with the 'cannot serialize access for this transaction error'.
>
> Another idea is to use AQ to broadcast the updates/inserts to each site
> via a multi subscriber queue.
>
> I am pretty sure this is a common task - has anyone got any better
> ideas/advice? Allowing the remote instance to query the master table
> each time they need it in a query is not really an option due to the
> number of selects they will be issuing against it!
>
> Thanks,
>
> Stephen.

And your Oracle version is?

Streams (AQ) would be my first choice.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)