一个远程引发的Oracle Update思考

教程发布:风哥 教程分类:ITPUX技术网 更新日期:2022-02-12 浏览学习:27

[font=Tahoma,][size=26px]一个远程引发的Oracle Update思考[font=Tahoma,]今天远程帮一同事研究了一个update语句,同事原本的语句这样写的:
UPDATE PAYSYSTEM_2
SET C21_c21_ysgz=(select nvl(c21.c21_yfgz,0)-nvl(c21.c2120,0)-nvl(c21.c211c,0)-nvl(c21.c211e,0)-nvl(c21.c211f,0)-vl(c21.c2131,0)
from c21 where gz_ym=C21_c21e7)
了解了需求后,知道同事要的效果是, 用上个月的各项工资算出来本月的应税工资即C21_c21_ysgz,PAYSYSTEM_2是本月工资的临时表,C21_c21e7是上月月份。
很显然, PAYSYSTEM_2,c21没有关联,没有一对一,会报返回值返回多个的错误。
如果是sql server很简单:
UPDATE PAYSYSTEM_2
SET C21_c21_ysgz= a.s (select nvl(c21.c21_yfgz,0)-nvl(c21.c2120,0)-nvl(c21.c211c,0)-nvl(c21.c211e,0)-nvl(c21.c211f,0)-vl(c21.c2131,0) as s
from c21 where gz_ym=C21_c21e7) a,PAYSYSTEM_2
where a.a01_key= PAYSYSTEM_2.a01_key
就ok了,但是oracle 没有update from语法。
oracle 则要如下:
主要方式有4种
1、子查询方式
UPDATE PAYSYSTEM_2 p
SET p.C21_c21_ysgz=
(select nvl(c21.c21_yfgz,0)-nvl(c21.c2120,0)-nvl(c21.c211c,0)-nvl(c21.c211e,0)-nvl(c21.c211f,0)-vl(c21.c2131,0)
from c21 where p.a01_key=c21.a01_key
and gz_ym=C21_c21e7);

UPDATE PAYSYSTEM_2 p
SET p.C21_c21_ysgz=
(select nvl(c21.c21_yfgz,0)-nvl(c21.c2120,0)-nvl(c21.c211c,0)-nvl(c21.c211e,0)-nvl(c21.c211f,0)-vl(c21.c2131,0)
from c21 where gz_ym=C21.c21e7
and p.a01_key=c21.a01_key)
where exists(select 1 from c21
where p.a01_key=c21.a01_key
and gz_ym=C21_c21e7 );
以上2种写法区别就是:
第二条,更无严谨,不满足条件的不去动;
第一条,不满足条件的会被替换成null;
这里不满足条件指的是子查询里有,被更新表里没有。
根据业务可以知道,初始表为空,所以它们都能实现业务需求,我们当然选择第一条,因为它只有一个子查询,只需查询一次c21表,效率会好些。
tips:先过滤,再关联,会缩小结果集,所以where后的条件不要搞反。

2、游标更新
begin
cr in (select nvl(c21.c21_yfgz,0)-nvl(c21.c2120,0)-nvl(c21.c211c,0)-nvl(c21.c211e,0)-nvl(c21.c211f,0)-vl(c21.c2131,0) as a,a01_key
from c21 where gz_ym=C21_c21e7)
loop
update PAYSYSTEM_2 p set p.C21_c21_ysgz=cr.a
where cr.a01_key=p.a01_key;
end loop;
end
此种方式充分利用了PAYSYSTEM_2这张临时表特性,效率比子查询要好些。

3、inline view更新
update
(select select nvl(c21.c21_yfgz,0)-nvl(c21.c2120,0)-nvl(c21.c211c,0)-nvl(c21.c211e,0)-nvl(c21.c211f,0)-vl(c21.c2131,0) as a,p.C21_c21_ysgz b
from c21,PAYSYSTEM_2 p where p.a01_key=c21.a01_key and c21.gz_ym=c21.C21_c21e7)
set b=a
如果视图基于多个表的连接,那么用户更新视图记录的能力将受到限制。除非update只涉及一个表且视图列中包含了被更新的表的整个主键,否则不能更新视图的基表。

4、merge更新法
merge into PAYSYSTEM_2 p
using (select nvl(c21.c21_yfgz,0)-nvl(c21.c2120,0)-nvl(c21.c211c,0)-nvl(c21.c211e,0)-nvl(c21.c211f,0)-vl(c21.c2131,0) as a,a01_key from c21 where gz_ym=C21_c21e7) c
on (p.a01_key=a.a01_key)
when matched then
update p set p.C21_c21_ysgz=c.a

5、结论
我自己用7万多条数据简单测试了一下:
1、子查询方式:分别用了8s多,6s多;
2、游标方式:3s多
3、inline view方式:1s多
4、merge方式:1s多
5、标准update语法,单表更新或较简单的语句采用使用此方案更优。
inline view更新法,两表关联且被更新表通过关联表主键关联的,采用此方案更优。
merge更新法,两表关联且被更新表不是通过关联表主键关联的,采用此方案更优。
游标更新法 ,多表关联且逻辑复杂的,采用此方案更优。
6、以上只是基于个人简单测试,不一定准,还是根据执行计划去选择最优方式,提高语句效率。

完工
休息了。

本文标签:
网站声明:本文由风哥整理发布,转载请保留此段声明,本站所有内容将不对其使用后果做任何承诺,请读者谨慎使用!
【上一篇】
【下一篇】