Re: Hash Joins vs. Nested Loops

Ryan,

There is a big difference, but not all the time. In general your colleague is right. On an OLTP system. Hash join is used in data warehouse environment when you want to retrieve all data. A hash join implies full table scan of the two tables. But now, why should I fully scan the two tables with one million records each when all I need is only few records from one of the table with all related records from the other one. More than that, the nested joins work like two imbricates loops. And if your outer select call some functions based on some of the columns, this treatment can be done before reaching the end of the loop. So there is big difference on performance and I tell you is not urban legend. Therefore if you only need retrieve all data from the two tables, the hash join is the prefered one by the optimizer and there is no reason to force a nested join.

On 20 Feb 2003 10:25:06 -0800, rgaffuri_at_cox.net (Ryan Gaffuri) wrote:

>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.
Received on Sun Feb 23 2003 - 14:21:49 CST