How can I replicate only some columns (rows) from given table ?

The Oracle (tm) Users' Co-Operative FAQ

How can I replicate only some columns (rows) from given table ?

Back to index of questions

The advanced master (multimaster) replication operates on whole tables so you cannot replicate only some chosen columns. Also you cannot replicate only specific rows (e.g. depending on the value of record's field).

On the other hand you can use snapshots (one-way replication frankly speaking) to create a subset of replicated columns.

Sequences are not replicated in any case. Documentation offers some techniques to avoid this limitation.

9.2 Update from Surya Rao

Using multi-master replication (a setup where there are multiple masters all replicating data to each other) it is not possible to replicate selective columns from a table (or even rows for that matter). However this can be easily achieved using Materialized views (used to be called snapshots prior to Oracle 9i). With materialized views one can define a materialized view in a materialized view site with either a column subset (selective columns) or row subset (using a 'where' clause in the view) or both. Two-way replication is possible with materialized views i.e data can replicate both ways between a master site and its materialized view site.

Row subsetting is accomplished by using a where clause in the materialized view definition. It is also called as horizontal subsetting. Column subsetting on read-only materialized views can be done by excluding the unwanted columns. For Column Subsetting on updateable materialized views, deployment templates must be used to create and distribute the materialized views. When defining an updateable materialized view, you cannot exclude the primary key columns of the master table at the master site.

In fact we can take this concept of column and row subsetting a step further by implementing Multi-tier Materialized views. Say, there is a SALES table at the master site at the headquarters of a corporation in New York. We can now have materialized views in London, New Delhi, Melbourne etc each replicating the SALES table with selective columns and rows relevant to their country offices. We can further have second level materialized views at various branch offices in each country which are based on their country materialized view. For instance, I can have second level materialized views in Sydney, Perth and Cairns which are based on the first level materialized view in Melbourne and further subset the data that is replicated to these brances by columns and or rows.

In a scenario where we have a combination of master-master and master-materialized view replication, updates are propagated as follows. Updates from the master site are pulled by the materialized view at regular intervals when it refreshes from its master. Updates from the materialized view site to its master site are done by executing deferred transactions from the materialized view site on its master view site. A materialized view site can only updates its master site. But its master site can then further propagate these changes (sent from its materialized view site) to other master sites. These master-master updates are for the whole table though.

Further reading: Oracle8i Replication, Oracle 9i replication.

Back to top

Back to index of questions