merge的用法和优点

根据一个表更新另一个表


alter table emp add dname varchar2(50) default 'noname';

更新语句是：

explain plan for
update emp set emp.dname=(select dept.dname from dept where dept.deptno=emp.deptno and dept.dname in ('ACCOUNTING','RESEARCH'))
where exists (select dept.dname from dept where dept.deptno=emp.deptno and dept.dname in ('ACCOUNTING','RESEARCH'));


select * from table(dbms_xplan.display);


Plan hash value: 961813568


----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | UPDATE STATEMENT    |      |     9 |   558 |    43  (24)| 00:00:01 |
|   1 |  UPDATE             | EMP  |       |       |            |          |
|*  2 |   HASH JOIN SEMI    |      |     9 |   558 |     7  (15)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| EMP  |    14 |   560 |     3   (0)| 00:00:01 |
|*  4 |    TABLE ACCESS FULL| DEPT |     2 |    44 |     3   (0)| 00:00:01 |
|*  5 |   TABLE ACCESS FULL | DEPT |     1 |    22 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   2 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")
   4 - filter("DEPT"."DNAME"='ACCOUNTING' OR "DEPT"."DNAME"='RESEARCH')
   5 - filter("DEPT"."DEPTNO"=:B1 AND ("DEPT"."DNAME"='ACCOUNTING' OR
              "DEPT"."DNAME"='RESEARCH'))


访问了两次dept表。


explain plan for
merge into emp using (select dname,deptno from dept where dept.dname in ('ACCOUNTING','RESEARCH')) dept
on (dept.deptno=emp.deptno)
when matched then update set emp.dname=dept.dname;


select * from table(dbms_xplan.display);




Plan hash value: 710185837


-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | MERGE STATEMENT      |      |     9 |   324 |     7  (15)| 00:00:01 |
|   1 |  MERGE               | EMP  |       |       |            |          |
|   2 |   VIEW               |      |       |       |            |          |
|*  3 |    HASH JOIN         |      |     9 |  1332 |     7  (15)| 00:00:01 |
|*  4 |     TABLE ACCESS FULL| DEPT |     2 |    44 |     3   (0)| 00:00:01 |
|   5 |     TABLE ACCESS FULL| EMP  |    14 |  1764 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   3 - access("DEPTNO"="EMP"."DEPTNO")
   4 - filter("DEPT"."DNAME"='ACCOUNTING' OR "DEPT"."DNAME"='RESEARCH')


只扫描了一次dept表。




语法：


merge into tmp1 a
using tmp2 b
on (a.id=b.id)
when matched then update set a.sal=b.sal+100 
delete where (a.sid>10)
when not matched then insert (a.id,a.sal,a.sid) values (b.id,b.sal,b.sid) where (a.sal<100);




注意：
merge into tmp1，只能更改tmp1的数据，不能更改using表的数据；
merge into不能更新join列
当有delete语句时，update后面不能有where条件，这是update的数据是：匹配到的数据-删除的数据