达梦大批量数据更新慢优化记录
示例sql如下，两表关联，更新一千多万数据，执行需要18分钟
UPDATE T1 SET (Balance, Available,Frozen,HFrozen, AbnmFrozen,SumBalance,SumAmt,
AvgCost,
LastModiDT,IncomeAmt,IncomeDay,IncomeNext, Tail,TailOrder, PreDisAmt,
SumDisAmt,
AutoDisAmt, SaleFee) =
(SELECT Balance, Available, Frozen, HFrozen, AbnmFrozen, SumBalance, SumAmt,
AvgCost,'20240808',IncomeAmt + IncomeDay + IncomeNext,0,0, 0, 0,
NVL(PreDisAmt,0),NVL(SumDisAmt,0), NVL(AutoDisAmt,0),NVL(SaleFee,0)
FROM T2 WHERE T2.FundID = T1.FundID AND T2.FundAcct = T1.FundAcct AND
T2.SeatNO = T1.SeatNO AND T2.CustNO = T1.CustNO AND T2.ChargeType
= T1.ChargeType)
WHERE (FundID,FundAcct,SeatNO,CustNo,ChargeType) IN
(SELECT FundID,FundAcct,SeatNO,CustNo,ChargeType FROM T2
WHERE T2.ApplyST = '00' AND T2.ChgFlag = 'M');
首先看下查询需要17分钟，sql如下
select count(*) from ( select
T1.FundID,
T1.FundAcct,T1.SeatNO,T1.CustNO,T1.ChargeType,
T1.Balance,T1.Available, T1.Frozen,T1.HFrozen,
T1.AbnmFrozen,T1.SumBalance, T1.SumAmt,
T1.AvgCost,T1.LastModiDT, T1.IncomeAmt,
T1.IncomeDay, T1.IncomeNext,T1.Tail,
T1.TailOrder,T1.PreDisAmt, T1.SumDisAmt, T1.
AutoDisAmt,T1.SaleFee from T1, T2
WHERE T2.FundID = T1.FundID AND T2.FundAcct =
T1.FundAcct AND
T2.SeatNO = T1.SeatNO AND T2.CustNO = T1.CustNO
AND T2.ChargeType = T1.ChargeType) a
WHERE
(a.FundID,a.FundAcct,a.SeatNO,a.CustNo,a.ChargeType) IN
(SELECT FundID,FundAcct,SeatNO,CustNo,ChargeType FROM T2
WHERE T2.ApplyST = '00' AND T2.ChgFlag = 'M');
et和执行计划如下
根据执行计划和et，可以定位到主要慢在嵌套循环，添加 /*+enable_index_join(0)*/ 让执行计划走hash关联，执行时间减少到85s
让执行计划走hahs关联，重新执行update ，执行时间减少一半
还可以通过merge 改写来进一步提升执行效率，执行时间减少到6分钟，等价改写如下：
merge /*+enable_index_join(0)*/ into T1 v1
using(SELECT FundID,FundAcct,SeatNO,CustNO,ChargeType,
Balance as Balance,
Available as Available,Frozen as Frozen,HFrozen as
HFrozen,
AbnmFrozen as AbnmFrozen, SumBalance as SumBalance,
SumAmt as SumAmt,
AvgCost as AvgCost,'20240201' as
LastModiDT,IncomeAmt + IncomeDay + IncomeNext as IncomeAmt,
0 as IncomeDay,0 as IncomeNext,0 as Tail,0 as
TailOrder, NVL(PreDisAmt,0) as PreDisAmt,
NVL(SumDisAmt,0) as SumDisAmt,NVL(AutoDisAmt,0) as
AutoDisAmt,NVL(SaleFee,0) as SaleFee
from T2 WHERE T2.ApplyST = '00' AND T2.ChgFlag = 'M' )
v2
on (v1.FundID = v2.FundID AND v1.FundAcct =
v2.FundAcct
AND v1.SeatNO = v2.SeatNO AND v1.CustNO =
v2.CustNO
AND v1.ChargeType = v2.ChargeType)
WHEN MATCHED THEN UPDATE
SET v1.Balance=v2.Balance ,v1.Available=v2.Available ,
v1.Frozen=v2.Frozen ,v1.HFrozen=v2.HFrozen ,
v1.AbnmFrozen=v2.AbnmFrozen
,v1.SumBalance=v2.SumBalance ,
v1.SumAmt=v2.SumAmt ,v1.AvgCost=v2.AvgCost
,v1.LastModiDT=v2.LastModiDT ,
v1.IncomeAmt=v2.IncomeAmt ,
v1.IncomeDay=v2.IncomeDay ,
v1.IncomeNext=v2.IncomeNext ,v1.Tail=v2.Tail
,v1.TailOrder=v2.TailOrder ,
v1.PreDisAmt=v2.PreDisAmt
,v1.SumDisAmt=v2.SumDisAmt ,
v1.AutoDisAmt=v2.AutoDisAmt ,v1.SaleFee=v2.SaleFee ;
et和执行计划如下
根据上面的et和执行计划，主要耗时在hash及update，多列更新可以尝试调整MULTI_UPD_OPT_FLAG参数，内存足够的情况下还可以调整HJ_BUF_SIZE和JOIN_HASH_SIZE
参数避免hash连接刷盘和hash冲突来提升效率
