请结合实际情况解释下nest loop 和hash loop

显示全部楼层 · 2007-9-26 18:42:10

什么时候发生，适用于什么情况？
请看下面两个语句其中im_school是学校表，pm_person是学生表，学生表的schoolid是学校表的主键，有外键索引，学校表的code上也有索引。

select * from im_school s, pm_person p where p.schoolid = s.id
|--Hash Match(Inner Join, HASH

.[id])=([p].[schoolid]), RESIDUAL

.[id]=[p].[schoolid]))
|--Clustered Index Scan(OBJECT

[citycarddbtest].[dbo].[im_school].[PK_IM_SCHOOL] AS ))
|--Clustered Index Scan(OBJECT

[citycarddbtest].[dbo].[pm_person].[PK_PM_PERSON] AS [p]))

select * from im_school s, pm_person p where p.schoolid = s.id and s.code='01021001'

|--Bookmark Lookup(BOOKMARK

[Bmk1001]), OBJECT

[citycarddbtest].[dbo].[pm_person] AS [p]) WITH PREFETCH)
|--Nested Loops(Inner Join, OUTER REFERENCES

.[id]))

|--Bookmark Lookup(BOOKMARK

[Bmk1000]), OBJECT

[citycarddbtest].[dbo].[im_school] AS ))

||--Index Seek(OBJECT

[citycarddbtest].[dbo].[im_school].[i_im_sch_code] AS ), SEEK:(.[code]='01021001') ORDERED FORWARD)

|--Index Seek(OBJECT:([citycarddbtest].[dbo].[pm_person].[index_pm_schoolid] AS [p]), SEEK:([p].[schoolid]=.[id]) ORDERED FORWARD)
请结合这两句给解释下，为什么第一句就是hash，第二句就是nested?其中im_school 有1944行，pm_person有1000972行

千问 · 2007-9-26 18:42:10

有书上这样介绍：
当一个连接输入很小（10行或更少），而其他连接输入相当大并且在它的连接上进行索引时，索引嵌套循环操作是最快的，这是因为他需要最少的I/O和最少的比较。如果两个连接输入都不小，但它们以它们的连接列排序，那么合并连接是最快的。如果两个输入都很大，且这两个输入相似大，那么合并连接和哈希连接提供相似性能。然而，如果这两个输入大小差别悬殊，那么哈希连接通常会快很多。
那么可以理解第一就是hash，第二句是nested。因为第二句的一个输入只是一行，符合条件。而第一个语句是两个输入相差悬殊，符合hash条件。
呵，自问自答了，碰巧看到这页书了。