UPDATE语句执行过长，求大佬指导

[黄硕。]

UPDATE SCONTAINERINFO SC
SET (SC.SPCI_FULL_IN_TIME, SC.REC_VER) =
(SELECT T1.ICEO_GATE_IN_OUT_TIME, SC.REC_VER + 1
FROM ICODECO T1, SEXPORTMANIFEST SSEM, SHSAILINGSCHEDULE SHSS
WHERE T1.ICEO_BATCH_NO = '3358749'
AND SSEM.SSEM_VOYAGE_ID = SHSS.SHSS_VOYAGE_ID
AND SC.SPCI_EXP_BL_ID = SSEM.SSEM_EXP_BL_ID
AND (SHSS.SHSS_ETD_TIME + 5 >= T1.ICEO_GATE_IN_OUT_TIME OR
SHSS.SHSS_ETD_TIME - 5 <= T1.ICEO_GATE_IN_OUT_TIME)
AND SHSS.SHSS_ETD_TIME < SYSDATE + 30
AND SC.SPCI_FULL_IN_TIME IS NULL
AND SSEM.SSEM_VALID_FLAG = 'Y'
AND T1.ICEO_CONTAINER_NO = SC.SPCI_CNT_NO
AND T1.ICEO_DISPOSE_FLAG = 'N'
AND (T1.ICEO_CTN_MOVE_TYPE = 'FI' --重箱进场
OR T1.ICEO_CTN_MOVE_TYPE = 'ICY' --进口重柜卸驳船
OR T1.ICEO_CTN_MOVE_TYPE = 'FD' --重箱卸船
OR T1.ICEO_CTN_MOVE_TYPE = 'EI' --空箱进场
OR T1.ICEO_CTN_MOVE_TYPE = 'ED' --空箱卸船
)
AND SHSS.SHSS_ORG_ID = T1.ICEO_ORG_ID
AND SHSS.SHSS_ORG_ID = '441'
AND ROWNUM = 1)
WHERE SC.SPCI_CREATE_TIME > SYSDATE - 30
AND SC.SPCI_ORG_ID = '441'
AND EXISTS
(SELECT 1
FROM ICODECO T1, SEXPORTMANIFEST SSEM, SHSAILINGSCHEDULE SHSS
WHERE T1.ICEO_BATCH_NO = '3358749'
AND SSEM.SSEM_VOYAGE_ID = SHSS.SHSS_VOYAGE_ID
AND SC.SPCI_EXP_BL_ID = SSEM.SSEM_EXP_BL_ID
AND (SHSS.SHSS_ETD_TIME + 5 >= T1.ICEO_GATE_IN_OUT_TIME OR
SHSS.SHSS_ETD_TIME - 5 <= T1.ICEO_GATE_IN_OUT_TIME)
AND SHSS.SHSS_ETD_TIME < SYSDATE + 30
AND SC.SPCI_FULL_IN_TIME IS NULL
AND SSEM.SSEM_VALID_FLAG = 'Y'
AND T1.ICEO_DISPOSE_FLAG = 'N'
AND T1.ICEO_CONTAINER_NO = SC.SPCI_CNT_NO
AND (T1.ICEO_CTN_MOVE_TYPE = 'FI' --重箱进场
OR T1.ICEO_CTN_MOVE_TYPE = 'ICY' --进口重柜卸驳船
OR T1.ICEO_CTN_MOVE_TYPE = 'FD' --重箱卸船
OR T1.ICEO_CTN_MOVE_TYPE = 'EI' --空箱进场
OR T1.ICEO_CTN_MOVE_TYPE = 'ED' --空箱卸船
)
AND SHSS.SHSS_ORG_ID = T1.ICEO_ORG_ID
AND SHSS.SHSS_ORG_ID = '441')

[数据开发者]

1、先把exists部分创建一个表，创建索引
2、把需要更新部分创建一个表，创建索引
3、把更新来源数据创建一个表，创建索引
4、把1、2、3步骤的数据来做更新，既得到更新后的数据，再替换或更新回原表

[超叔csdn]

改成merge into的写法

[stelf]

列上面带了计算导致日期不走索引，

[编程写手]

建议编写存储过程或者假表统一管理需要的字段不，尽量不要使用过多的and和or这样不利于使用索引优化，不要使用子查询

[AHUA1001]

UPDATE SCONTAINERINFO SC SET (SC.SPCI_FULL_IN_TIME, SC.REC_VER) = (SELECT T1.ICEO_GATE_IN_OUT_TIME, SC.REC_VER + 1 FROM ICODECO T1, SEXPORTMANIFEST SSEM, SHSAILINGSCHEDULE SHSS WHERE T1.ICEO_BATCH_NO = '3358749' AND SSEM.SSEM_VOYAGE_ID = SHSS.SHSS_VOYAGE_ID AND SC.SPCI_EXP_BL_ID = SSEM.SSEM_EXP_BL_ID AND SHSS.SHSS_ETD_TIME BETWEEN T1.ICEO_GATE_IN_OUT_TIME - 5 AND T1.ICEO_GATE_IN_OUT_TIME + 5 AND SHSS.SHSS_ETD_TIME < SYSDATE + 30 AND SC.SPCI_FULL_IN_TIME IS NULL AND SSEM.SSEM_VALID_FLAG = 'Y' AND T1.ICEO_CONTAINER_NO = SC.SPCI_CNT_NO AND T1.ICEO_DISPOSE_FLAG = 'N' AND T1.ICEO_CTN_MOVE_TYPE IN ('FI','ICY','FD','EI','ED') AND SHSS.SHSS_ORG_ID = T1.ICEO_ORG_ID AND SHSS.SHSS_ORG_ID = '441' AND T1.ICEO_GATE_IN_OUT_TIME IS NOT NULL AND SC.REC_VER IS NOT NULL AND ROWNUM = 1) WHERE SC.SPCI_CREATE_TIME > SYSDATE - 30 AND SC.SPCI_ORG_ID = '441' ;

[一名即将失业的db]

merge into SCONTAINERINFO SC --SET (SC.SPCI_FULL_IN_TIME, SC.REC_VER) = using (SELECT T1.ICEO_GATE_IN_OUT_TIME, T1.ICEO_CONTAINER_NO, SSEM.SSEM_EXP_BL_ID, ROW_NUMBER() OVER(PARTITION BY T1.ICEO_CONTAINER_NO,SSEM.SSEM_EXP_BL_ID) AS RN FROM ICODECO T1, SEXPORTMANIFEST SSEM, SHSAILINGSCHEDULE SHSS WHERE T1.ICEO_BATCH_NO = '3358749' AND SSEM.SSEM_VOYAGE_ID = SHSS.SHSS_VOYAGE_ID --AND SC.SPCI_EXP_BL_ID = SSEM.SSEM_EXP_BL_ID AND (SHSS.SHSS_ETD_TIME + 5 >= T1.ICEO_GATE_IN_OUT_TIME OR SHSS.SHSS_ETD_TIME - 5 <= T1.ICEO_GATE_IN_OUT_TIME) AND SHSS.SHSS_ETD_TIME < SYSDATE + 30 --AND SC.SPCI_FULL_IN_TIME IS NULL AND SSEM.SSEM_VALID_FLAG = 'Y' --AND T1.ICEO_CONTAINER_NO = SC.SPCI_CNT_NO AND T1.ICEO_DISPOSE_FLAG = 'N' AND T1.ICEO_CTN_MOVE_TYPE in( 'FI','ICY','FD','EI','ED') --重箱进场\进口重柜卸驳船\重箱卸船\空箱进场\空箱卸船 AND SHSS.SHSS_ORG_ID = T1.ICEO_ORG_ID AND SHSS.SHSS_ORG_ID = '441') T ON(SC.SPCI_EXP_BL_ID = T.SSEM_EXP_BL_ID AND T.ICEO_CONTAINER_NO = SC.SPCI_CNT_NO AND RN = 1) WHERE SC.SPCI_CREATE_TIME > SYSDATE - 30 AND SC.SPCI_ORG_ID = '441' AND EXISTS AND SC.SPCI_FULL_IN_TIME IS NULL

[一名即将失业的db]

merge into SCONTAINERINFO SC --SET (SC.SPCI_FULL_IN_TIME, SC.REC_VER) = using (SELECT T1.ICEO_GATE_IN_OUT_TIME, T1.ICEO_CONTAINER_NO, SSEM.SSEM_EXP_BL_ID, ROW_NUMBER() OVER(PARTITION BY T1.ICEO_CONTAINER_NO,SSEM.SSEM_EXP_BL_ID) AS RN FROM ICODECO T1, SEXPORTMANIFEST SSEM, SHSAILINGSCHEDULE SHSS WHERE T1.ICEO_BATCH_NO = '3358749' AND SSEM.SSEM_VOYAGE_ID = SHSS.SHSS_VOYAGE_ID --AND SC.SPCI_EXP_BL_ID = SSEM.SSEM_EXP_BL_ID AND (SHSS.SHSS_ETD_TIME + 5 >= T1.ICEO_GATE_IN_OUT_TIME OR SHSS.SHSS_ETD_TIME - 5 <= T1.ICEO_GATE_IN_OUT_TIME) AND SHSS.SHSS_ETD_TIME < SYSDATE + 30 --AND SC.SPCI_FULL_IN_TIME IS NULL AND SSEM.SSEM_VALID_FLAG = 'Y' --AND T1.ICEO_CONTAINER_NO = SC.SPCI_CNT_NO AND T1.ICEO_DISPOSE_FLAG = 'N' AND T1.ICEO_CTN_MOVE_TYPE in( 'FI','ICY','FD','EI','ED') --重箱进场\进口重柜卸驳船\重箱卸船\空箱进场\空箱卸船 AND SHSS.SHSS_ORG_ID = T1.ICEO_ORG_ID AND SHSS.SHSS_ORG_ID = '441') T ON(SC.SPCI_EXP_BL_ID = T.SSEM_EXP_BL_ID AND T.ICEO_CONTAINER_NO = SC.SPCI_CNT_NO AND RN = 1) WHEN MATCHED THEN UPDATE SET SC.SPCI_FULL_IN_TIME = ICEO_GATE_IN_OUT_TIME, SC.REC_VER = SC.REC_VER + 1 WHERE SC.SPCI_CREATE_TIME > SYSDATE - 30 AND SC.SPCI_ORG_ID = '441' AND EXISTS AND SC.SPCI_FULL_IN_TIME IS NULL; 之前的忘了更新了，用这个看看

[lhdz_bj]

更改的记录数多的话，建议改成merge into。