If a table has primary key then it is selected by deafult for replication. In absense of PK, a UK is selected based on alphanumeric order. If both are not present then all columns are used to form a unique identifier to help replicat locate the row.
lets understand this with an example
create a table
CREATE TABLE "NEW_TEST1"
( "ID" NUMBER,
"NAME" VARCHAR2(10),
"CITY" VARCHAR2(10),
"EMAIL" VARCHAR2(10)
);create 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);
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
------------------------------ ------------------------------ -------------------- ---------------
AIND NEW_TEST1 ID 1
AIND NEW_TEST1 NAME 2
IND1 NEW_TEST1 ID 1
IND2 NEW_TEST1 EMAIL 1
insert some records
insert into NEW_TEST1 values (1,'aaa','hkh','abc');
insert into NEW_TEST1 values (2,'bbb','khg','def');
insert into NEW_TEST1 values (3,'ccc','jdj','ghi');
insert into NEW_TEST1 values (4,'ddd','jtj','jkl');
commit;as you see we do not have a PK as of now but we do have UK on the table.
lets add supplemental logging for this table
GGSCI (oel6-node03.localdomain) 8> info trandata oggsrc.new_test1
Logging of supplemental redo log data is disabled for table OGGSRC.NEW_TEST1.
GGSCI (oel6-node03.localdomain) 9> 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'.lets see which key is picked by OGG for replication
GGSCI (oel6-node03.localdomain) 10> 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: ID, NAME.
you can see that this one belongs to index “AIND”
lets delete supplemental logging for this table and add one more index named “AAA”. This is alpanumerically preeceeds “AIND”
GGSCI (oel6-node03.localdomain) 11> delete trandata oggsrc.new_test1
Logging of supplemental redo log data disabled for table OGGSRC.NEW_TEST1.
TRANDATA for scheduling columns has been disabled on table 'OGGSRC.NEW_TEST1'.
GGSCI (oel6-node03.localdomain) 12> info trandata oggsrc.new_test1
Logging of supplemental redo log data is disabled for table OGGSRC.NEW_TEST1.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.lets add supplemental logging
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.
we can see that now the key used belongs to “AAA” index
lets see what happens if PK is present
GGSCI (oel6-node03.localdomain) 15> delete trandata oggsrc.new_test1
Logging of supplemental redo log data disabled for table OGGSRC.NEW_TEST1.
TRANDATA for scheduling columns has been disabled on table 'OGGSRC.NEW_TEST1'.
GGSCI (oel6-node03.localdomain) 16> info trandata oggsrc.new_test1
Logging of supplemental redo log data is disabled for table OGGSRC.NEW_TEST1.
add PK constraint
ALTER TABLE new_test1 ADD CONSTRAINT ppp PRIMARY KEY (id,name,city);
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 NAME 1
AAA NEW_TEST1 EMAIL 2
AIND NEW_TEST1 NAME 2
AIND NEW_TEST1 ID 1
IND1 NEW_TEST1 ID 1
IND2 NEW_TEST1 EMAIL 1
PPP NEW_TEST1 CITY 3
PPP NEW_TEST1 NAME 2
PPP NEW_TEST1 ID 1you can see that if PK is present then it will be used irrespective of any other UK.
GGSCI (oel6-node03.localdomain) 17> 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) 18> 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: ID, NAME, CITY.adding additional column using COLS
now lets remove this PK and have only UK index
SQL> alter table new_test1 drop constraint ppp;
Table altered.
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.if now we add supplemental logging it will use “AAA” index whoch has email and name column.
lets suppose we want to add one additional column city for supplemental logging
GGSCI (oel6-node03.localdomain) 47> add trandata oggsrc.new_test1 cols(city)
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) 48> 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, CITY.
now you can see city is also added for supplemental logging apart from email and name
DBATracker 