We
should be extra cautious when using ‘HANDLECOLLISIONS’. Without
HANDLECOLLISIONS, replicat abends if row updated at source is missing at target
database.
To fix
this issue if you plan to use HANDLECOLLISION then please make a note of how
HANDLECOLLISION behaves in different scenarios -
With
HANDLECOLLISION -
- If updates are performed on non-key columns
on a row @source that is missing @target
then, this change record is simply discarded
by replicat @target.
-The missing record at target is inserted
only if the update is performed on key
columns. Another point to note here is that, if supplemental logging is enabled
on primary key then the missing row at target is inserted only with values of
primary key columns, remaining columns are kept NULL. If supplemental logging
is enabled for ALL columns then only the row is inserted in target DB with
values of all columns.
Below is the test I performed –
OBJECT
ID is the primary key in TEST table.
SOURCE
|
TARGET
|
QA_SQL>
alter table test add supplemental log data (primary key) columns;
Table
altered.
|
|
QA_SQL>
select object_id, object_name from test;
OBJECT_ID
OBJECT_NAME
----------
--------------------
35525 TEST
35526 TEST_PK
|
PT_SQL>
select object_id, object_name from test;
OBJECT_ID
OBJECT_NAME
----------
--------------------
35525 TEST
ROW with object_id 35526 is missing @target.
|
QA_SQL>
update test set OBJECT_NAME='TEST_PK_2' where object_id=35526;
1
row updated.
QA_SQL>
commit;
Commit
complete.
|
PT_SQL> select object_id, object_name from test;
-
OBJECT_ID OBJECT_NAME
---------- --------------------
35525 TEST
ROW @ TARGET IS DISCARDED BECAUSE UPDATE WAS NOT
PERFORMED ON KEY COLUMN.
|
QA_SQL>
update test set OBJECT_ID=35527 where object_id=35526;
1
row updated.
QA_SQL>
commit;
Commit
complete.
|
PT_SQL> select object_id, object_name from
test;
OBJECT_ID OBJECT_NAME
---------- --------------------
35527
35525 TEST
PT_SQL> /
OBJECT_ID OBJECT_NAME
---------- --------------------
35527
35525 TEST
@TARGET THE ROW IS INSERTED ONLY WITH VALUES OF KEY
COLUMNS BECAUSE UPDATE WAS PERFORMED ON KEY COLUMN
|
QA_SQL>
alter table test add supplemental log data (all) columns;
Table
altered.
QA_SQL>
update test set OBJECT_ID=35527 where object_id=35527;
1
row updated.
QA_SQL>
commit;
Commit
complete.
|
PT_SQL> select object_id, object_name from
test;
OBJECT_ID OBJECT_NAME
---------- --------------------
35527 TEST_PK_2
35525 TEST
@TARGET THE ROW IS INSERTED WITH VALUES OF ALL
COLUMNS BECAUSE SUPPLEMENTAL LOGGING WAS ENABLED FOR ALL COLUMNS.
|
