Oracle中update语句的优化学习
来源:互联网 发布:万里独行知马力 编辑:程序博客网 时间:2024/05/01 01:31
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;
阅读全文
0 0
- Oracle中update语句的优化学习
- oracle中update语句的性能优化
- Oracle的update语句优化
- oracle的update语句优化
- Oracle的update语句优化研究
- Oracle的update语句优化研究
- Oracle的update语句优化研究
- Oracle的update语句优化研究
- Oracle的update语句优化研究
- Oracle的update语句优化研究
- Oracle的update语句优化研究
- Oracle的update语句优化研究 批量更新
- oracle 10g update的语法 更名成 Oracle的update语句优化研究
- Oracle-----Update语句优化之merge into
- oracle 的 update 语句实例
- Oracle的For Update语句
- Oracle的update语句研究
- Oracle中执行update语句时卡住的解决方法
- 设计模式-行为模式之Chain-Of-Responsibility
- java接口与多态
- GPS基本解析思路
- Matplotlib入门教程
- PCB中电流如何流动——电流如何沿传输线流动
- Oracle中update语句的优化学习
- Uncaught DOMException: Failed to execute 'insertBefore' on 'Node': The node before which the new nod
- BLE Link Layer学习笔记
- IM即时通讯实现原理
- [转]HUSTOJ管理问题
- macOS terminal setup to boost your productivity
- Android逆向之旅---Android中某视频的数据请求加密协议(IDA动态调试SO)第一篇
- SpringAOP基本概念及实现原理(一)
- poj1125