oracle一般把使用dblink的表做为远端，下面语句把yz.remot_tab作为远程表
SQL> select l.object_id,l.object_name from yzxd.local_tab l,yz.remot_tab@remote r where l.object_id=r.object_id and l.object_id=2;
OBJECT_ID OBJECT_NAME
---------- --------------------------------------------------------------------------------------------------------------------------------
2 C_OBJ#
Execution Plan
----------------------------------------------------------
Plan hash value: 635280512
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst |IN-OUT|
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 24 | 4 (0)| 00:00:01 | | |
| 1 | MERGE JOIN CARTESIAN| | 1 | 24 | 4 (0)| 00:00:01 | | |
|* 2 | TABLE ACCESS FULL | LOCAL_TAB | 1 | 11 | 3 (0)| 00:00:01 | | |
| 3 | BUFFER SORT | | 1 | 13 | 1 (0)| 00:00:01 | | |
| 4 | REMOTE | REMOT_TAB | 1 | 13 | 1 (0)| 00:00:01 | REMOTE | R->S |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("L"."OBJECT_ID"=2)
Remote SQL Information (identified by operation id):
----------------------------------------------------
4 - SELECT "OBJECT_ID" FROM "YZ"."REMOT_TAB" "R" WHERE "OBJECT_ID"=2 (accessing
'REMOTE' )
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
623 bytes sent via SQL*Net to client
552 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
使用driving_site 把yz.remot_tab作为本地表，会把谓词access("A1"."OBJECT_ID"=2)传到远端执行，把执行结果传输过来而不是把整个表的数据传输过来
SQL> select /*+driving_site(r) */ l.object_id,l.object_name from yzxd.local_tab l,yz.remot_tab@remote r where l.object_id=r.object_id and l.object_id=2;
OBJECT_ID OBJECT_NAME
---------- --------------------------------------------------------------------------------------------------------------------------------
2 C_OBJ#
Execution Plan
----------------------------------------------------------
Plan hash value: 1476067701
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst |IN-OUT|
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT REMOTE| | 2 | 168 | 3 (0)| 00:00:01 | | |
| 1 | MERGE JOIN CARTESIAN | | 2 | 168 | 3 (0)| 00:00:01 | | |
| 2 | REMOTE | LOCAL_TAB | 1 | 79 | 2 (0)| 00:00:01 | ! | R->S |
| 3 | BUFFER SORT | | 2 | 10 | 1 (0)| 00:00:01 | | |
|* 4 | INDEX RANGE SCAN | IDX_OBJECTID_REMOT_TAB | 2 | 10 | 1 (0)| 00:00:01 | QDDS | |
-----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("A1"."OBJECT_ID"=2)
Remote SQL Information (identified by operation id):
----------------------------------------------------
2 - SELECT "OBJECT_NAME","OBJECT_ID" FROM "YZXD"."LOCAL_TAB" "A2" WHERE "OBJECT_ID"=2 (accessing '!' )
Note
-----
- fully remote statement
Statistics
----------------------------------------------------------
9 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
623 bytes sent via SQL*Net to client
552 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
insert、update、delete 会使hint driving_site失效
SQL> insert into yzxd.mid_tab select /*+driving_site(r) */ l.object_id,l.object_name from yzxd.local_tab l,yz.remot_tab@remote r where l.object_id=r.object_id and l.object_id< 100;
98 rows created.
Execution Plan
----------------------------------------------------------
Plan hash value: 728795340
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst |IN-OUT|
------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 9 | 216 | 5 (0)| 00:00:01 | | |
| 1 | LOAD TABLE CONVENTIONAL | MID_TAB | | | | | | |
|* 2 | HASH JOIN | | 9 | 216 | 5 (0)| 00:00:01 | | |
|* 3 | TABLE ACCESS FULL | LOCAL_TAB | 9 | 99 | 3 (0)| 00:00:01 | | |
| 4 | REMOTE | REMOT_TAB | 97 | 1261 | 2 (0)| 00:00:01 | REMOTE | R->S |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("L"."OBJECT_ID"="R"."OBJECT_ID")
3 - filter("L"."OBJECT_ID"<100)
Remote SQL Information (identified by operation id):
----------------------------------------------------
4 - SELECT /*+ OPAQUE_TRANSFORM */ "OBJECT_ID" FROM "YZ"."REMOT_TAB" "R" WHERE
"OBJECT_ID"<100 (accessing 'REMOTE' )
Statistics
----------------------------------------------------------
4 recursive calls
36 db block gets
211 consistent gets
2 physical reads
4240 redo size
863 bytes sent via SQL*Net to client
985 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
98 rows processed
要想dml driving_site不失效，使用for循环查到语句一条一条插入。
BEGIN
FOR i IN (select /*+driving_site(r) */ l.object_id,l.object_name from yzxd.local_tab l,yz.remot_tab@remote r where l.object_id=r.object_id and l.object_id< 100)
LOOP
insert into yzxd.mid_tab values(i.object_id,i.object_name);
commit;
END LOOP;
COMMIT;
END;