autotrace merge command : AUTOTRACE « SQL PLUS Session Environment « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. SQL PLUS Session Environment
3. AUTOTRACE

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>