MERGE和关联删除和关联更新
Merge是根据某个row source而有条件地选择性insert/delete/update目的表。但是它通过1次全表扫描（假设执行计划是全表扫描访问）就能够实现3次全表扫描才能完成的任务，因此提高了SQL执行的性能。由于它既然是insert/delete/update的合体，为了更清楚地了解原理，我们应该也能够将它拆解成关联更新、关联删除和普通插入：创建实验表：
create table t071se09_a( id number , a varchar2(20));
create table t071se09_b( id number , a varchar2(20));
insert intot071se09_a values ( 1, 'A');
insert intot071se09_a values ( 2, 'B');
insert intot071se09_b values ( 1, 'AA');
insert intot071se09_b values ( 2, 'BB');
insert intot071se09_b values ( 100, 'XX');
commit;查看表的内容：
select* from t071se09_a;1 A
2 B
select* from t071se09_b;1 AA
2 BB
100 XX
执行以下的merge（以t071se09_b为标准更新t071se09_a）：
MERGE INTO t071se09_a t
USING t071se09_b c
ON (t.id = c.id)
WHEN MATCHED THEN
UPDATE SET
t.a = c.a
DELETE WHERE ( c.id=2)
WHEN NOT MATCHED THEN
INSERT VALUES ( c.id, c.a);
commit;3 rows merged.
select* from t071se09_a;1 AA
100 XX
b表作为标准，不会变化：
select* from t071se09_b;1 AA
2 BB
100 XX
让我们用“关联更新、关联删除和普通插入”重新实现一遍上面的效果：
truncate table t071se09_a;
insert intot071se09_a values ( 1, 'A');
insert intot071se09_a values ( 2, 'B');
commit;查看表的内容：
select* from t071se09_a;
1 A
2 B
普通插入：
insert into t071se09_a select * from t071se09_b
where id not in ( selectid from t071se09_a );
1 rows inserted.
关联更新：
update t071se09_a t set t.a=( selecta from t071se09_b c where t.id=c.id );3 rows updated.
关联删除：
delete from t071se09_a t where exists ( select 'X' from t071se09_b c
where t.id=c.idand c.id=2) ;1 rows deleted.
a表的结果与之前merge的结果一样：
select* from t071se09_a;
1 AA
100 XX
页:
[1]