Merge operation
This example was developed in Oracle 9.0.1 and retested in Oracle 9.2.0
This operation is implemented in the following versions
|
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.0 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'