LOOKUP HELP
Moderators: chulett, rschirm, roy
LOOKUP HELP
I have 26 million rows in oracle refrence table and around 2 lakh rows in a source (in my case ouput from a join). I need to implement a left outer join with driving table as a source(in my case ouput from a join). Is it better to go for a join stage with left outer condition or a lookup stage? If lookup stage it should be a sparse lookup or normal lookup?
Appreciate any help to sovle this issue.
Appreciate any help to sovle this issue.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
200000 sparse lookups (2 lakh) is quite a lot. If you follow that route, you should ensure that the reference table is properly indexed and that statistics are kept up to date.
What would the size of one of your lookup records be (average size of it contains varchars)? Size of key columns + size of data columns. You may be able to support a standard lookup instead of a sparse and possibly see better overall performance. Only way to know for certain is to try...it'll basically be down to the time it takes to extract the table compared to performing 200000 sparse lookups.
Regards,
What would the size of one of your lookup records be (average size of it contains varchars)? Size of key columns + size of data columns. You may be able to support a standard lookup instead of a sparse and possibly see better overall performance. Only way to know for certain is to try...it'll basically be down to the time it takes to extract the table compared to performing 200000 sparse lookups.
Regards,
- james wiles
All generalizations are false, including this one - Mark Twain.
All generalizations are false, including this one - Mark Twain.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Join (preceded by "don't sort, already sorted" Sort stage and relying on sorted data from the database only grabs one key value at a time from each link. Indeed, that's why it mandates sorted inputs; it would be horribly memory hungry otherwise, needing to load the entire right input into memory and probably build an index on the join keys.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
I have tested this case with join(left outer) and with lookup stage
(condition not met=continue,lookup failure=continue) .
In both of these cases the resulting records is not equal.
Suppose if have 1000 records in driving table and 2000 records in the right table. Join with left outer condition returns more than 1000 records (this is possible) but with lookup its returning only 1000 records. Do we have to change any lookup property to get desired result as in the case of left outer join.
(condition not met=continue,lookup failure=continue) .
In both of these cases the resulting records is not equal.
Suppose if have 1000 records in driving table and 2000 records in the right table. Join with left outer condition returns more than 1000 records (this is possible) but with lookup its returning only 1000 records. Do we have to change any lookup property to get desired result as in the case of left outer join.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Lookup with Continue will only return one row from the reference input unless you enable "multiple rows returned" on that input. This can only be enabled on one reference input per Lookup stage.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.