Mirroring a table to remote instances

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. Received on Wed Dec 14 2005 - 11:25:25 CST