Use merge into for Performance Optimization

Using merge into for performance optimization sometimes the development team has such a requirement that a table and its backup table replace some fields in the backup table with the original table, when the data size is very large, it will be very slow. If we use merge into, it will often improve the performance several times. Let's do an experiment: SQL> drop table test1 purge; the table has been deleted. SQL> drop table test2 purge; the table has been deleted. SQL> create table test1 as select * from dba_objects; the table has been created. SQL> alter table test1 nologging; the table has been changed. SQL> begin 2 for I in 1 .. 5 loop 3 insert/* + append */4 into test1 5 select * from dba_objects; 6 commit; 7 end loop; 8 end; 9/PL/SQL process completed successfully. SQL> update test1 set object_id = rownum; row 303258 has been updated. SQL> commit; submitted completely. SQL> create table test2 as select * from test1; the table has been created. SQL> select count (*) from test1; COUNT (*) ---------- 303258 SQL> select count (*) from test2; COUNT (*) ---------- 303258 SQL> create index ind_object_id1 on test1 (object_id) nologging; the index has been created. SQL> create index ind_object_id2 on test2 (object_id) nologging; the index has been created. SQL> exec dbms_stats.gather_table_stats (user, 'test1'); PL/SQL process completed successfully. SQL> exec dbms_stats.gather_table_stats (user, 'test2'); PL/SQL process completed successfully. SQL> set timing onSQL> set autotrace traceonlySQL> update test1 t1 2 set t1.object _ type = (select object_type 3 from test2 t2 4 where t1.object _ id = t2.object _ id ); 303258 rows have been updated. Used time: 00: 00: 13.07 execution Plan -------------------------------------------------------- Plan hash value: 2560893763 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | increment | 0 | update statement | 303K | 4146K | 949 (2) | 00:00:12 | 1 | UPDATE | TEST1 | 2 | table access full | TEST1 | 303K | 4146K | 949 (2) | 00:00:12 | 3 | table access by index rowid | TEST2 | 1 | 14 | 4 (0) | 00:00:01 | * 4 | index range scan | IND_OBJECT_ID2 | 1 | 3 (0) | 00:00:01 | identified Predicate Information (identified by operation id ):--- ---------------------------------------------- 4-access ("T2 ". "OBJECT_ID" =: B1) statistics 330 recursive cballs 338515 db block gets 1250542 consistent gets 1 physical reads 107333692 redo size 673 bytes sent via SQL * Net to client 701 bytes encoded ed via SQL * Net from client 4 SQL * net roundtrips to/from client 1 sorts (memory) 0 sorts (disks) 30325 8 rows processed SQL> commit; the submission is complete. Used time: 00: 00: 00.00SQL> merge into test1 t1 2 using test2 t2 3 on (t1.object _ id = t2.object _ id) 4 when matched then 5 update set t1.object _ type = t2.object _ type; 303258 rows have been merged. Used time: 00: 00: 03.87 execution Plan ------------------------------------------------------ Plan hash value: 520388833 Bytes | Id | Operation | Name | Rows | Bytes | TempSpc | Cost (% CPU) | Time | increment | 0 | merge statement | 303K | 5923K | 4947 (2) | 00:01:00 | 1 | MERGE | TEST1 | 2 | VIEW | * 3 | hash join | 303K | 53M | 30M | 4947 (2) | 00:01:00 | 4 | table access full | TEST2 | 303K | 26M | 957 (3) | 00:00:12 | 5 | table access full | TEST1 | 303K | 26M | 957 (3) | 00:00:12 | identified Predicate Information (identified by operation id ):---------- ----------------------------------------- 3-access ("T1 ". "OBJECT_ID" = "T2 ". "OBJECT_ID ") statistics 378 recursive cballs 310584 db block gets 8547 consistent gets 3751 physical reads 76712320 redo size 678 bytes sent via SQL * Net to client 671 bytes encoded ed via SQL * Net from client 4 SQL * net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 303258 rows processed SQL> commit; the submission is complete.