Mega Code Archive

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>