Ensuring Row Uniqueness in Source and Target Table
Unless a KEYCOLS
clause is used in the
TABLE
or MAP
statement, Oracle GoldenGate
selects a row identifier to use in the following order of priority, depending on the
number and type of constraints that were logged (see Transaction Log Settings and Requirements).
-
Primary key if it does not contain any extended (32K)
VARCHAR2/NVARCHAR2
columns. Primary key without invisible columns. -
Unique key: Unique key without invisible columns.
In the case of a non-integrated Replicat, the selection of the unique key is as follows:
-
First unique key alphanumerically with no virtual columns, no UDTs, no function-based columns, no nullable columns, and no extended (32K)
VARCHAR2/NVARCHAR2
columns. To support a key that contains columns that are part of an invisible index, you must use theALLOWINVISIBLEINDEXKEYS
parameter in the Oracle GoldenGateGLOBALS
file. -
First unique key alphanumerically with no virtual columns, no UDTs, no extended (32K)
VARCHAR2/NVARCHAR2
columns, or no function-based columns, but can include nullable columns. To support a key that contains columns that are part of an invisible index, you must use theALLOWINVISIBLEINDEXKEYS
parameter in the Oracle GoldenGateGLOBALS
file.
-
-
Not Nullable Unique keys: At least one column from one of the unique keys must be not nullable. This is because
NOALLOWNULLABLEKEYS
is the default.Note:
ALLOWNULLABLEKEYS
is not valid for integrated Replicat. -
If none of the preceding key types exist (even though there might be other types of keys defined on the table) Oracle GoldenGate constructs a pseudo key of all columns that the database allows to be used in a unique key, excluding virtual columns, UDTs, function-based columns, extended (32K)
VARCHAR2/NVARCHAR2
columns, and any columns that are explicitly excluded from the Oracle GoldenGate configuration by an Oracle GoldenGate user.Unless otherwise excluded due to the preceding restrictions, invisible columns are allowed in the pseudo key.
Note:
If there are other, non-usable keys on a table or if there are no
keys at all on the table, Oracle GoldenGate logs an appropriate message to the
report file. Constructing a key from all of the columns impedes the performance
of Oracle GoldenGate on the source system. On the target, this key causes
Replicat to use a larger, less efficient WHERE
clause.
If a table does not have an appropriate key, or if you prefer the
existing key(s) not to be used, you can define a substitute key if the table has
columns that always contain unique values. You define this substitute key by
including a KEYCOLS
clause within the Extract
TABLE
parameter and the Replicat MAP
parameter. The specified key will override any existing primary or unique key that
Oracle GoldenGate finds. For more information, see Parameters and Functions Reference
for Oracle GoldenGate.
-
KEYCOLS parameter isn’t specified.
-
USEALLKEYCOLUMNS parameter isn’t specified.
-
ALLOWNULLABLEKEYS parameter isn’t specified.
-
Source and target key columns don’t match.
-
Unique index matches to source table key column exists.