In Shareplex replication it is desirable to have a primary or unique key defined on source and target table. If there is no unique or primary key defined on replicating table (for example, in a packaged application), then the posting can be slow for such tables, especially during UPDATE or DELETE. In such situations it may be desired to use a key (one’s own) other than primary or unique key, Shareplex permits such configuration. This solution delves on the way to configure the replication to use such custom key.
General information.
Defining one’s own key in Shareplex replication is achieved by configuring the entry for the table in Shareplex config file in the following manner:
To create a key definition for the example table JANE.SALES, create the entry for the table in question by typing a space after the source object and using the following syntax, including the parentheses. Spaces between column names within the parentheses are optional. The !key component must precede the column name(s) as below:
!key (col_name1, col_name2)
Example:
!key (emp_no,emp_name)
The config file entry for the table with custom keys from the example above would look like:
jane.sales !key (emp_no,emp_name) jane.sales sysB@o.oraB
The general syntax for key definition is:
< owner>.<source_table_name> !key (col_name1, col_name2, ..) < owner>.<target_table_name> <target_host>@o.<target_sid>
ADDITIONAL INFORMATION:
The following excerpts are taken from is taken from Shareplex Administrator Guide Chapter titled “Planning Shareplex Replication” section titled “Shareplex keys” , and Chapter titled “Preparing for SharePlex Replication” section titled “Components of a SharePlex configuration”. They delve on virtues of using keys in replication and also on the use of key definition in the absence of such keys:
SharePlex keys
Understanding the role of keys in the SQL statement constructed by SharePlex is critical
to helping you understand and maintain data synchronization. SharePlex uses the key in
its SQL statements to locate the correct row in the target table for UPDATEs and
DELETEs, and to ensure that a row being inserted is not already there. Row IDs cannot
be used to locate rows on the target system because row IDs are different on the source
and target systems, and the target row ID is not in the redo log.
SharePlex works fastest when there is a primary key or a unique key on all tables being
replicated, especially large tables and tables containing LONG columns. When choosing
a key to use, SharePlex uses the best available key column in the following order of
priority.
• Primary key
• Unique key with the fewest columns, where at least one of the columns is NOT
NULL
• Unique key with the fewest columns. If a key permits NULLs, SharePlex cannot
ensure the row’s uniqueness for UPDATEs and DELETES, so there is a possibility it
could change the wrong row on the target system. To control how SharePlex
addresses keys that permit NULLs, set the SP_SYS_IN_SYNC parameter. See
Chapter 2 of the SharePlex Reference Guide for more information about this parameter.
• If SharePlex cannot determine a key, it constructs a key using all of the columns
except LONGs and LOBs. This key is maintained internally and is not created on the
table itself. This option is the least desirable option, because the resultant WHERE
clause causes Oracle to perform a full table scan on the target table to find the row,
and that significantly slows replication. Moreover, the use of a simulated key does
not always enforce uniqueness. For more information, see “Long columns” on
page nnn.
• User-defined key. You need to create a non-unique index on the target system and
add the index to the SharePlex hints file, which allows the Post process to use the
index upon posting data to that table.
Effects on the source system: If a table does not have a unique key and SharePlex has
to build its simulated all-column key for UPDATEs, this procedure can be very timeconsuming
for tables with numerous columns.
Effects on the target system: Without the help of a primary or unique key on the target
table, Post (relying on its constructed all-column key) must compare the entire table row
by row. This causes transaction data to accumulate in the queues, increasing the latency
between source and target instances to unacceptable levels.
Recommendation: If adding a primary key is not possible (such as when packaged
applications are in use), you can create a user-defined key by creating a key definition.
To create a key definition, you use special syntax in the configuration file to list one or
more columns that ensure row uniqueness. SharePlex will use those columns as a
unique key in its WHERE clause to locate target rows for posting. This key overrides
any other key created for the table. See “Components of a SharePlex configuration” on
page nnn for more information about constructing a key definition.
An alternative to a key definition is to create or use a unique index based on one or more
columns that establish uniqueness. See “Indices” on page nnn for more information.
Keys in peer-to-peer replication:Primary keys are essential in peer-to-peer replication,
and they must be unique among all of the databases that will be involved in replication.
For more information about keys in peer-to-peer replication, see page nnn.
