下面例子中的表aaa、bbb分别位于两个库,aaa所在库通过数据库链db244访问bbb表：

create table aaa as select * from dba_objects;
create table bbb as select * from dba_objects;

1、嵌套循环连接

SQL> select count(*) from aaa a
where exists
(select * from bbb@db244 b where a.object_id=b.object_id);

COUNT(*)
----------
27165

Elapsed: 00:04:04.74

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 FILTER
3 2 TABLE ACCESS (FULL) OF 'AAA'
4 2 REMOTE* DB244.US.ORACLE.COM
4 SERIAL_FROM_REMOTE SELECT "OBJECT_ID" FROM "BBB" "B" WHERE "OBJECT_ID"=:1

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
437 consistent gets
0 physical reads
0 redo size
381 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

2、排序合并连接

SQL> select count(*) from aaa a
where a.object_id in
(select object_id from bbb@db244 b);

COUNT(*)
----------
27165

Elapsed: 00:00:00.59

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 MERGE JOIN
3 2 SORT (JOIN)
4 3 TABLE ACCESS (FULL) OF 'AAA'
5 2 SORT (JOIN)
6 5 VIEW OF 'VW_NSO_1'
7 6 SORT (UNIQUE)
8 7 REMOTE* DB244.US.ORACLE.COM
8 SERIAL_FROM_REMOTE SELECT "OBJECT_ID" FROM "BBB" "B"

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
437 consistent gets
0 physical reads
0 redo size
381 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
1 rows processed

3、通过提示改用hash连接

SQL> select /*+ use_hash(a,b) */count(*) from aaa a
where exists
(select * from bbb@db244 b
where a.object_id=b.object_id);

COUNT(*)
----------
27165

Elapsed: 00:00:00.51

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=91 Card=1 Bytes=26)
1 0 SORT (AGGREGATE)
2 1 HASH JOIN (SEMI) (Cost=91 Card=27172 Bytes=706472)
3 2 TABLE ACCESS (FULL) OF 'AAA' (Cost=47 Card=27172 Bytes=353236)

4 2 VIEW OF 'VW_SQ_1' (Cost=42 Card=32509 Bytes=422617)
5 4 REMOTE* (Cost=42 Card=32509 Bytes=422617)
DB244.US.ORACLE.COM
5 SERIAL_FROM_REMOTE SELECT "OBJECT_ID" FROM "BBB" "B"

Statistics
----------------------------------------------------------
3 recursive calls
0 db block gets
468 consistent gets
0 physical reads
0 redo size
381 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:456820211101

Followup:
the query plan showed us that -- two remote operations followed by a filter.
gives us the remote query that was executed and all.

it is basically taking:

2 select tracknum, status, transdate, insseq
10:09:24 3 from workhistory@wkl
10:09:24 4 where tracknum in (select distinct tracknum from workhistory@wkl
where insseq = 142);

and executing it like this:


for x in ( select * from remote_table )
loop
query remote table again by tracknum to get tracknum and insseq

if one of the the returned insseq's is 142
then
output record
end if
end loop

Execution Plan
----------------------------------------------------------
0 INSERT STATEMENT Optimizer=CHOOSE (Cost=172346 Card=57009 By
tes=2052324)

1 0 FILTER
2 1 REMOTE* (Cost=1319 Card=57009 Bytes=2052324) WKL
3 1 REMOTE* (Cost=3 Card=1 Bytes=24) WKL


2 SERIAL_FROM_REMOTE SELECT "TRACKNUM","STATUS","TRANSDATE","INSS
EQ" FROM "WORKHISTORY" "SYS_ALIAS_1"

3 SERIAL_FROM_REMOTE SELECT "TRACKNUM","INSSEQ" FROM "WORKHISTORY
" "WORKHISTORY" WHERE "TRACKNUM"=:1