现有一张表70W数据,现更新其中的10W数据,用一条UPDAT发现效率很慢,有没有效率高一些的,
SQL如下:
update history h
set h.ndlr = (SELECT SUM(NVL(mnlr, 0)) AS ck
FROM history a, sysdata b
WHERE to_char(a.tjrq, 'mmdd') in('0331','0630','0930',to_char(b.last_date, 'mmdd'))
and to_char(tjrq, 'yyyy') = to_char(b.last_date,'yyyy')
and a.zh = h.zh and a.khjlh=h.khjlh),
h.nddx = (SELECT SUM(NVL(ljye, 0)) AS ljye
FROM mngr_simgain_acct_history a, sys_datactl b
WHERE to_char(a.tjrq, 'mmdd') in
('0331','0630','0930',to_char(b.last_date, 'mmdd'))
and to_char(tjrq, 'yyyy') =
to_char(b.last_date + 1, 'yyyy')
and zh = h.zh and khjlh=h.khjlh and zhxh=h.zhxh)
where h.tjrq = (select last_date from sys_datactl) and (h.mnlrzhlx='1' or h.mnlrzhlx='2');
SQL如下:
update history h
set h.ndlr = (SELECT SUM(NVL(mnlr, 0)) AS ck
FROM history a, sysdata b
WHERE to_char(a.tjrq, 'mmdd') in('0331','0630','0930',to_char(b.last_date, 'mmdd'))
and to_char(tjrq, 'yyyy') = to_char(b.last_date,'yyyy')
and a.zh = h.zh and a.khjlh=h.khjlh),
h.nddx = (SELECT SUM(NVL(ljye, 0)) AS ljye
FROM mngr_simgain_acct_history a, sys_datactl b
WHERE to_char(a.tjrq, 'mmdd') in
('0331','0630','0930',to_char(b.last_date, 'mmdd'))
and to_char(tjrq, 'yyyy') =
to_char(b.last_date + 1, 'yyyy')
and zh = h.zh and khjlh=h.khjlh and zhxh=h.zhxh)
where h.tjrq = (select last_date from sys_datactl) and (h.mnlrzhlx='1' or h.mnlrzhlx='2');
set h.ndlr = (SELECT SUM(NVL(mnlr, 0)) AS ck
FROM history a, sysdata b
WHERE to_char(a.tjrq, 'mmdd') in
('0331', '0630', '0930', to_char(b.last_date, 'mmdd'))
and to_char(tjrq, 'yyyy') = to_char(b.last_date, 'yyyy')
and a.zh = h.zh
and a.khjlh = h.khjlh),------这个结果集,考虑优化
h.nddx = (SELECT SUM(NVL(ljye, 0)) AS ljye
FROM mngr_simgain_acct_history a, sys_datactl b
WHERE to_char(a.tjrq, 'mmdd') in
('0331', '0630', '0930', to_char(b.last_date, 'mmdd'))
and to_char(tjrq, 'yyyy') =
to_char(b.last_date + 1, 'yyyy')
and zh = h.zh
and khjlh = h.khjlh
and zhxh = h.zhxh)------这个结果集,考虑优化
where h.tjrq = (select last_date from sys_datactl)
and (h.mnlrzhlx = '1' or h.mnlrzhlx = '2');-------这2个条件考虑优化
--更新第一个值
MERGE INTO HISTORY H
USING (SELECT SUM(NVL(MNLR, 0)) AS CK, A.ZH, A.KHJLH
FROM HISTORY A, SYSDATA B
WHERE TO_CHAR(A.TJRQ, 'mmdd') IN
('0331', '0630', '0930', TO_CHAR(B.LAST_DATE, 'mmdd'))
AND TO_CHAR(TJRQ, 'yyyy') = TO_CHAR(B.LAST_DATE, 'yyyy')) A
ON (A.ZH = H.ZH AND A.KHJLH = H.KHJLH)
WHEN MATCHED THEN
UPDATE
SET H.NDLR = A.CK
WHERE H.TJRQ = (SELECT LAST_DATE FROM SYS_DATACTL)
AND (H.MNLRZHLX = '1' OR H.MNLRZHLX = '2');--更新第二个值
MERGE INTO HISTORY H
USING (SELECT SUM(NVL(LJYE, 0)) AS LJYE, ZH, KHJLH, ZHXH
FROM MNGR_SIMGAIN_ACCT_HISTORY A, SYS_DATACTL B
WHERE TO_CHAR(A.TJRQ, 'mmdd') IN
('0331', '0630', '0930', TO_CHAR(B.LAST_DATE, 'mmdd'))
AND TO_CHAR(TJRQ, 'yyyy') = TO_CHAR(B.LAST_DATE + 1, 'yyyy')) A
ON (A.ZH = H.ZH AND A.KHJLH = H.KHJLH AND A.ZHXH = H.ZHXH)
WHEN MATCHED THEN
UPDATE
SET H.NDDX = A.LJYE
WHERE H.TJRQ = (SELECT LAST_DATE FROM SYS_DATACTL)
AND (H.MNLRZHLX = '1' OR H.MNLRZHLX = '2');
在using 提示not a single-group group function
这是什么原因,我也不需要分组`
--看到了,忽略了,改了下--更新第一个值
MERGE INTO HISTORY H
USING (SELECT SUM(NVL(MNLR, 0)) AS CK, A.ZH, A.KHJLH
FROM HISTORY A, SYSDATA B
WHERE TO_CHAR(A.TJRQ, 'mmdd') IN
('0331', '0630', '0930', TO_CHAR(B.LAST_DATE, 'mmdd'))
AND TO_CHAR(TJRQ, 'yyyy') = TO_CHAR(B.LAST_DATE, 'yyyy')
GROUP BY A.ZH, A.KHJLH) A
ON (A.ZH = H.ZH AND A.KHJLH = H.KHJLH)
WHEN MATCHED THEN
UPDATE
SET H.NDLR = A.CK
WHERE H.TJRQ = (SELECT LAST_DATE FROM SYS_DATACTL)
AND (H.MNLRZHLX = '1' OR H.MNLRZHLX = '2');--更新第二个值
MERGE INTO HISTORY H
USING (SELECT SUM(NVL(LJYE, 0)) AS LJYE, ZH, KHJLH, ZHXH
FROM MNGR_SIMGAIN_ACCT_HISTORY A, SYS_DATACTL B
WHERE TO_CHAR(A.TJRQ, 'mmdd') IN
('0331', '0630', '0930', TO_CHAR(B.LAST_DATE, 'mmdd'))
AND TO_CHAR(TJRQ, 'yyyy') = TO_CHAR(B.LAST_DATE + 1, 'yyyy')
GROUP BY ZH, KHJLH, ZHXH) A
ON (A.ZH = H.ZH AND A.KHJLH = H.KHJLH AND A.ZHXH = H.ZHXH)
WHEN MATCHED THEN
UPDATE
SET H.NDDX = A.LJYE
WHERE H.TJRQ = (SELECT LAST_DATE FROM SYS_DATACTL)
AND (H.MNLRZHLX = '1' OR H.MNLRZHLX = '2');