autotrace merge command
SQL> create table myTable as select * from dba_objects;
Table created.
SQL> delete from myTable where rownum <= 100;
100 rows deleted.
SQL> create table myTable2 as select * from dba_objects;
Table created.
SQL> set autotrace on
SQL> merge into myTable b
2 using myTable2 o
3 on (b.owner = o.owner and b.object_name = o.object_name
4 and
5 b.subobject_name = o.subobject_name
6 and
7 b.object_id = o.object_id)
8 when matched then update set b.created = o.created
9 when not matched then insert
10 values ( o.OWNER ,o.OBJECT_NAME ,o.SUBOBJECT_NAME ,o.OBJECT_ID ,o.DATA_OBJECT_ID
11 ,o.OBJECT_TYPE ,o.CREATED ,o.LAST_DDL_TIME,o.TIMESTAMP ,o.STATUS,o.TEMPORARY,o.GENERATED
12 ,o.SECONDARY )
13 /
13219 rows merged.
Execution Plan
----------------------------------------------------------
Plan hash value: 449939568
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost |
-------------------------------------------------------------------------
| 0 | MERGE STATEMENT | | 13723 | 3832K| | 99 |
| 1 | MERGE | MYTABLE | | | | |
| 2 | VIEW | | | | | |
|* 3 | HASH JOIN OUTER | | 13723 | 2680K| 1504K| 99 |
| 4 | TABLE ACCESS FULL| MYTABLE2 | 13723 | 1340K| | 26 |
| 5 | TABLE ACCESS FULL| MYTABLE | 13723 | 1340K| | 26 |
-------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("B"."OBJECT_ID"(+)="O"."OBJECT_ID" AND
"B"."SUBOBJECT_NAME"(+)="O"."SUBOBJECT_NAME" AND
"B"."OBJECT_NAME"(+)="O"."OBJECT_NAME" AND "B"."OWNER"(+)="O"."OWN
ER")
Note
-----
- cpu costing is off (consider enabling it)
Statistics
----------------------------------------------------------
253 recursive calls
14050 db block gets
388 consistent gets
329 physical reads
4644528 redo size
929 bytes sent via SQL*Net to client
1374 bytes received via SQL*Net from client
6 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
13219 rows processed
SQL>
SQL> set autotrace off
SQL> rollback;
Rollback complete.
SQL> set autotrace on
SQL>
SQL> merge into myTable b
2 using (select * from myTable2) o
3 on (b.owner = o.owner and b.object_name = o.object_name
4 and
5 b.subobject_name = o.subobject_name
6 and
7 b.object_id = o.object_id)
8 when matched then update set b.created = o.created
9 when not matched then insert
10 values ( o.OWNER ,o.OBJECT_NAME ,o.SUBOBJECT_NAME ,o.OBJECT_ID ,o.DATA_OBJECT_ID
11 ,o.OBJECT_TYPE ,o.CREATED ,o.LAST_DDL_TIME,o.TIMESTAMP ,o.STATUS,o.TEMPORARY,o.GENERATED
12 ,o.SECONDARY )
13 /
13219 rows merged.
Execution Plan
----------------------------------------------------------
Plan hash value: 449939568
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost |
-------------------------------------------------------------------------
| 0 | MERGE STATEMENT | | 13723 | 3832K| | 99 |
| 1 | MERGE | MYTABLE | | | | |
| 2 | VIEW | | | | | |
|* 3 | HASH JOIN OUTER | | 13723 | 2680K| 1504K| 99 |
| 4 | TABLE ACCESS FULL| MYTABLE2 | 13723 | 1340K| | 26 |
| 5 | TABLE ACCESS FULL| MYTABLE | 13723 | 1340K| | 26 |
-------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("B"."OBJECT_ID"(+)="MYTABLE2"."OBJECT_ID" AND
"B"."SUBOBJECT_NAME"(+)="MYTABLE2"."SUBOBJECT_NAME" AND
"B"."OBJECT_NAME"(+)="MYTABLE2"."OBJECT_NAME" AND
"B"."OWNER"(+)="MYTABLE2"."OWNER")
Note
-----
- cpu costing is off (consider enabling it)
Statistics
----------------------------------------------------------
4 recursive calls
13763 db block gets
678 consistent gets
0 physical reads
4614184 redo size
929 bytes sent via SQL*Net to client
1390 bytes received via SQL*Net from client
6 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
13219 rows processed
SQL>
SQL> set autotrace off
SQL> rollback;
Rollback complete.
SQL>
SQL> drop table myTable;
Table dropped.
SQL> drop table myTable2;
Table dropped.
SQL>
SQL>
SQL>