sql-server – Hash,Merge和Loop join之间的区别？

在SQL Server中,您可以指定连接提示：

> HASH JOIN
> MERGE JOIN
> LOOP JOIN

这三个连接提示的定义是什么,何时应该使用？

解决方法

SQL Server employs three types of join operations:

• Nested loops joins

• Merge joins

• Hash joins

If one join input is small (fewer than 10 rows) and the other join input is fairly large and indexed on its join columns,an index nested loops join is the fastest join operation because they require the least I/O and the fewest comparisons. For more information about nested loops,see Understanding Nested Loops Joins.

If the two join inputs are not small but are sorted on their join column (for example,if they were obtained by scanning sorted indexes),a merge join is the fastest join operation. If both join inputs are large and the two inputs are of similar sizes,a merge join with prior sorting and a hash join offer similar performance. However,hash join operations are often much faster if the two input sizes differ significantly from each other. For more information,see Understanding Merge Joins.

Hash joins can efficiently process large,unsorted,non indexed inputs.

但我相信你应该从一个更基本的话题开始：Query Tuning,最后去使用查询提示.

（编辑：天瑞地安资讯网_黄海网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!