What key is used by Shareplex (4299629)

What key is used by Shareplex

In SharePlex replication it is good to know what key will be used by SharePlex when posting data to the target database.

General information. 

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 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.

• 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.

The "show config" provides the key used by tables that are part of the replication. This is a convenient way to derive such info. The output will not delve on how SharePlex arrives at such decision. Here is a sample output from a config file that only has one table in replication:

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 time consuming 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. An alternative to a key definition is to create or use a unique index based on one or more columns that establish uniqueness. 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.