Merge join IO cost.
2013-07-06 01:48:08
How come one to one and one to many merge join have no IO cost
USE tempdb GO CREATE TABLE #t1 (id INT NOT NULL PRIMARY KEY CLUSTERED, val INT NOT NULL) CREATE TABLE #t2 (id INT NOT NULL, id2 INT NOT NULL PRIMARY KEY CLUSTERED (id, id2), val INT NOT NULL) UPDATE STATISTICS #t1 WITH ROWCOUNT = 1000000 UPDATE STATISTICS #t2 WITH ROWCOUNT = 1000000 SELECT * FROM #t1 a INNER JOIN #t2 b ON a.id = b.id SELECT * FROM #t1 a INNER JOIN #t1 b ON a.id = b.id SELECT * FROM #t2 a INNER JOIN #t2 b ON a.id = b.id
2013-07-06 02:10:56
Only many-to-many merge join requires a worktable (and hence, I/O).
GokhanVarol 2013-07-06 02:12:22
oh, i see the IO is generated at the scan lower down
2013-07-06 02:14:17
Yes, the merge join itself doesn't incur any I/O unless it uses a worktable.