we have this configuration on source
CREATE TABLE "NEW_TEST1"
( "ID" NUMBER,
"NAME" VARCHAR2(10),
"CITY" VARCHAR2(10),
"EMAIL" VARCHAR2(10)
);
created some UK indexes
create unique index ind1 on NEW_TEST1(id);
create unique index aind on NEW_TEST1(id,name);
create unique index ind2 on NEW_TEST1(email);
create unique index aaa on NEW_TEST1(name,email);
SQL> select INDEX_NAME,TABLE_NAME,COLUMN_NAME,COLUMN_POSITION from user_ind_columns where TABLE_NAME='NEW_TEST1' order by INDEX_NAME;
INDEX_NAME TABLE_NAME COLUMN_NAME COLUMN_POSITION
------------------------------ ------------------------------ -------------------- ---------------
AAA NEW_TEST1 EMAIL 2
AAA NEW_TEST1 NAME 1
AIND NEW_TEST1 ID 1
AIND NEW_TEST1 NAME 2
IND1 NEW_TEST1 ID 1
IND2 NEW_TEST1 EMAIL 1
6 rows selected.
Add supplemental logging. we see that the aaa index got used in UK as per alphanumeric order. for more info visit this article
https://dbatracker.com/2020/06/12/selection-of-primary-unique-key-by-ogg/
GGSCI (oel6-node03.localdomain) 13> add trandata oggsrc.new_test1
Logging of supplemental redo data enabled for table OGGSRC.NEW_TEST1.
TRANDATA for scheduling columns has been added on table 'OGGSRC.NEW_TEST1'.
GGSCI (oel6-node03.localdomain) 14> info trandata oggsrc.new_test1
Logging of supplemental redo log data is enabled for table OGGSRC.NEW_TEST1.
Columns supplementally logged for table OGGSRC.NEW_TEST1: EMAIL, NAME.
this means every update will have email and name columns in where caluse
lets see how
any update on table on source
SQL> select * from new_test1;
ID NAME CITY EMAIL
---------- ---------- ---------- ----------
1 aaa hkh abc
2 bbb khg def
3 ccc jdj ghi
4 ddd jtj jkl
SQL> update new_test1 set city='uuu' where id=4;
1 row updated.
SQL> commit;
Commit complete.
on target it is converted to. this is from replicat log file (using SHOWSYNTAX)
UPDATE "OGGTRG"."NEW_TEST1" x SET x."ID" = '4',x."CITY" = 'uuu' WHERE x."NAME"='ddd' AND x."EMAIL"='jkl'
now if you want any other UK column to be used in replication then you can override this using KEYCOLS
modify replicat parameter file and add this
map oggsrc.new_test1, target oggtrg.new_test1, KEYCOLS (id);
GGSCI (oel6-node04.localdomain) 11> view params rep_12
Replicat rep_12
SETENV(ORACLE_HOME="/u01/app/oracle/product/11.2.0/db_1")
SETENV(ORACLE_SID = "tgtdb")
AssumeTargetDefs
DiscardFile ./dirrpt/rpdw.dsc, Purge
UserID oggadm, Password Welcome1
showsyntax
DDL INCLUDE ALL
DDLERROR DEFAULT IGNORE
map oggsrc.new_test1, target oggtrg.new_test1, KEYCOLS (id);
map oggsrc.*, target oggtrg.*;
now if you update on source
now update staement like this
SQL> update new_test1 set city='eee' where id=4;
1 row updated.
SQL> commit;
Commit complete.
it will be converted to below on target
UPDATE "OGGTRG"."NEW_TEST1" x SET x."NAME" = 'ddd',x."CITY" = 'eee',x."EMAIL" = 'jkl' WHERE x."ID"='4'
Statement length: 102
lets verify same thing with schematrandata
we have disabled table level supplemental logging
GGSCI (oel6-node03.localdomain) 62> info trandata oggsrc.*
Logging of supplemental redo log data is disabled for table OGGSRC.CITY.
Logging of supplemental redo log data is disabled for table OGGSRC.COMPOSITE.
Logging of supplemental redo log data is disabled for table OGGSRC.ERTY.
Logging of supplemental redo log data is disabled for table OGGSRC.NEW.
Logging of supplemental redo log data is disabled for table OGGSRC.NEW_TEST1.
Logging of supplemental redo log data is disabled for table OGGSRC.PING.
Logging of supplemental redo log data is disabled for table OGGSRC.RAVE.
Logging of supplemental redo log data is disabled for table OGGSRC.TARA.
Logging of supplemental redo log data is disabled for table OGGSRC.TEST.
Logging of supplemental redo log data is disabled for table OGGSRC.ZORA.
enabled schema level supplemnetal logging
GGSCI (oel6-node03.localdomain) 61> add schematrandata oggsrc
2020-06-12 17:12:37 INFO OGG-01788 SCHEMATRANDATA has been added on schema oggsrc.
2020-06-12 17:12:37 INFO OGG-01976 SCHEMATRANDATA for scheduling columns has been added on schema oggsrc.
GGSCI (oel6-node03.localdomain) 64> info schematrandata oggsrc
2020-06-12 17:13:30 INFO OGG-01785 Schema level supplemental logging is enabled on schema OGGSRC.
2020-06-12 17:13:30 INFO OGG-01980 Schema level supplemental logging is enabled on schema OGGSRC for all scheduling columns.
source table update statement
SQL> select * from new_test1;
ID NAME CITY EMAIL
---------- ---------- ---------- ----------
1 aaa hkh abc
2 bbb khg def
3 ccc jdj ghi
4 ddd eee jkl
SQL> update new_test1 set city='qqq' where id=4;
1 row updated.
SQL> commit;
Commit complete.
on target it is converted to below
UPDATE "OGGTRG"."NEW_TEST1" x SET x."NAME" = 'ddd',x."CITY" = 'qqq',x."EMAIL" = 'jkl' WHERE x."ID"='4'
Statement length: 102
so KEYCOLS works same with schematrandata as well