MERGE STATEMENT

Description

Merge statement

Versions

This example was developed in Oracle 9.0.1 and retested in Oracle 9.2.0

This operation is implemented in the following versions

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 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.0.1 this statement generates the following execution plan

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

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

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