A join between local table ls1 and remote table rl1 results in the following query being sent to the remote server:
select a,b,c from localserver.testdb.dbo.ls1 t1, rl1 t2 where t1.a = t2.a
The statement sent to the remote server contains a fully qualified reference back to the local table on the local system. The remote server will either dynamically create a temporary proxy table definition for this table, or use an existing proxy table with a matching mapping. The remote server will then execute the join and return the result set back to the local server.
The plan for this relocated join would appear as:
Emit | DXCHG | JOIN / \ DXCHG Scan (rl1) / Scan (ls1)
This plan has two distributed exchange operators present.
The DXCHG above the scan
of local table ls1 represents the cost of the
remote server pulling results to the remote system. The DXCHG at
the top of the plan represents the movement of the join result back
to the local system.
