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')
uj5u.com熱心網友回復：
改成merge into的寫法uj5u.com熱心網友回復：
1、先把exists部分創建一個表，創建索引2、把需要更新部分創建一個表，創建索引
3、把更新來源資料創建一個表，創建索引
4、把1、2、3步驟的資料來做更新，既得到更新后的資料，再替換或更新回原表
uj5u.com熱心網友回復：
列上面帶了計算導致日期不走索引，uj5u.com熱心網友回復：
建議撰寫存盤程序或者假表統一管理需要的欄位不，盡量不要使用過多的and和or這樣不利于使用索引優化，不要使用子查詢uj5u.com熱心網友回復：
UPDATE SCONTAINERINFO SCSET (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' ;
uj5u.com熱心網友回復：
更改的記錄數多的話，建議改成merge into。uj5u.com熱心網友回復：
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
uj5u.com熱心網友回復：
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;
之前的忘了更新了，用這個看看
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/31410.html
標籤：開發
上一篇：sql陳述句的優化，查詢效率低
下一篇：求助！！！新服務器上windows 2012r2datacenter版，安裝oracle11g impdp匯入例外的慢