MERGE STATEMENT (REMOTE)

Description

Merge statement executed on a remote database

Versions

This operation is implemented in the following versions

9.0.1
9.2.0
10.1.0
10.2.0

Example

This example was developed using Oracle 9.2.0.1 on Windows 2000

This example requires the following table definition 

    CREATE TABLE t1 (c1 NUMBER,c2 NUMBER);
    CREATE TABLE t2 (c1 NUMBER,c2 NUMBER);

    MERGE INTO t2@database@link a2
    USING
    (  
      SELECT * FROM t1
    ) a1
    ON (a2.c1 = a1.c1)
    WHEN MATCHED THEN
      UPDATE SET a2.c2 = a1.c2
    WHEN NOT MATCHED THEN
      INSERT (a2.c1,a2.c2)
      VALUES (a1.c1,a2.c2);

In Oracle 9.2 and Oracle 10.2 this statement generates the following execution plan

0     MERGE STATEMENT (REMOTE) Optimizer=CHOOSE
1   0   MERGE OF 'T2'
2   1     VIEW
3   2       HASH JOIN (OUTER)
4   3         VIEW
5   4           REMOTE
6   3         TABLE ACCESS (FULL) OF 'T2'