Rich,
You must be able to uniquely identify a row to be able to logically replicate
it, whether that be via a primary key, unique key, or a combination of columns
which can be guaranteed to be unique (up to 38 columns in the case of
Goldengate).
If a table has no identifiable PK/UK, I usually recommend that a default
surrogate key is added using a sequence and a default (in 12, I like the
DEFAULT sequence-name.NEXTVAL syntax ). Do not add an identity column - they
are not support for them in replication scenarios at present.
If you are doing updates by rowid, you need to set the appropriate level of
supplemental logging to ensure that the relevant data (pk/uk/all columns) is
captured in the redo regardless of the modification statement to ensure the row
on the destination can be identified and manipulated. This will increase the
amount of redo written and therefore have an impact on redo performance. PK
supplemental logging will add minimal overhead, supplementally logging all
columns may be devastating depending upon the size of each row.
regards
Neil Chandler
________________________________
On 2018/03/27 11:21, vit.spinka wrote:
Note that all logical replication products see the data as they are in the redo
- they don't see the original SQL. Thus if the table has a primary key, they
will use it to apply the changes - and it does not matter at all whether the
user sql was using rowid or whatever else.
Right, but a primary key is required in this case, correct? Not sure how else
this would be accomplished...
Thanks!
Rich
