视频链接地址:http://www.tudou.com/programs/view/GUnRTkZFqFk/
内容:批量更新优化方法比对
--使用场景:日常运维时常会出现根据一个表的内容去更新另一个表内容;
--涉及方法:1.普通游标 2.批量绑定游标 3.merge
构造数据:
create table t1 as select * from dba_objects where object_id is not null;
create table t2 as select * from dba_objects where object_id is not null;
1.普通游标
begin
for c1 in (select object_id,object_name from t2) loop
update t1 set t1.object_name=c1.object_name
where t1.object_id=c1.object_id;
end loop;
end;
/
2.批量绑定游标
declare
c_nt number;
cursor c1 is select object_id,object_name from t2;
type t_t1 is table of c1%rowtype;
v_t1 t_t1;
begin
c_nt :=0;
open c1;
loop
fetch c1 bulk collect into v_t1 limit 5000;
forall idx in 1..v_t1.count
update t1 set t1.object_name=v_t1(idx).object_name
where t1.object_id=v_t1(idx).object_id;
if c_nt=5000 then
commit;
end if;
c_nt :=c_nt+1;
exit when c1%NOTFOUND;
end loop;
rollback;
close c1;
end;
/
3.merge使用
merge into t1
using t2
on (t1.object_id=t2.object_id)
when matched then
update set t1.object_name=t2.object_name;
merge into t1
using t3
on (t1.object_id=t3.object_id)
when matched then
update set t1.object_name=t3.object_name;
4.注意几个问题
(1) merge索引使用
(2) 批量绑定更新limit数量大小影响
5.小结:
merge:快捷,但不够灵活,速度较快
批量绑定游标:较灵活,速度较快
普通游标:较慢,较灵活
merge into t1
using t3
on (t1.object_id=t3.object_id)
when matched then
update set t1.object_name=t3.object_name;
update t2 set object_name='ZBK' where rownum<11;
select count(1) from t1 where object_name='ZBK';
新建QQ交流群:309241836
需回答问题"11G稳定执行计划工具叫什么?"
要求:一定基础