Lookup Stage get more records than primary link records

jenny_wang · Post by **jenny_wang** » Mon Jul 14, 2008 3:02 am

the job use a lookup stage, the primary link is reading records from a records.ds. the reference link is a oracle stage, the records.ds have 22697 records and the oracle stage output 304 records,but the lookup stage output 68091 records.

could you please help me to find out why this happened ?

mahadev.v · Post by **mahadev.v** » Mon Jul 14, 2008 3:05 am

Probably due to duplicates in the reference data. Remove the duplicates in the reference link on the lookup keys.

ray.wurlod · Post by **ray.wurlod** » Mon Jul 14, 2008 3:16 am

Possibly also due to incorrect partitioning.

jenny_wang · Post by **jenny_wang** » Mon Jul 14, 2008 3:26 am

hi,Ray
which partition should be used? how can i know whether this is caused by wrong partition?

i found that there are duplicated entries in the oracle output, but there is no warnings in the log.

ray.wurlod · Post by **ray.wurlod** » Mon Jul 14, 2008 3:34 am

Hash partition by the lookup key on the stream input and either Hash or Entire partition by the lookup key on the reference input. Do check to see whether there are duplicates in your reference input - if you don't want them interpolate a Remove Duplicates stage on this link, or simply include a DISTINCT keyword in the Oracle query.

mahadev.v · Post by **mahadev.v** » Mon Jul 14, 2008 3:37 am

Generally an entire partition is used on the reference link and the main link is left to auto. No warnings in the log (i am guessing) could be because your key fields are different from the lookup keys.

jenny_wang · Post by **jenny_wang** » Mon Jul 14, 2008 3:47 am

hi, mahadev
if there exist duplicated entries, warnings will show up in the log. i have a column named site_unit, the oracle output two columns named site_key and site_unit, if the two site unit are identical, the site_key is output or the site_key is null. I am not clear about what you said " because your key fields are different from the lookup keys. "

ray.wurlod · Post by **ray.wurlod** » Mon Jul 14, 2008 4:19 am

No warnings are logged if your Lookup stage specifies "allow duplicates".

jenny_wang · Post by **jenny_wang** » Mon Jul 21, 2008 2:23 am

I remove the duplicated records and the job works well. thanks for help!