Using merge to replace complex update statements

A friend asked me an UPDATE statement, the following two kinds of writing which performance is higher, one is not hint, one is hint

UPDATE cs_performance_current C SET c.performance_score = ' 0.00 ', C.assess_desc = ' discipline assessment ' WHERE c.target_mode_seq_id  = (SELECT d.seq_id from Cs_target_model_rel D, Cs_area A WHERE d.assess_id = 1265 and
           c.target_mode_seq_id = d.seq_id and d.staff_id = 8 and c.com_date = To_date (' 20110801 ', ' YYYYMMDD ')
           and d.target_model_id = 332 and d.state = To_char (2) and d.area_id = a.area_id

and a.area_name = ' Yongxing sub-branches '); Plan Hash value:2321634139-------------------------------------------------------------------------------------- --------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) |
Time |   ----------------------------------------------------------------------------------------------------------
| 0 |                        UPDATE STATEMENT |     |    1 |   41 | 679 (1) |   00:00:09 | |  1 |          UPDATE               |       cs_performance_current |       |            |          |   | |* 2 |                        FILTER |       |       |            |          |   |
|    3 | TABLE ACCESS Full |   cs_performance_current |  124k|   4968k| 664 (1) |    00:00:08 | |* 4 |                        FILTER |       |       |            |          |   |
|     5 |                        NESTED LOOPS |     |    1 |     52 | 3 (0) |      00:00:01 | |* 6 | TABLE ACCESS by INDEX rowid|     Cs_target_model_rel |    1 |     26 | 2 (0) |       00:00:01 | |* 7 | INDEX UNIQUE SCAN |     Pk_cs_target_rule_rel |       1 |     | 1 (0) |      00:00:01 | |* 8 | TABLE ACCESS by INDEX rowid|     Cs_area |    1 |     26 | 1 (0) |       00:00:01 | |* 9 | INDEX UNIQUE SCAN |     Pk_cs_area_new |       1 |     | 0 (0) |
00:00:01 | ----------------------------------------------------------------------------------------------------------predicate information (identified by Operation ID):-------------------- -------------------------------2-filter ("C".) target_mode_seq_id "= (select" D ".")  seq_id "from" Cs_area "" A "," Cs_target_model_rel "" D "where:b1=to_date (' 2011-08-01 00:00:00 ', ' syyyy-mm-dd Hh24:mi:ss ') and "D". " seq_id "=:b2 and" D "." staff_id "=8 and" D "." assess_id "=1265 and" D "." target_model_id "=332 and" D "." State "= ' 2 ' and" D ". area_id "=" A "." area_id "and" A "." Area_name "= ' Yongxing sub-branches ') 4-filter (: B1=to_date (' 2011-08-01 00:00:00 ', ' syyyy-mm-dd hh24:mi:ss ')) 6-filter (" D "). staff_id "=8 and" D "." assess_id "=1265 and" D "." target_model_id "=332 and" D "." State "= ' 2") 7-access ("D".) seq_id "=:B1" 8-filter ("A".) Area_name "= ' Yongxing sub-branches ') 9-access (" D ".) area_id "=" A "." area_id ") Time used: 00:00:00.29 statistics----------------------------------------------------------1 recursive CA LLS 0 db BlockGets 32670 consistent gets physical reads 0 redo size 536 bytes sent via sql*net t O Client 978 bytes received via sql*net from client 3 sql*net roundtrips to/from Client 1 s Orts (memory) 0 sorts (disk) 0 rows processed

UPDATE/*+ PUSH_SUBQ (@D) */cs_performance_current c SET c.performance_score = ' 0.00 ', C.assess_desc = ' discipline assessment ' WHERE C . target_mode_seq_id = (SELECT/*+ qb_name (d) * * d.seq_id from Cs_target_model_rel D, Cs_area A WH ERE d.assess_id = 1265 and c.target_mode_seq_id = d.seq_id and d.staff_id = 8 and C.com_d
           ATE = to_date (' 20110801 ', ' YYYYMMDD ') and d.target_model_id = 332 and d.state = To_char (2) and d.area_id = a.area_id and A.area_name = ' Yongxing sub-branches ') plan hash value:4189652906---------------- ------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) |
Time |   ----------------------------------------------------------------------------------------------------------
| 0 |                        UPDATE STATEMENT |     |    1 |  39 |  43 (0) |   00:00:01 | |  1 | UPDATE |       cs_performance_current |       |            |          |   | |* 2 | TABLE ACCESS Full |     cs_performance_current |    1 |    39 | 40 (0) |    00:00:01 | |* 3 |                        FILTER |       |       |            |          |   |
|     4 |                        NESTED LOOPS |     |    1 |     47 | 3 (0) |      00:00:01 | |* 5 | TABLE ACCESS by INDEX rowid|     Cs_target_model_rel |    1 |     25 | 2 (0) |       00:00:01 | |* 6 | INDEX UNIQUE SCAN |     Pk_cs_target_rule_rel |       1 |     | 1 (0) |      00:00:01 | |* 7 | TABLE ACCESS by INDEX rowid|     Cs_area |    1 |     22 | 1 (0) |       00:00:01 | |* 8 | INDEX UNIQUE SCAN |     Pk_cs_area_new |       1 |     | 0 (0) |
00:00:01 |  ----------------------------------------------------------------------------------------------------------predicate InFormation (identified by Operation ID):---------------------------------------------------2-filter ("C".) target_mode_seq_id "= (select/*+ push_subq qb_name (" D ")/" D ".  
              seq_id "from" Cs_area "" A "," Cs_target_model_rel "" D "where:b1=to_date (' 2011-08-01 00:00:00 ', ' syyyy-mm-dd Hh24:mi:ss ') and "D". " seq_id "=:b2 and" D "." assess_id "=1265 and" D "." staff_id "=8 and" D "." target_model_id "=332 and" D "." State "= ' 2 ' and" D ". area_id "=" A "." area_id "and" A "." Area_name "= ' Yongxing sub-branches ') 3-filter (: B1=to_date (' 2011-08-01 00:00:00 ', ' syyyy-mm-dd hh24:mi:ss ')) 5-filter (" D "). assess_id "=1265 and" D "." staff_id "=8 and" D "." target_model_id "=332 and" D "." State "= ' 2") 6-access ("D".) seq_id "=:B1" 7-filter ("A".) Area_name "= ' Yongxing sub-branches ') 8-access (" D ".) area_id "=" A "." AREA_ID ") Statistical information----------------------------------------------------------1 recursive calls 0 db bl
 Ock gets 32670 consistent gets        Physical reads 0 redo size 536 bytes sent via sql*net to client 978 bytes receive D via sql*net from client 3 sql*net roundtrips to/from client 1 sorts (memory) 0 sorts (d ISK) 0 rows processed

As you can see, logical reads are all the same, so this SQL, plus hint performance is the same. In fact, for the UPDATE statement in Oracle, if the SQL is complex, you need to correlate multiple tables to do update, this time you should use

The merge statement replaces update and is overwritten with the merge:

MERGE into Cs_performance_current A USING (SELECT d.seq_id from Cs_target_model_rel D, Cs_area
              M WHERE d.assess_id = 1265 and d.staff_id = 8 and d.target_model_id = 332 and D.state = To_char (2) and d.area_id = m.area_id and M.area_name = ' Yongxing sub-branches ') H on (A .  target_mode_seq_id = h.seq_id) when matched THEN UPDATE SET a.performance_score = ' 0.00 ', A.assess_desc = ' Labor discipline Assessment ' WHERE A.com_date = to_date (' 20110801 ', ' YYYYMMDD ');-------------------------------------------------------------------- --------------------------------------
 | Id | Operation | Name | Rows | Bytes | Cost (%CPU) |
 Time |   ----------------------------------------------------------------------------------------------------------
 | 0 |                        MERGE STATEMENT |     |   1 |   167 | 750 (1) |
 00:00:10 |   |  1 |               MERGE           |       cs_performance_current |       |            |          |
 |   |   2 |                        VIEW |       |       |            |          |
 |    |* 3 |                        HASH JOIN |     |   1 |   210 | 750 (1) |
 00:00:10 |   |     4 |                        NESTED LOOPS |     |    1 |    52 | 85 (2) |
 00:00:02 |      |* 5 | TABLE ACCESS Full |     Cs_target_model_rel |    1 |    26 | 84 (2) |
 00:00:02 |      |* 6 | TABLE ACCESS by INDEX rowid|     Cs_area |    1 |     26 | 1 (0) |
 00:00:01 |       |* 7 | INDEX UNIQUE SCAN |     Pk_cs_area_new |       1 |     | 0 (0) |
 00:00:01 |   |     8 | TABLE ACCESS Full |   cs_performance_current |    124k|   18m| 664 (1) |
 00:00:08 |  ----------------------------------------------------------------------------------------------------------predicate Information (identified by Operation ID):---------------------------------------------------3-access ("A".) target_mode_seq_id "=" D "." seq_id ") 5-filter (" D "). staff_id "=8 and" D "." assess_id "=1265 and" D "." target_model_id "=332 and" D "." State "= ' 2") 6-filter ("M".) Area_name "= ' Yongxing sub-branches ') 7-access (" D ".) area_id "=" M "." AREA_ID ") statistical information----------------------------------------------------------333 recursive cal  LS 0 db block gets 3474 consistent gets 3 physical reads 0 redo size 546 Bytes sent via sql*net to client 998 bytes received via sql*net from client 3 sql*net roundtrips           ROM client sorts (memory) 0 sorts (disk) 0 rows processed

Logical reading dropped nearly 10 times times, where cs_performance_current is a full table scan, it has 100W of data, so the index is indexed on the target_mode_seq_id column

Plan Hash value:3908453434-------------------------------------------------------------------------------------- ---------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) |
Time |   -----------------------------------------------------------------------------------------------------------
| 0 |                        MERGE STATEMENT |     |   1 |    167 | 88 (2) |   00:00:02 | |  1 | MERGE |       cs_performance_current |       |            |          |   |
|   2 |                        VIEW |       |       |            |          |   |
|    3 | TABLE ACCESS by INDEX ROWID |     cs_performance_current |   1 |     158 | 3 (0) |   00:00:01 | |     4 |                        NESTED LOOPS |     |   1 |    210 | 88 (2) |   00:00:02 | |      5 |                        NESTED LOOPS |     |    1 |    52 | 85 (2) | 00:00:02| |* 6 | TABLE ACCESS Full |     Cs_target_model_rel |    1 |    26 | 84 (2) |       00:00:02 | |* 7 | TABLE ACCESS by INDEX rowid|     Cs_area |    1 |     26 | 1 (0) |        00:00:01 | |* 8 | INDEX UNIQUE SCAN |     Pk_cs_area_new |       1 |     | 0 (0) |      00:00:01 | |* 9 | INDEX RANGE SCAN |     idx_cs_pfc_current |       1 |     | 1 (0) |
00:00:01 |  -----------------------------------------------------------------------------------------------------------predicate Information (identified by Operation ID):---------------------------------------------------6-filter ("D".) staff_id "=8 and" D "." assess_id "=1265 and" D "." target_model_id "=332 and" D "." State "= ' 2") 7-filter ("M".) Area_name "= ' Yongxing sub-branches ') 8-access (" D ".) area_id "=" M "." area_id ") 9-access (" A "). target_mode_seq_id "=" D "."
       seq_id ") Time used: 00:00:00.18 statistic information----------------------------------------------------------   0 Recursive calls 0 DB block gets 386 consistent gets 0 physical reads 0 red O Size 544 Bytes sent via sql*net to client 998 bytes via received from client 3 sql*net Net roundtrips to/from Client 1 sorts (memory) 0 sorts (disk) 0 rows processed

Now logical reading down to 386

Among Oracle, it is strongly recommended = use merge instead of UPDATE statements