现在有这样一种情况,源表数据量1000W,或者更大,属于总表,增量表数据量百万级别,要求把增量表数据更新到源表中。
我采用的是MERGE语句,根据条件判断是insert还是update,增量表300W的时候且全走UPDATE分支时,执行不动了,请问增量更新还有什么更好的方案?
MERGE INTO MDA_订单_100M
USING MDA_订单_100M_TEMP
ON ( MDA_订单_100M.产品 = MDA_订单_100M_TEMP.产品
AND MDA_订单_100M.货主城市 = MDA_订单_100M_TEMP.货主城市
AND MDA_订单_100M.货主地区 = MDA_订单_100M_TEMP.货主地区
AND MDA_订单_100M.运货商 = MDA_订单_100M_TEMP.运货商
AND MDA_订单_100M.发货日期 = MDA_订单_100M_TEMP.发货日期
AND MDA_订单_100M.到货日期 = MDA_订单_100M_TEMP.到货日期
AND MDA_订单_100M.订购日期 = MDA_订单_100M_TEMP.订购日期
AND MDA_订单_100M.客户 = MDA_订单_100M_TEMP.客户
AND MDA_订单_100M.雇员 = MDA_订单_100M_TEMP.雇员
AND 1 = 1 )
WHEN MATCHED THEN
UPDATE SET MDA_订单_100M.订购数量_sum = MDA_订单_100M.订购数量_sum + MDA_订单_100M_TEMP.订购数量_sum,
MDA_订单_100M.订购数量_count = MDA_订单_100M.订购数量_count + MDA_订单_100M_TEMP.订购数量_count,
MDA_订单_100M.订购数量_avg = ( MDA_订单_100M.订购数量_avg * MDA_订单_100M.订购数量_count + MDA_订单_100M_TEMP.订购数量_avg * MDA_订单_100M_TEMP.订购数量_count ) / ( MDA_订单_100M.订购数量_count + MDA_订单_100M_TEMP.订购数量_count ),
MDA_订单_100M.订购数量_max = CASE
WHEN MDA_订单_100M.订购数量_max >= MDA_订单_100M_TEMP.订购数量_max THEN MDA_订单_100M.订购数量_max
ELSE MDA_订单_100M_TEMP.订购数量_max
END,
MDA_订单_100M.订购数量_min = CASE
WHEN MDA_订单_100M.订购数量_min <= MDA_订单_100M_TEMP.订购数量_min THEN MDA_订单_100M.订购数量_min
ELSE MDA_订单_100M_TEMP.订购数量_min
END,
MDA_订单_100M.单价_sum = MDA_订单_100M.单价_sum + MDA_订单_100M_TEMP.单价_sum,
MDA_订单_100M.单价_count = MDA_订单_100M.单价_count + MDA_订单_100M_TEMP.单价_count,
MDA_订单_100M.单价_avg = ( MDA_订单_100M.单价_avg * MDA_订单_100M.单价_count + MDA_订单_100M_TEMP.单价_avg * MDA_订单_100M_TEMP.单价_count ) / ( MDA_订单_100M.单价_count + MDA_订单_100M_TEMP.单价_count ),
MDA_订单_100M.单价_max = CASE
WHEN MDA_订单_100M.单价_max >= MDA_订单_100M_TEMP.单价_max THEN MDA_订单_100M.单价_max
ELSE MDA_订单_100M_TEMP.单价_max
END,
MDA_订单_100M.单价_min = CASE
WHEN MDA_订单_100M.单价_min <= MDA_订单_100M_TEMP.单价_min THEN MDA_订单_100M.单价_min
ELSE MDA_订单_100M_TEMP.单价_min
END,
MDA_订单_100M.运货费_sum = MDA_订单_100M.运货费_sum + MDA_订单_100M_TEMP.运货费_sum,
MDA_订单_100M.运货费_count = MDA_订单_100M.运货费_count + MDA_订单_100M_TEMP.运货费_count,
MDA_订单_100M.运货费_avg = ( MDA_订单_100M.运货费_avg * MDA_订单_100M.运货费_count + MDA_订单_100M_TEMP.运货费_avg * MDA_订单_100M_TEMP.运货费_count ) / ( MDA_订单_100M.运货费_count + MDA_订单_100M_TEMP.运货费_count ),
MDA_订单_100M.运货费_max = CASE
WHEN MDA_订单_100M.运货费_max >= MDA_订单_100M_TEMP.运货费_max THEN MDA_订单_100M.运货费_max
ELSE MDA_订单_100M_TEMP.运货费_max
END,
MDA_订单_100M.运货费_min = CASE
WHEN MDA_订单_100M.运货费_min <= MDA_订单_100M_TEMP.运货费_min THEN MDA_订单_100M.运货费_min
ELSE MDA_订单_100M_TEMP.运货费_min
END,
MDA_订单_100M.订单ID_sum = MDA_订单_100M.订单ID_sum + MDA_订单_100M_TEMP.订单ID_sum,
MDA_订单_100M.订单ID_count = MDA_订单_100M.订单ID_count + MDA_订单_100M_TEMP.订单ID_count,
MDA_订单_100M.订单ID_avg = ( MDA_订单_100M.订单ID_avg * MDA_订单_100M.订单ID_count + MDA_订单_100M_TEMP.订单ID_avg * MDA_订单_100M_TEMP.订单ID_count ) / ( MDA_订单_100M.订单ID_count + MDA_订单_100M_TEMP.订单ID_count ),
MDA_订单_100M.订单ID_max = CASE
WHEN MDA_订单_100M.订单ID_max >= MDA_订单_100M_TEMP.订单ID_max THEN MDA_订单_100M.订单ID_max
ELSE MDA_订单_100M_TEMP.订单ID_max
END,
MDA_订单_100M.订单ID_min = CASE
WHEN MDA_订单_100M.订单ID_min <= MDA_订单_100M_TEMP.订单ID_min THEN MDA_订单_100M.订单ID_min
ELSE MDA_订单_100M_TEMP.订单ID_min
END
WHEN NOT MATCHED THEN
INSERT (MDA_订单_100M.产品,
MDA_订单_100M.货主城市,
MDA_订单_100M.货主地区,
MDA_订单_100M.运货商,
MDA_订单_100M.发货日期,
MDA_订单_100M.到货日期,
MDA_订单_100M.订购日期,
MDA_订单_100M.客户,
MDA_订单_100M.雇员,
MDA_订单_100M.订购数量_sum,
MDA_订单_100M.订购数量_count,
MDA_订单_100M.订购数量_avg,
MDA_订单_100M.订购数量_max,
MDA_订单_100M.订购数量_min,
MDA_订单_100M.单价_sum,
MDA_订单_100M.单价_count,
MDA_订单_100M.单价_avg,
MDA_订单_100M.单价_max,
MDA_订单_100M.单价_min,
MDA_订单_100M.运货费_sum,
MDA_订单_100M.运货费_count,
MDA_订单_100M.运货费_avg,
MDA_订单_100M.运货费_max,
MDA_订单_100M.运货费_min,
MDA_订单_100M.订单ID_sum,
MDA_订单_100M.订单ID_count,
MDA_订单_100M.订单ID_avg,
MDA_订单_100M.订单ID_max,
MDA_订单_100M.订单ID_min)
VALUES(MDA_订单_100M_TEMP.产品,
MDA_订单_100M_TEMP.货主城市,
MDA_订单_100M_TEMP.货主地区,
MDA_订单_100M_TEMP.运货商,
MDA_订单_100M_TEMP.发货日期,
MDA_订单_100M_TEMP.到货日期,
MDA_订单_100M_TEMP.订购日期,
MDA_订单_100M_TEMP.客户,
MDA_订单_100M_TEMP.雇员,
MDA_订单_100M_TEMP.订购数量_sum,
MDA_订单_100M_TEMP.订购数量_count,
MDA_订单_100M_TEMP.订购数量_avg,
MDA_订单_100M_TEMP.订购数量_max,
MDA_订单_100M_TEMP.订购数量_min,
MDA_订单_100M_TEMP.单价_sum,
MDA_订单_100M_TEMP.单价_count,
MDA_订单_100M_TEMP.单价_avg,
MDA_订单_100M_TEMP.单价_max,
MDA_订单_100M_TEMP.单价_min,
MDA_订单_100M_TEMP.运货费_sum,
MDA_订单_100M_TEMP.运货费_count,
MDA_订单_100M_TEMP.运货费_avg,
MDA_订单_100M_TEMP.运货费_max,
MDA_订单_100M_TEMP.运货费_min,
MDA_订单_100M_TEMP.订单ID_sum,
MDA_订单_100M_TEMP.订单ID_count,
MDA_订单_100M_TEMP.订单ID_avg,
MDA_订单_100M_TEMP.订单ID_max,
MDA_订单_100M_TEMP.订单ID_min)
我采用的是MERGE语句,根据条件判断是insert还是update,增量表300W的时候且全走UPDATE分支时,执行不动了,请问增量更新还有什么更好的方案?
MERGE INTO MDA_订单_100M
USING MDA_订单_100M_TEMP
ON ( MDA_订单_100M.产品 = MDA_订单_100M_TEMP.产品
AND MDA_订单_100M.货主城市 = MDA_订单_100M_TEMP.货主城市
AND MDA_订单_100M.货主地区 = MDA_订单_100M_TEMP.货主地区
AND MDA_订单_100M.运货商 = MDA_订单_100M_TEMP.运货商
AND MDA_订单_100M.发货日期 = MDA_订单_100M_TEMP.发货日期
AND MDA_订单_100M.到货日期 = MDA_订单_100M_TEMP.到货日期
AND MDA_订单_100M.订购日期 = MDA_订单_100M_TEMP.订购日期
AND MDA_订单_100M.客户 = MDA_订单_100M_TEMP.客户
AND MDA_订单_100M.雇员 = MDA_订单_100M_TEMP.雇员
AND 1 = 1 )
WHEN MATCHED THEN
UPDATE SET MDA_订单_100M.订购数量_sum = MDA_订单_100M.订购数量_sum + MDA_订单_100M_TEMP.订购数量_sum,
MDA_订单_100M.订购数量_count = MDA_订单_100M.订购数量_count + MDA_订单_100M_TEMP.订购数量_count,
MDA_订单_100M.订购数量_avg = ( MDA_订单_100M.订购数量_avg * MDA_订单_100M.订购数量_count + MDA_订单_100M_TEMP.订购数量_avg * MDA_订单_100M_TEMP.订购数量_count ) / ( MDA_订单_100M.订购数量_count + MDA_订单_100M_TEMP.订购数量_count ),
MDA_订单_100M.订购数量_max = CASE
WHEN MDA_订单_100M.订购数量_max >= MDA_订单_100M_TEMP.订购数量_max THEN MDA_订单_100M.订购数量_max
ELSE MDA_订单_100M_TEMP.订购数量_max
END,
MDA_订单_100M.订购数量_min = CASE
WHEN MDA_订单_100M.订购数量_min <= MDA_订单_100M_TEMP.订购数量_min THEN MDA_订单_100M.订购数量_min
ELSE MDA_订单_100M_TEMP.订购数量_min
END,
MDA_订单_100M.单价_sum = MDA_订单_100M.单价_sum + MDA_订单_100M_TEMP.单价_sum,
MDA_订单_100M.单价_count = MDA_订单_100M.单价_count + MDA_订单_100M_TEMP.单价_count,
MDA_订单_100M.单价_avg = ( MDA_订单_100M.单价_avg * MDA_订单_100M.单价_count + MDA_订单_100M_TEMP.单价_avg * MDA_订单_100M_TEMP.单价_count ) / ( MDA_订单_100M.单价_count + MDA_订单_100M_TEMP.单价_count ),
MDA_订单_100M.单价_max = CASE
WHEN MDA_订单_100M.单价_max >= MDA_订单_100M_TEMP.单价_max THEN MDA_订单_100M.单价_max
ELSE MDA_订单_100M_TEMP.单价_max
END,
MDA_订单_100M.单价_min = CASE
WHEN MDA_订单_100M.单价_min <= MDA_订单_100M_TEMP.单价_min THEN MDA_订单_100M.单价_min
ELSE MDA_订单_100M_TEMP.单价_min
END,
MDA_订单_100M.运货费_sum = MDA_订单_100M.运货费_sum + MDA_订单_100M_TEMP.运货费_sum,
MDA_订单_100M.运货费_count = MDA_订单_100M.运货费_count + MDA_订单_100M_TEMP.运货费_count,
MDA_订单_100M.运货费_avg = ( MDA_订单_100M.运货费_avg * MDA_订单_100M.运货费_count + MDA_订单_100M_TEMP.运货费_avg * MDA_订单_100M_TEMP.运货费_count ) / ( MDA_订单_100M.运货费_count + MDA_订单_100M_TEMP.运货费_count ),
MDA_订单_100M.运货费_max = CASE
WHEN MDA_订单_100M.运货费_max >= MDA_订单_100M_TEMP.运货费_max THEN MDA_订单_100M.运货费_max
ELSE MDA_订单_100M_TEMP.运货费_max
END,
MDA_订单_100M.运货费_min = CASE
WHEN MDA_订单_100M.运货费_min <= MDA_订单_100M_TEMP.运货费_min THEN MDA_订单_100M.运货费_min
ELSE MDA_订单_100M_TEMP.运货费_min
END,
MDA_订单_100M.订单ID_sum = MDA_订单_100M.订单ID_sum + MDA_订单_100M_TEMP.订单ID_sum,
MDA_订单_100M.订单ID_count = MDA_订单_100M.订单ID_count + MDA_订单_100M_TEMP.订单ID_count,
MDA_订单_100M.订单ID_avg = ( MDA_订单_100M.订单ID_avg * MDA_订单_100M.订单ID_count + MDA_订单_100M_TEMP.订单ID_avg * MDA_订单_100M_TEMP.订单ID_count ) / ( MDA_订单_100M.订单ID_count + MDA_订单_100M_TEMP.订单ID_count ),
MDA_订单_100M.订单ID_max = CASE
WHEN MDA_订单_100M.订单ID_max >= MDA_订单_100M_TEMP.订单ID_max THEN MDA_订单_100M.订单ID_max
ELSE MDA_订单_100M_TEMP.订单ID_max
END,
MDA_订单_100M.订单ID_min = CASE
WHEN MDA_订单_100M.订单ID_min <= MDA_订单_100M_TEMP.订单ID_min THEN MDA_订单_100M.订单ID_min
ELSE MDA_订单_100M_TEMP.订单ID_min
END
WHEN NOT MATCHED THEN
INSERT (MDA_订单_100M.产品,
MDA_订单_100M.货主城市,
MDA_订单_100M.货主地区,
MDA_订单_100M.运货商,
MDA_订单_100M.发货日期,
MDA_订单_100M.到货日期,
MDA_订单_100M.订购日期,
MDA_订单_100M.客户,
MDA_订单_100M.雇员,
MDA_订单_100M.订购数量_sum,
MDA_订单_100M.订购数量_count,
MDA_订单_100M.订购数量_avg,
MDA_订单_100M.订购数量_max,
MDA_订单_100M.订购数量_min,
MDA_订单_100M.单价_sum,
MDA_订单_100M.单价_count,
MDA_订单_100M.单价_avg,
MDA_订单_100M.单价_max,
MDA_订单_100M.单价_min,
MDA_订单_100M.运货费_sum,
MDA_订单_100M.运货费_count,
MDA_订单_100M.运货费_avg,
MDA_订单_100M.运货费_max,
MDA_订单_100M.运货费_min,
MDA_订单_100M.订单ID_sum,
MDA_订单_100M.订单ID_count,
MDA_订单_100M.订单ID_avg,
MDA_订单_100M.订单ID_max,
MDA_订单_100M.订单ID_min)
VALUES(MDA_订单_100M_TEMP.产品,
MDA_订单_100M_TEMP.货主城市,
MDA_订单_100M_TEMP.货主地区,
MDA_订单_100M_TEMP.运货商,
MDA_订单_100M_TEMP.发货日期,
MDA_订单_100M_TEMP.到货日期,
MDA_订单_100M_TEMP.订购日期,
MDA_订单_100M_TEMP.客户,
MDA_订单_100M_TEMP.雇员,
MDA_订单_100M_TEMP.订购数量_sum,
MDA_订单_100M_TEMP.订购数量_count,
MDA_订单_100M_TEMP.订购数量_avg,
MDA_订单_100M_TEMP.订购数量_max,
MDA_订单_100M_TEMP.订购数量_min,
MDA_订单_100M_TEMP.单价_sum,
MDA_订单_100M_TEMP.单价_count,
MDA_订单_100M_TEMP.单价_avg,
MDA_订单_100M_TEMP.单价_max,
MDA_订单_100M_TEMP.单价_min,
MDA_订单_100M_TEMP.运货费_sum,
MDA_订单_100M_TEMP.运货费_count,
MDA_订单_100M_TEMP.运货费_avg,
MDA_订单_100M_TEMP.运货费_max,
MDA_订单_100M_TEMP.运货费_min,
MDA_订单_100M_TEMP.订单ID_sum,
MDA_订单_100M_TEMP.订单ID_count,
MDA_订单_100M_TEMP.订单ID_avg,
MDA_订单_100M_TEMP.订单ID_max,
MDA_订单_100M_TEMP.订单ID_min)
系统:Windows server 2003
内存:8G
CPU:Intel Xeon E5504 现在有一种方案是先备份源表中需要update的数据,然后删除掉,最后insert into select(汇总)进去。
但是这样好像也快不到哪去。
试试看先将旧表和新表进行关联插入到一个表中,不要用临时表。
再将原先旧表改名,新插表改回旧表的方式。
怀疑数据库本身有问题,换个机器试试。
目标表在关联字段建本地分区索引 ,分区键和索引键一样
目标表的分区最好分到多个表空间上写个存储过程按分区扫源表,操作目标表(更新或插入,)
开多个session调存储过程传分区为参数
百万级别的数据服务器给力10分钟内就可以搞定了