Difference in the implementation of lookup and join
stages,in joining two tables?
- 7 Answers
- 14283 Views
- TCS, I also Faced
- E-Mail Answers
Answers were Sorted based on User's Feedback
Answer / kiran
Hai This is Kiran...
If u want to join more than one table ,u can use join,lookup
and merge also.
Join: it is used join more than one table based one key
column .it can perform 4 join as inner join,left outer
join,right join and full outer join.
Lokk-UP:it is used join more than one table,but not necesary
to join based ont he key column but it need data-type.it
give reference link and single out put link and give reject
link also.it can perform inner join and left outer jojn.
main difference: if the huge amount of the data contain in
reference table refer to join else look-up.
| Is This Answer Correct ? | 14 Yes | 2 No |
Answer / krishna
Generally we are using lookup for comparision purpose,
based on the reference table size we r using join or lookup.
If the reference table size is less than the main table
then use lookup stage other wise use join stage.
In lookup we have two types:
Normal lookup - reference lookup is less no of rows
compared to main table
sparse lookup - reference lookup is more no of rows
compared to main table.
But the peformance wise use join if the reference table has
more data.
Regards,
Krishna
| Is This Answer Correct ? | 8 Yes | 0 No |
Answer / zulfi123786
Hi This is Zulfi
Basically Join is used when you have large amount of data
about in millions and it performs inner join,left
outer,right outer and full outer joins
The join stage requires the incomming data to be hash
partitioned and sorted on the joining keys
The look up is used when the reference records are fewer in
number about less than one lakh and it doesnot require the
incomming source data to be sorted, instead the refrence
link should be in Entire partition mode.
In look up there are two types
Normal and Sparse
Sparse is available only when the reference is a database.
usually Normal has to be used unless when the refrence to
source rows ratio is 100:1
| Is This Answer Correct ? | 8 Yes | 0 No |
Answer / sadanand
HI All,
I would like to add one more point to JOIN.
To achieve full outer join the number of inputs need would
be only two.
The Primay table need to be sorted.
Memory used is very less compared to Lookup.
Regards,
Sadanand.
| Is This Answer Correct ? | 2 Yes | 0 No |
Answer / indian
Hi Zulfi..you are answer is more explained one and clear
we will go for Merge if we want the rejected data for every
update link
| Is This Answer Correct ? | 0 Yes | 0 No |
30 jobs are running in unix.i want to find out my job.how to do this?Give me command?
What is a datastage job?
how to handle null values using transformer stage?
Have you have ever worked in unix environment and why it is useful in datastage?
Hi, I have 3 records, 1 Srini 1 vasa 2 reddy now i want o/p like this 1 Srinivasa 2 reddy could u please help in this Thanks in advance.
if a column contains data like ram,rakesh,madhan,suraj,pradeep,bhaskar then I want to place names separated by commas in another columns how can we do?
How many Key we can define in remove duplicate stage?
i WANTED TO USE THE RANGE LOOKUP SCENARIO IN DATASTAGE 7.5.2 SRVER JOB.i HAVE A DATE FIELD IN SOURCE AND I SHOULD MATCH IT WITH A FIELD IN LOOKUP FILE.BUT,THE FIELDS SHOULD MATCH EVEN THOUGH THERE IS SOME RANGE.CAN SOMEONE TELL ME HOW CAN I DO THAT. THANKS
What are the repository tables in datastage?
there are two schemas x and y are there. some data is in x schema. i want to use that in y schema..how can i use? please give some possibilities
What are the enhancements made in datastage 7.5 compare with 7.0?
how to clear the source file? 2) diff b/t odbc & Oracle?what is node? 3) explain one complex job u face in ur project
Teradata 
