rowid as primary key

Robeen · Jul 24, 2021 10:36AM

Oracle DB 12.1.0.2

Hello Team,

there are a few tables on Oracle which do not have primary keys. On Oracle we can create a table with no primary key. I am using a CDC tool (Striim) to replicate data from these tables to Kudu. There is a feature called GGGTrail which will read changes from Golden Gate trail files and apply to target table. On Kudu, it is crucial to set the primary key for these tables.

I am planning to use the rowid as primary key on Kudu for these tables.. the CDC app will read metadata (ROWID) for each record and apply them on target. In case the rowid changes on Oracle (due to deletes, flashback etc..), CDC app will delete and insert record with new rowid

Do you think I can proceed with rowid as primary key on target?

Thanks,

Roshan

Frank Kulash · Jul 24, 2021 10:52AM

Hi, @Robeen

In case the rowid changes on Oracle (due to deletes, flashback etc..), CDC app will delete and insert record with new rowid

That's exactly why you need a primary key. Imagine you move a table to a different tablespace, so all rows will have new ROWIDs.

Create a real primary key.

Frank Kulash · Jul 24, 2021 10:52AM

Hi, @Robeen

In case the rowid changes on Oracle (due to deletes, flashback etc..), CDC app will delete and insert record with new rowid

That's exactly why you need a primary key. Imagine you move a table to a different tablespace, so all rows will have new ROWIDs.

Create a real primary key.

Robeen · Jul 24, 2021 10:56AM

If rowid cannot be used, is there a way how I can add a unique column (hidden)? I cannot alter the table since it might affect golden gate replication on source.

prov.00.txt

Paulzip · Jul 24, 2021 11:33AM

is there a way how I can add a unique column (hidden)?

Yes you can, but whether or not Kudu and Golden Gate can handle that is another matter.

-- Create demo table, no PK
create table T_TABLE_TEST as
  select dbms_random.value(0, 100) SOME_VAL
       , dbms_random.string('x', trunc(dbms_random.value(0, 30))) SOME_STR
  from dual
  connect by level <= 5
/

Table created.

select *
from   T_TABLE_TEST
/

  SOME_VAL|SOME_STR
----------|------------------------------
 6.8254594|6XO91A80Z3BVVZVHWG4PQ
16.7237219|AMCJKGVRKFS57UON3VX2R2
61.3746587|VPWQORO07P5KJ0MSO3
57.8522938|BOCSYW1E1XQFCUJY9H8JV
12.3681307|KXDKL25CNX25W

-- Add an identity column, which will be our PK
alter table T_TABLE_TEST add UNIQUE_ID integer
  generated by default on null as identity not null
/

Table altered.

-- Make it a PK
alter table T_TABLE_TEST add constraint PK_T_TABLE_TEST primary key (UNIQUE_ID)
/

Table altered.

select *
from   T_TABLE_TEST
/

  SOME_VAL|SOME_STR                      | UNIQUE_ID
----------|------------------------------|----------
 6.8254594|6XO91A80Z3BVVZVHWG4PQ         |         1
16.7237219|AMCJKGVRKFS57UON3VX2R2        |         2
61.3746587|VPWQORO07P5KJ0MSO3            |         3
57.8522938|BOCSYW1E1XQFCUJY9H8JV         |         4
12.3681307|KXDKL25CNX25W                 |         5

-- Make the column invisible
alter table T_TABLE_TEST modify UNIQUE_ID invisible
/

Table altered.


select *
from   T_TABLE_TEST
/


  SOME_VAL|SOME_STR
----------|------------------------------
 6.8254594|6XO91A80Z3BVVZVHWG4PQ
16.7237219|AMCJKGVRKFS57UON3VX2R2
61.3746587|VPWQORO07P5KJ0MSO3
57.8522938|BOCSYW1E1XQFCUJY9H8JV
12.3681307|KXDKL25CNX25W


exit

Robeen · Jul 24, 2021 12:10PM

Thanks for the update.

Unfortunately we cannot modify the source tables as application will stop running on production. Do you think I can carry on with the CDC replication with rowid as PK on Kudu? If rowids are changed on source Oracle, there will be delete and insert operation.

Robeen · Jul 24, 2021 2:34PM

I also noticed some records with rowid exist on target but not on Oracle

Error on CDC:

AdapterException | TGT_KUDU_Insert_CB_ORDERS

Error while writing batch on table impala::cbs.cb_subs_pos_services Reason Couldnot persist row : (string servid="AAAjKWAGZAACVsiAA/") : Row error for primary key="AAAjKWAGZAACVsiAA/", tablet=null, server=5a392d04a3924313b38b311cf514afab, status=Not found: key not found (error 0). Cause: Couldnot persist row : (string servid="AAAjKWAGZAACVsiAA/") : Row error for primary key="AAAjKWAGZAACVsiAA/", tablet=null, server=5a392d04a3924313b38b311cf514afab, status=Not found: key not found (error 0)

Kudu entry for table (cb_subs_pos_services) is shown

servid

AAAjKWAGZAACVsiAA/

account_link_code_n

24431620

acc_serv_flag_v

S

action_parm_strg_v

NULL

application_name_v

NULL

authorised_by_n

NULL

authorised_date_dt

NULL

cancelled_by_n

NULL

cancelled_date_dt

NULL

cancel_comments_v

NULL

cancel_reason_code_v

NULL

charges_present_flg_v

NULL

chrono_num_n

NULL

collected_upfront_v

N

comments_v

NULL

created_by_n

2072

critical_service_flg_v

N

execute_immediate_flg_v

NULL

from_self_care_flg_v

N

last_modified_date_d

2021-07-24 15:27:05.382000000

location_code_v

DD_CV

login_name_v

NULL

margin_amount_n

0

move_to_schdl_flg_v

NULL

order_amount_n

239435

order_ref_no

26974758

order_type_v

O

print_work_order_indicators_v

0:0:0

priority_flg_n

0

processed_date_dt

2021-07-20 12:28:09.702000000

reason_code_v

NULL

receipt_ref_num_v

NULL

refunded_date_dt

NULL

rejected_reason_v

NULL

requested_date_dt

2021-07-20 12:27:47.428000000

schdl_link_code_n

10749976

scheme_ref_code_n

NULL

serial_num_n

1

service_code_v

FXL

service_key_code_v

INST

serv_acc_link_code_n

24431621

status_optn_v

PS

trans_date_dt

2021-07-20 12:27:47.346000000

trans_num_v

NULL

op_insert_date

NULL

op_update_date

2021-07-24 15:27:04.784000000

Oracle entry:

$C:\Users\RoshanBi\AppData\Local\Temp\msohtmlclip1\01\clip_image002.jpg$

Table DDL on Kudu:

CREATE EXTERNAL TABLE cbs.cb_subs_pos_services ( servid STRING NOT NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION, account_link_code_n BIGINT NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION, acc_serv_flag_v STRING NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION, action_parm_strg_v STRING NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION, application_name_v STRING NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION, authorised_by_n BIGINT NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION, authorised_date_dt TIMESTAMP NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION, cancelled_by_n BIGINT NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION, cancelled_date_dt TIMESTAMP NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION, cancel_comments_v STRING NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION, cancel_reason_code_v STRING NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION, charges_present_flg_v STRING NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION, chrono_num_n BIGINT NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION, collected_upfront_v STRING NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION, comments_v STRING NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION, created_by_n BIGINT NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION, critical_service_flg_v STRING NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION, execute_immediate_flg_v STRING NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION, from_self_care_flg_v STRING NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION, last_modified_date_d TIMESTAMP NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION, location_code_v STRING NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION, login_name_v STRING NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION, margin_amount_n INT NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION, move_to_schdl_flg_v STRING NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION, order_amount_n BIGINT NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION, order_ref_no STRING NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION, order_type_v STRING NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION, print_work_order_indicators_v STRING NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION, priority_flg_n BIGINT NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION, processed_date_dt TIMESTAMP NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION, reason_code_v STRING NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION, receipt_ref_num_v STRING NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION, refunded_date_dt TIMESTAMP NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION, rejected_reason_v STRING NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION, requested_date_dt TIMESTAMP NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION, schdl_link_code_n BIGINT NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION, scheme_ref_code_n BIGINT NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION, serial_num_n BIGINT NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION, service_code_v STRING NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION, service_key_code_v STRING NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION, serv_acc_link_code_n BIGINT NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION, status_optn_v STRING NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION, trans_date_dt TIMESTAMP NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION, trans_num_v STRING NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION, op_insert_date TIMESTAMP NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION, op_update_date TIMESTAMP NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION, PRIMARY KEY (servid) ) PARTITION BY HASH (servid) PARTITIONS 16 STORED AS KUDU TBLPROPERTIES ('external.table.purge'='TRUE', 'kudu.master_addresses'='rb-hadoop-03.mtg.local,rb-hadoop-04.mtg.local,rb-hadoop-05.mtg.local')

Table DDL on Oracle:

CREATE TABLE "CBSOGG"."CB_SUBS_POS_SERVICES"

( "ORDER_REF_NO" VARCHAR2(20 BYTE),

"SERIAL_NUM_N" NUMBER(3,0),

"ORDER_TYPE_V" VARCHAR2(1 BYTE),

"TRANS_NUM_V" VARCHAR2(20 BYTE),

"TRANS_DATE_DT" TIMESTAMP (6) WITH TIME ZONE,

"SERVICE_CODE_V" VARCHAR2(3 BYTE),

"ACCOUNT_LINK_CODE_N" NUMBER(10,0),

"SERV_ACC_LINK_CODE_N" NUMBER(10,0),

"SERVICE_KEY_CODE_V" VARCHAR2(4 BYTE),

"SCHEME_REF_CODE_N" NUMBER(6,0),

"ACTION_PARM_STRG_V" VARCHAR2(200 BYTE),

"PROFIT_CENTRE_CODE" VARCHAR2(6 BYTE),

"PRIORITY_FLG_N" NUMBER(2,0) DEFAULT 0,

"OFFER_GROUP_CODE_V" VARCHAR2(2 BYTE),

"CRITICAL_SERVICE_FLG_V" VARCHAR2(1 BYTE) DEFAULT 'N',

"CHARGES_PRESENT_FLG_V" VARCHAR2(1 BYTE),

"ORDER_AMOUNT_N" NUMBER(14,0),

"COLLECTED_UPFRONT_V" VARCHAR2(1 BYTE) DEFAULT 'N',

"RECEIPT_REF_NUM_V" VARCHAR2(20 BYTE),

"STATUS_OPTN_V" VARCHAR2(2 BYTE),

"EXECUTE_IMMEDIATE_FLG_V" VARCHAR2(1 BYTE),

"MOVE_TO_SCHDL_FLG_V" VARCHAR2(1 BYTE),

"REQUESTED_DATE_DT" TIMESTAMP (6) WITH TIME ZONE,

"PROCESSED_DATE_DT" TIMESTAMP (6) WITH TIME ZONE,

"CREATED_BY_N" NUMBER(10,0),

"AUTHORISED_BY_N" NUMBER(10,0),

"AUTHORISED_DATE_DT" TIMESTAMP (6) WITH TIME ZONE,

"CANCELLED_BY_N" NUMBER(10,0),

"CANCELLED_DATE_DT" TIMESTAMP (6) WITH TIME ZONE,

"REFUNDED_DATE_DT" TIMESTAMP (6) WITH TIME ZONE,

"REFUND_TRANS_NO_V" VARCHAR2(20 BYTE),

"FROM_SELF_CARE_FLG_V" VARCHAR2(1 BYTE) DEFAULT 'N',

"SCHDL_LINK_CODE_N" NUMBER(10,0),

"REASON_CODE_V" VARCHAR2(10 BYTE),

"COMMENTS_V" VARCHAR2(300 BYTE),

"CANCEL_REASON_CODE_V" VARCHAR2(10 BYTE),

"CANCEL_COMMENTS_V" VARCHAR2(200 BYTE),

"LOCATION_CODE_V" VARCHAR2(10 BYTE),

"REJECTED_REASON_V" VARCHAR2(300 BYTE),

"LOGIN_NAME_V" VARCHAR2(30 BYTE),

"BANK_DETAILS_V" VARCHAR2(1000 BYTE),

"APPLICATION_NAME_V" VARCHAR2(20 BYTE),

"MARGIN_AMOUNT_N" NUMBER(14,0) DEFAULT 0,

"CHRONO_NUM_N" NUMBER(10,0),

"PRINT_WORK_ORDER_INDICATORS_V" VARCHAR2(5 BYTE) DEFAULT '0:0:0',

"ERROR_DESC_V" VARCHAR2(200 BYTE),

"SALE_TRANSACTION_NUMBER_V" VARCHAR2(50 BYTE),

"LAST_MODIFIED_DATE_D" TIMESTAMP (6) WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,

"ACC_SERV_FLAG_V" VARCHAR2(1 BYTE) DEFAULT 'S',

"RESUBMIT_COUNT_N" NUMBER(4,0),

"WAIVE_OFF_APPROVAL_V" VARCHAR2(2 BYTE),

SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS,

SUPPLEMENTAL LOG DATA (UNIQUE INDEX) COLUMNS,

SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS,

SUPPLEMENTAL LOG DATA (ALL) COLUMNS

) SEGMENT CREATION IMMEDIATE

PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

NOCOMPRESS LOGGING

STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645