Re: Hash Joins vs. Nested Loops

Donovan R. <mdonovan_at_hotmail.com> wrote in message news:<idai5v020vkgn5nvst2an7io9c8l46shth_at_4ax.com>...
> 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.
>

Hash join is faster than people may think. I can safely say that if your tables have millions of rows and you need to join at least 20 percent of the data, then hash join easily beats nested loops.

>
>
> 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 Mon Feb 24 2003 - 10:30:48 CST