Topic
  • 2 replies
  • Latest Post - ‏2014-07-07T14:30:43Z by Ajay Kulkarni
Ajay Kulkarni
Ajay Kulkarni
23 Posts

Pinned topic Nested Loop Vs Merge Vs HAsh Join

‏2014-07-07T03:49:52Z |

 

Hello

While we are writing query involving join, what should be a easy indicator for making a guess on the type of join (Nested, Merge, Hash) the optimizer would suggest?

Is there a way one can force a specific join by tweaking the join condition?

 

  • krmilligan
    krmilligan
    450 Posts

    Re: Nested Loop Vs Merge Vs HAsh Join

    ‏2014-07-07T14:24:35Z  

    How the join is written has no determination on the join method that the query optimizer will use.  The indexing strategy for the tables involved with the join is going to have the most direct impact on which join method that the optimizer will choose.

  • Ajay Kulkarni
    Ajay Kulkarni
    23 Posts

    Re: Nested Loop Vs Merge Vs HAsh Join

    ‏2014-07-07T14:30:43Z  

    How the join is written has no determination on the join method that the query optimizer will use.  The indexing strategy for the tables involved with the join is going to have the most direct impact on which join method that the optimizer will choose.

    Thanks, Is there a way by which just by observation a programmer can make a guess (not necessary a correct one) on what type of join (Hash, Merge or NLJ) the optimizer may use? I am sure there may be ton of factors the optimizer may be looking at like the Size of the table, the cardinality, seclectivity, the index.

    But if one has to make a guess , what would be the hint that can help one make the guess. What i am trying to get at is to understand at a very high level how Optimizer decides between Hash /Merge/NLJ