Re: Hash Joins vs. Nested Loops

rgaffuri_at_cox.net (Ryan Gaffuri) wrote in message news:<1efdad5b.0302201025.670fb5b8_at_posting.google.com>...
> Someone I was working with told me the following. I was wondering if
> anyone has any comments, because I have not seen this in any
> articles/books.
>
> He said that when he tunes he generally likes to try to force nested
> loop joins over hash joins because these tend to increase performance?
> He has done this for a long time and he said that is based on
> experience.
>
> Anyone else notice this? I have never seen this anywhere else. BTW, I
> know the difference between hash joins and nested loops and know when
> either is generally used by the optimizer.

The rule of thumb: If you need to filter tables before join, use hash or sort/merge. If not, use nested loops. Hash joins and sort/merge joins are very efficient when you join big tables, both in number of rows and columns and have some conditions restricting the number of rows/columns in the output. In case of hash or sort/merge, filtering is done before join. In case of nested loops, filtering is done after the loops. You can clearly see it in explained plans.

So, the correct answer is: Depends on your query. I had huge performance gains by forcing hash joins. I also had huge performance gains forcing nested loop instead of hash join. Received on Tue Feb 25 2003 - 11:04:45 CST