Sparse look up

badga · Post by **badga** » Mon Jun 07, 2010 1:35 am

Hi,

I am new to datastage, learning the basic concepts of all the stages.
Can anyone help me,when to use sparse lookup? What is the difference between normal & sparse look up? And Also how to do a 'Cross Join'?

ArndW · Post by **ArndW** » Mon Jun 07, 2010 1:42 am

A "normal" lookup will read all of the reference data into memory to speed up subsequent lookups. A "sparse" lookup will perform a read of the reference for each data row. The latter is best when there are a large number of reference rows and just a few data rows, or where the reference data values can change during the course of a single run.

A "cross join" is an inner join where the condition is always met.

badga · Post by **badga** » Mon Jun 07, 2010 2:03 am

Thanks for your quick reply.. :D
So for sparse look up you mean to say that the reference data will not be loaded into RAM for comparision?
Is their any option in Look up stage where we can specify the look up as 'Sparse look up'?

ArndW · Post by **ArndW** » Mon Jun 07, 2010 2:10 am

Yes (normal lookup loaded into RAM)
Yes (specify normal or sparse lookup in the source DB stage)

ray.wurlod · Post by **ray.wurlod** » Mon Jun 07, 2010 3:15 am

Sparse lookup is only supported against certain databases (DB2 and Oracle, if memory serves), and the option to use it is actually made on the database stage rather than in the Lookup stage.

srini.dw · Post by **srini.dw** » Thu Aug 08, 2013 11:33 pm

ArndW wrote:A "cross join" is an inner join where the condition is always met.

I know its old post, but the information is wrong, just want to highlight

CROSS JOIN returns the Cartesian product of rows from tables in the join. In other words, it will produce rows which combine each row from the first table with each row from the second table.