When Oracle GoldenGate encounters tables without a primary key (PK) for data replication, it has different approaches depending on the situation:

1. Using Unique Indexes:

• If a unique index exists on the table, GoldenGate can use those columns to identify rows for updates and deletes. This is the preferred approach as it avoids full table scans on the target database.

2. KEYCOLS Parameter:

• If no unique index exists, GoldenGate allows you to define a logical key using the KEYCOLS parameter in the extract parameter file. This parameter specifies a comma-separated list of columns that uniquely identify a row.

TABLE TABLE1, KEYCOLS(COL1,COL2);
or
MAP TABLE1, TARGET TABLE1, KEYCOLS(COL1,COL2);

3. All Columns as Key:

• As a last resort, if neither PKs, unique indexes, nor KEYCOLS are available, GoldenGate utilizes all columns in the table to uniquely identify rows. This approach is less efficient as it involves full table scans on the target database for updates and deletes potentially raising performance issues.

Things to Consider:

• Using all columns as the key has performance implications on both source and target databases, increasing resource consumption and possible latency.
• Ensure duplicate rows don’t exist if using all columns as the key. Veridata is a good product to execute regular checks.
• Small tables with infrequent DML might still be acceptable with an all-column key.

Alex’s Recommendations:

• If possible, implement a unique index on the table for efficient replication.
• Define a logical key using KEYCOLS if a unique index is impractical.
• Only use all columns as the key as a last resort and understand the performance implications.

• GoldenGate