根据一个表更新另一个表
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的数据是:匹配到的数据-删除的数据
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的数据是:匹配到的数据-删除的数据