业务需求需要将错误数据进行整理,主要是针对于length(l.brchno) >= 4这部分数据,以下是一个UPDATE进行更新的操作。
源表有28万的数据量,符合条件的有54327条数据。UPDATE执行时间大概是一个半小时,使用Merge进行优化后执行时间是27秒
explain plan for
update sif_member_load l
set l.brchno =
(select brchno
from (select distinct d.brchno, d.brchna
from sif_member_load d
where d.brchna in (select brchna
from sif_member_load ld
where ld.brchno like '%寿险%'
or ld.brchna like '%寿险%'
)
and length(d.brchno) < 4
and d.brchno <> '8619') k
where l.brchna = k.brchna)
where length(l.brchno) >= 4
and l.brchno not like '%综拓%'
and exists (select distinct d.brchno
from sif_member_load d
where d.brchna in (select brchna
from sif_member_load ld
where ld.brchno like '%寿险%'
or ld.brchna like '%寿险%'
/* where l.brchno = '寿险续期'
group by brchna
having count(*) > 1*/
)
and length(d.brchno) < 4
and d.brchno <> '8619');----加exists原因是找到符合条件的数据
select * from table (dbms_xplan.display);
执行计划
SQL> select * from table (dbms_xplan.display);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------
Plan hash value: 2740296014
-------------------------------------------------------------------------------------------------
| Id | Operation
使用Merge优化改写一个update语句
最新推荐文章于 2023-11-17 11:30:33 发布
本文探讨如何利用Merge语句优化传统的Update操作,通过实例解析Merge的优势,提高数据库更新性能。
摘要由CSDN通过智能技术生成