This content has been marked as final. Show 4 replies
Papai wrote:Theoretically the lookup is doing a join, you get additional configuration to configure how it is implemented but the end result is the same.
I have checked this but if you provide some theoretical knowledge it will be more helpful
because I am unable understand the generated code.This is going to be an issue in the long run (ODI generates code to do all of its actions) , to really know the difference in this case I would do a little reading on :
Join types (full, inner, outer, left, right etc)
Correlated and Nested Subqueries (as per one of the results in the link given to you above).
If you nail those two, you will be able to look at the generated code and decide which implementation technique is right for YOU on your environment, it wont be the same for everyone one..... which leads me to suggest you look at Explain plans , Oracle optimizer and how to do basic SQL Performance comparison - this will help you decide which method you use.
BTW - In 10G we used to do all the lookups we ever needed, using Joins. We can also manually define nested/correlated select statements for an individual column in the target datastore - its just we needed to manually type it out :-)
Hi, recent docs on ODI indicate a bit of configurability for Lookups, and recommend a particular approach for small-sized lookups:
"A wizard is available in the interface editor to create lookups using a source as the driving table and a model or target datastore as the driving table. These lookups now appear as a compact graphical object in the Sources diagram of the interface. The user can choose how the lookup is generated: as a Left Outer Join in the FROM clause or as an expression in the SELECT clause (in-memory lookup with nested loop). This second syntax is sometimes more efficient on small lookup tables."