Author's name: Vadim Grepan
Date written: 04-Sep-2001
Oracle version(s): 8i (8.1.*)
How can I replicate only some columns (rows) from given table?
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.
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
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
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.