Page 1 of 1

Parallel loading with Referential Integrity

Posted: Fri Nov 05, 2010 7:23 am
by RaviReena
I want to load/append the data to an empty table using ORACLE Enterprise stage in parallel.I have a referential integrity with other table for my target table and i want to reject/drop the rows which violates the referential Integrity.

what is the best design approach for this scenario of loading around 10million rows.


Any help is appericiated.

Posted: Sun Nov 07, 2010 10:38 pm
by prakashdasika
Join the dataset with the table with which you have the referential integrity and drop/reject the records and send only the ones that satisfy the constraints. You can use three stages:

1. Join - You can do a inner join and drop the records or do a left outer join and capture the records in the Transformer stage. Do not forget to sort the records on the keys

2. Merge - Similar to join, the data should be sorted and the links should be correctly choosen (MASTER AND UPDATE) and the rejected records can be directly captured in this stage itself.

3. Lookup - Define data stream and lookup stream properly and use the constraint to captue or drop the rejects. This can be a risky option due to volume of records.