Merge join IO cost.

GokhanVarol 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

alt text

SQLkiwi 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
SQLkiwi 2013-07-06 02:14:17
Yes, the merge join itself doesn't incur any I/O unless it uses a worktable.