Configure vertically partitioned replication
Use vertically partitioned replication to replicate a subset of the columns of a table. For example, you can replicate data changes made to C1, C2, C3, and C4, but not changes made to C5 and C6, as shown in the diagram.
Supported source and targets
Oracle to all targets
Guidelines for using vertically partitioned replication
Follow these guidelines when creating a configuration file that includes vertically partitioned replication.
-
Vertically partitioned replication is appropriate for reporting and other data sharing strategies, but it is not appropriate for high availability environments. Once you configure a table for vertically partitioned replication, SharePlex does not recognize the other columns, so data in those columns is not replicated.
-
You can combine horizontally partitioned and vertically partitioned replication for maximum control over which information is distributed, and to where.
For example:
A company has a headquarters and regional divisions. The headquarters maintains the corporate database, and each region maintains a regional database. The headquarters uses vertically partitioned replication to share some of the column data of a table to those locations, while retaining other sensitive data at headquarters. Row changes made to the shared columns are further partitioned horizontally, for replication to the appropriate regional database. - A table cannot be configured to replicate some columns to one target system and all columns to another (combination of vertically partitioned replication and full-table replication). You can, however, configure full-table replication to an identical table on one target, and then configure vertically partitioned replication from that target to a second target that contains the table that requires only the partition columns.
- A target table can, but does not have to, contain all of the same columns as its source table. The target can contain just the columns being replicated from the source table. The names of corresponding source and target columns do not need to be the same. Corresponding columns must contain the same data types (same type, size, precision).
To configure vertically partitioned replication, you specify either a column partition or an exclusion column partition in the configuration file:
- A column partition specifies the columns that you want to include in replication. Only data changes that are made to the specified columns get sent to the target.
- An exclusion column partition specifies columns to be excluded from replication. No data from those columns is replicated to the target.
Follow these rules to specify either type of column partition:
- There can be one partition per source table. A column partition and an exclusion partition are mutually exclusive.
- A column list must be enclosed within parentheses.
- Separate each column name with a comma. A space after the comma is optional.
- The maximum length of a partition is 174820 bytes (the maximum line length allowed in a configuration file). Therefore, the actual number of columns that you can list depends on the length of each name.
- The columns can be contiguous or non-contiguous in the source table. For example, you can replicate the first, third and seventh columns of a table.
- Key columns are not required to be included in the partition.
- If using horizontally partitioned and vertically partitioned replication together for this table, all of the columns in the partition scheme must be part of the column condition.
- Use one configuration file for all of the data that you want to replicate from a given datasource, including tables that will have full-table replication and those that will use partitioned replication.
To configure entries for vertically partitioned replication, use the following syntax. For more information about how to create a configuration file, see Configure data replication.
| datasource_specification | ||
|
# table specification with column partition | ||
|
src_owner.table (src_col,src_col,...) |
tgt_owner.table [(tgt_col,tgt_col,...)] | routing_map |
|
# table specification with exclusion column partition | ||
| src_owner.table !(src_col,src_col,...) | tgt_owner.table | routing_map |
| Configuration component | Description |
|---|---|
| src_owner.table and tgt_owner.table | The specifications for the source and target tables, respectively. |
|
(src_col, src_col,...) |
Specifies a column partition that lists the columns to include in replication. No other column data is replicated, including data in columns that are added after the start of replication (assuming DDL replication is enabled). |
|
!(src_col,src_col,...) |
Specifies an exclusion column partition that lists the columns to exclude from replication. All other column data is replicated, including data in columns that are added after the start of replication (assuming DDL replication is enabled). Note: When using an exclusion column partition, the corresponding source and target column names must be identical, and the excluded columns cannot be used in a key definition. For more information, see Define a unique key. |
| (tgt_col,tgt_col,...) |
The target columns. Use this option to map source columns to target columns that have different owners or names. If the source and target columns have identical owners or names, the target columns can be omitted. To map source columns to target columns, follow these rules:
|
| routing_map |
The routing map for the column partition. The routing map can be one of the following:
|
Configuration examples
The following is a vertically partitioned replication configuration replicating to multiple targets by using a compound routing map. To use a compound routing map for this source table, all targets must be named scott.sal.
| Datasourceo.oraA | ||
| scott.emp (c1,c2) | scott.sal |
sysB@o.oraB+sysC@o.oraC |
The following is a vertically partitioned replication configuration replicating to a single target where the target columns have different names from those of the source.
| Datasourceo.oraA | ||
| scott.emp (c1,c2) | scott.sal (c5,c6) |
sysB@o.oraB |
The following configuration file is not valid because it repeats the same column partition of scott.emp (c1, c2) twice in the configuration file.
| Datasourceo.oraA | ||
| scott.emp (c1,c2) | scott.cust (c1,c2) |
sysB@o.oraB |
| scott.emp (c1,c2) | scott.sales (c1,c2) | sysC@o.oraC |