Oracle中update语句的优化学习
1.语法:
单表:UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值
update DB_OG_MINING_ITEM t
set (t.id_applyid,
t.id_licenceid) =
(select t1.id_applyid, t1.id_licenceid from db_og_mining_arch t1 where t.id_receiveid=t1.id_receiveid)
where exists(select 1 from db_og_mining_arch t1 where t1.id_receiveid=t.id_receiveid)
set (t.id_applyid,
t.id_licenceid) =
(select t1.id_applyid, t1.id_licenceid from db_og_mining_arch t1 where t.id_receiveid=t1.id_receiveid)
where exists(select 1 from db_og_mining_arch t1 where t1.id_receiveid=t.id_receiveid)
关联表更新时一定要有exists(select 1 from 表b where a.字段2=b.字段2)这样的条件,否则将表a的其他数据的字段1更新为null值。
2.提高更新效率的2种解决方案:
inline view更新法:指更新一个临时建立的视图。
update (select t.id_applyid id_applyid_t, t.id_licenceid id_licenceid_t, t1.id_applyid id_applyid_t1, t1.id_licenceid id_licenceid_t1
from DB_OG_MINING_ITEM t, db_og_mining_arch t1 where t.id_receiveid=t1.id_receiveid)
set id_applyid_t=id_applyid_t1,id_licenceid_t=id_licenceid_t1
from DB_OG_MINING_ITEM t, db_og_mining_arch t1 where t.id_receiveid=t1.id_receiveid)
set id_applyid_t=id_applyid_t1,id_licenceid_t=id_licenceid_t1
快速游标更新法:oracle支持快速游标,不需要定义直接把游标写到for循环中,这样就方便了我们批量更新数据。再加上oracle的rowid物理字段(oracle默认给每个表都有rowid这个字段,并且是唯一索引),可以快速定位到要更新的记录上。
begin
for cr in (select t.rowid,t1.id_applyid,t1.id_licenceid from DB_OG_MINING_ITEM t,db_og_mining_arch t1 where t.id_receiveid=t1.id_receiveid) loop
update DB_OG_MINING_ITEM set id_applyid=cr.id_applyid,id_licenceid=cr.id_licenceid where rowid=cr.rowid;
end loop;
end;
for cr in (select t.rowid,t1.id_applyid,t1.id_licenceid from DB_OG_MINING_ITEM t,db_og_mining_arch t1 where t.id_receiveid=t1.id_receiveid) loop
update DB_OG_MINING_ITEM set id_applyid=cr.id_applyid,id_licenceid=cr.id_licenceid where rowid=cr.rowid;
end loop;
end;