autotrace merge command : autotrace « SQL Plus « Oracle PL / SQL

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>

1.	autotrace command
2.	Autotrace on and off
3.	Autotrace lower text function
4.	Autotrace running total
5.	Autotrace a query on a huge table
6.	Autotrace a large table
7.	Autotrace a query with group clause
8.	Execution Plan
9.	autotrace a nested query
10.	set autotrace on explain for every single statement
11.	set autotrace traceonly explain for bitmap index
12.	set autotrace traceonly explain, and condition
13.	set autotrace traceonly statistics
14.	set autotrace traceonly statistics for 'select * from tableName'
15.	set autotrace traceonly
16.	AUTOTRACE exists (subquery)
17.	AUTOTRACE table joining
18.	AUTOTRACE table joining and aggregate function
19.	autotrace ansi full outer join
20.	autotrace count(*)
21.	autotrace ctxsys.context index
22.	autotrace table with/without an index

autotrace merge command : autotrace « SQL Plus « Oracle PL / SQL

Related examples in the same category