关于UPDATE级MERGE关联表进行UPDATE的说明

1、建立测试表 
SQL> select * from test1; 
ID NAME 
----------- -------------------- 
1 gaopeng 
2 gaopeng1 
3 gaopeng2 
4 gaopeng3 

SQL> select * from test2; 
ID NAME 
----------- -------------------- 
1 yanlei 
2 yanlei1 
2、目标 根据ID进行更改将TEST1中的name字段更改为TEST2中的NAME字段 
3、写法 
---UPDATE错误写法 
update test1 t1 set name=(select name from test2 t2 where t1.id=t2.id) ; 
结果为 
SQL> update test1 t1 set name=(select name from test2 t2 where t1.id=t2.id) ; 
4 rows updated 

SQL> select * from test1; 
ID NAME 
----------- -------------------- 
1 yanlei 
2 yanlei1 
3 
4 
可以看到TEST1中ID不与TEST2中匹配的行赋予了正确的值，不匹配的行赋予了空值，这不是我们需要的。 
---UPDATE正确的写法 
SQL> update test1 t1 set name=(select name from test2 t2 where t1.id=t2.id) 
2 where exists (select 1 
3 from test2 t2 
4 where t1.id=t2.id 
5 ) 
6 ; 
2 rows updated 

SQL> select * from test1; 
ID NAME 
----------- -------------------- 
1 yanlei 
2 yanlei1 
3 gaopeng2 
4 gaopeng3 
可以看到这种写法达到了我们的要求，因为首先通过EXISTS语句过滤掉了不匹配的行，如果需要对TEST1表进行WHERE 条件限制可以如下： 
update test1 t1 set name=(select name from test2 t2 where t1.id=t2.id) 
where exists (select 1 
from test2 t2 
where t1.id=t2.id 
) 
and t1.id=1; 


---MERGE正确写法 
SQL> merge into test1 t1 
2 using 
3 test2 t2 
4 on(t1.id=t2.id) 
5 when matched then 
6 update 
7 set t1.name=t2.name 
8 ; 
2 rows merged 


SQL> select * from test1 order by id; 
ID NAME 
----------- -------------------- 
1 yanlei 
2 yanlei1 
3 gaopeng2 
4 gaopeng3 

可以看到这种写法也达到了我们要的求。如果需要对TEST1表进行限制条件可以 

merge into test1 t1 
using 
test2 t2 
on(t1.id=t2.id) 
when matched then 
update 
set t1.name=t2.name 
where t1.id=2; 


注意：对于如上的更改的要求TEST2表中ID列必须唯一键 
如果不唯一都会报错 
在TEST2表中增加一行 
SQL> insert into test2 values(1,'yanlei1'); 
1 row inserted 

SQL> commit; 
Commit complete 

SQL> select * from test2; 
ID NAME 
----------- -------------------- 
2 yanlei1 
1 yanlei 
1 yanlei1 


update 会报错 
update test1 t1 set name=(select name from test2 t2 where t1.id=t2.id) 
where exists (select 1 
from test2 t2 
where t1.id=t2.id 
) 
ORA-01427: single-row subquery returns more than one row 
merger 会报错 
ORA-30926: unable to get a stable set of rows in the source tables 

对于执行性能而言： 
MERGE 可以对执行计划进行控制，更改其连接方式提高性能，但是UPDATE首先会做WHERE条件后的子查询，通过关联子查询过滤出相应的行数（关联子查询由外层表驱动内层表 
换句话说外层表有多少行就会驱动多少次，虽然ORACLE做了SEMI ANTI等半连接但性能还是大数据量的情况下任然很慢），然后通过查询出来的行数和 
更改表做连接进行更改数据(次连接为NEST LOOP类型），不仅多一个步骤而且无法通过更改连接方式进行提高性能。 

UPDATE执行计划： 
---------------------------------------------------------------------------------------
| Id  | Operation           | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT    |       |      1 |        |      0 |00:00:00.01 |      32 |
|   1 |  UPDATE             | TEST1 |      1 |        |      0 |00:00:00.01 |      32 |                  --第二次驱动
|*  2 |   HASH JOIN SEMI    |       |      1 |      2 |      2 |00:00:00.01 |      14 |                --第一次驱动
|   3 |    TABLE ACCESS FULL| TEST1 |      1 |      4 |      4 |00:00:00.01 |       7 |
|   4 |    TABLE ACCESS FULL| TEST2 |      1 |      2 |      2 |00:00:00.01 |       7 |
|*  5 |   TABLE ACCESS FULL | TEST2 |      2 |      1 |      2 |00:00:00.01 |      14 |
---------------------------------------------------------------------------------------

MERGE执行计划 

PLAN_TABLE_OUTPUT 
-------------------------------------------------------------------------------- 
Plan hash value: 520388833 
------------------------------------------------------------------------------ 
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 
------------------------------------------------------------------------------ 
| 0 | MERGE STATEMENT | | 2 | 100 | 7 (15)| 00:00:01 | 
| 1 | MERGE | TEST1 | | | | | 
| 2 | VIEW | | | | | | 
|* 3 | HASH JOIN | | 2 | 124 | 7 (15)| 00:00:01 | --仅驱动一次，这一步是可以使用HINT更改连接方式的 
| 4 | TABLE ACCESS FULL| TEST2 | 2 | 50 | 3 (0)| 00:00:01 | 
| 5 | TABLE ACCESS FULL| TEST1 | 4 | 148 | 3 (0)| 00:00:01 | 
------------------------------------------------------------------------------

谢谢！

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7728585/viewspace-1448374/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7728585/viewspace-1448374/