This discussion is archived
4 Replies Latest reply: Nov 27, 2012 12:33 PM by 110797 RSS

Lookup Vs Join

Papai Newbie
Currently Being Moderated
Hi,
I want to know the basic difference between Lookup and Join in odi.
Thanks
  • 1. Re: Lookup Vs Join
    Bhabani Ranjan Guru
    Currently Being Moderated
    Have a look.
    http://dwteam.in/lookup-in-odi-11g/

    Thanks.
  • 2. Re: Lookup Vs Join
    Papai Newbie
    Currently Being Moderated
    Hi Bhabani,
    I have checked this but if you provide some theoretical knowledge it will be more helpful because I am unable understand the generated code.
    Please Ans
  • 3. Re: Lookup Vs Join
    PeakIndicators_Alastair Guru
    Currently Being Moderated
    Papai wrote:
    Hi Bhabani,
    I have checked this but if you provide some theoretical knowledge it will be more helpful
    Theoretically the lookup is doing a join, you get additional configuration to configure how it is implemented but the end result is the same.

    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 :-)
  • 4. Re: Lookup Vs Join
    110797 Newbie
    Currently Being Moderated
    Hi, recent docs on ODI indicate a bit of configurability for Lookups, and recommend a particular approach for small-sized lookups:
    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."

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points