Duplicate Records Exist in PC_ECO_ITEM_F,PC_MCO_ITEM_F Tables after Oracle Product Lifecycle Analytics(OPLA) Change Data Capture (CDC) Extract, Transfer,Load(ETL) (Doc ID 2809258.1)

Last updated on SEPTEMBER 27, 2021

Applies to:

Symptoms

ACTUAL BEHAVIOR
Duplicate records exist in PC_ECO_ITEM_F,PC_MCO_ITEM_F tables after CDC

After FULL LOAD, There were no duplicates found in PC_ECO_ITEM_F with combination of given key columns PC_ECO_ID, ITEM_ID, SITE_ID, ITEM_REV_ID.

However, After scheduling incremental loads, observed duplicate records in PC_ECO_ITEM_F , PC_MCO_ITEM_F tables.

Here are the queries to find the duplicates.

SELECT PC_ECO_ID, ITEM_ID, SITE_ID, ITEM_REV_ID, COUNT(*) FROM PC_ECO_ITEM_F GROUP BY PC_ECO_ID, ITEM_ID, SITE_ID, ITEM_REV_ID HAVING COUNT(*) > 1

SELECT B.PC_ECO_ID, B.ITEM_ID, B.SITE_ID, B.ITEM_REV_ID, COUNT(*) FROM ( SELECT * FROM PC_ECO_ITEM_F WHERE SITE_ID = 0 ) B GROUP BY B.PC_ECO_ID, B.ITEM_ID, B.SITE_ID, B.ITEM_REV_ID HAVING COUNT(*) > 1


EXPECTED BEHAVIOR

There should not be any duplicate values in PC_ECO_ITEM_F , PC_MCO_ITEM_F tables after CDC ETL.


STEPS

The issue can be reproduced at will with the following steps:
1. Run Incremental load
2. Run the below queries:

SELECT PC_ECO_ID, ITEM_ID, SITE_ID, ITEM_REV_ID, COUNT(*) FROM PC_ECO_ITEM_F GROUP BY PC_ECO_ID, ITEM_ID, SITE_ID, ITEM_REV_ID HAVING COUNT(*) > 1

SELECT B.PC_ECO_ID, B.ITEM_ID, B.SITE_ID, B.ITEM_REV_ID, COUNT(*) FROM ( SELECT * FROM PC_ECO_ITEM_F WHERE SITE_ID = 0 ) B GROUP BY B.PC_ECO_ID, B.ITEM_ID, B.SITE_ID, B.ITEM_REV_ID HAVING COUNT(*) > 1

3. Can see that the PC_ECO_ITEM_F , PC_MCO_ITEM_F tables have duplicates

Changes

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.