driving_site isn't really appropriate for a MERGE (or something that changes a table) because the MERGE *must* be done on the site where table sits.
I did this on two db's
SQL> create table t as select OWNER
2 ,OBJECT_NAME
3 ,SUBOBJECT_NAME
4 ,OBJECT_ID
5 ,DATA_OBJECT_ID
6 ,OBJECT_TYPE
7 ,CREATED
8 ,LAST_DDL_TIME
9 ,TIMESTAMP
10 ,STATUS
11 ,TEMPORARY
12 ,GENERATED
13 ,SECONDARY
14 ,NAMESPACE
15 ,EDITION_NAME
16 ,SHARING
17 ,EDITIONABLE
18 ,ORACLE_MAINTAINED from dba_objects;
Table created.
and then we can see the impact of the driving_site hint in a *select*
select /*+ driving_site(src) */ *
from t@np121 tgt,
(SELECT * FROM t WHERE ROWID IN (SELECT rowid FROM t where rownum < 1000)
) src
where src.object_id = tgt.object_id;
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst |IN-OUT|
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 249 | 288 (2)| 00:00:01 | | |
|* 1 | HASH JOIN | | 1 | 249 | 288 (2)| 00:00:01 | | |
| 2 | NESTED LOOPS | | 1 | 134 | 9 (12)| 00:00:01 | | |
| 3 | VIEW | VW_NSO_1 | 999 | 11988 | 7 (0)| 00:00:01 | | |
| 4 | HASH UNIQUE | | 1 | 11988 | | | | |
|* 5 | COUNT STOPKEY | | | | | | | |
| 6 | TABLE ACCESS FULL | T | 999 | 11988 | 7 (0)| 00:00:01 | | |
| 7 | TABLE ACCESS BY USER ROWID| T | 1 | 122 | 1 (0)| 00:00:01 | | |
| 8 | REMOTE | T | 92273 | 10M| 279 (2)| 00:00:01 | NP121 | R->S |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T"."OBJECT_ID"="TGT"."OBJECT_ID")
5 - filter(ROWNUM<1000)
Remote SQL Information (identified by operation id):
----------------------------------------------------
8 - SELECT "OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID","DATA_OBJECT_ID","OBJECT_TYPE","
CREATED","LAST_DDL_TIME","TIMESTAMP","STATUS","TEMPORARY","GENERATED","SECONDARY","NAMESPACE","ED
ITION_NAME","SHARING","EDITIONABLE","ORACLE_MAINTAINED" FROM "T" "TGT" (accessing 'NP121' )
Note
-----
- this is an adaptive plan
select /*+ driving_site(tgt) */ *
from t@np121 tgt,
(SELECT * FROM t WHERE ROWID IN (SELECT rowid FROM t where rownum < 1000)
) src
where src.object_id = tgt.object_id;
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst |IN-OUT|
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT REMOTE| | 999 | 242K| 935 (1)| 00:00:01 | | |
|* 1 | HASH JOIN | | 999 | 242K| 935 (1)| 00:00:01 | | |
|* 2 | HASH JOIN | | 999 | 130K| 503 (1)| 00:00:01 | | |
| 3 | VIEW | VW_NSO_1 | 999 | 11988 | 252 (1)| 00:00:01 | NP121 | |
|* 4 | COUNT STOPKEY | | | | | | | |
| 5 | REMOTE | T | 999 | 11988 | 252 (1)| 00:00:01 | ! | R->S |
| 6 | REMOTE | T | 78131 | 9308K| 252 (1)| 00:00:01 | ! | R->S |
| 7 | TABLE ACCESS FULL | T | 92273 | 10M| 431 (1)| 00:00:01 | NP121 | |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A1"."OBJECT_ID"="A2"."OBJECT_ID")
2 - access("A1".ROWID="$kkqu_col_1")
4 - filter(ROWNUM<1000)
Remote SQL Information (identified by operation id):
----------------------------------------------------
5 - SELECT ROWID FROM "T" "A3" (accessing '!' )
6 - SELECT ROWID,"OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID","DATA_OBJECT_ID","OB
JECT_TYPE","CREATED","LAST_DDL_TIME","TIMESTAMP","STATUS","TEMPORARY","GENERATED","SECONDAR
Y","NAMESPACE","EDITION_NAME","SHARING","EDITIONABLE","ORACLE_MAINTAINED" FROM "T" "A1"
(accessing '!' )
Note
-----
- fully remote statement
36 rows selected.