The UPDATE SCONTAINERINFO SC
SET (SC) SPCI_FULL_IN_TIME, SC. REC_VER)=
(SELECT T1. ICEO_GATE_IN_OUT_TIME, SC. REC_VER + 1
The FROM ICODECO T1, SEXPORTMANIFEST SSEM, SHSAILINGSCHEDULE SHSS
WHERE a 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 & gt;=T1. ICEO_GATE_IN_OUT_TIME OR
SHSS. SHSS_ETD_TIME - 5 & lt;=T1. ICEO_GATE_IN_OUT_TIME)
AND SHSS. SHSS_ETD_TIME & lt; 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 - &heavy play
OR T1. ICEO_CTN_MOVE_TYPE='ICY - imported heavy tank unloading barge
The OR T1. ICEO_CTN_MOVE_TYPE='FD - &heavy discharging
Empty containers OR T1. ICEO_CTN_MOVE_TYPE='EI - play
Empty containers OR T1. ICEO_CTN_MOVE_TYPE='ED' - discharging
)
AND SHSS. SHSS_ORG_ID=T1. ICEO_ORG_ID
AND SHSS. SHSS_ORG_ID='441'
AND ROWNUM=1)
WHERE SC. SPCI_CREATE_TIME & gt; SYSDATE - 30
AND SC. SPCI_ORG_ID='441'
AND the EXISTS
(SELECT 1
The FROM ICODECO T1, SEXPORTMANIFEST SSEM, SHSAILINGSCHEDULE SHSS
WHERE a 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 & gt;=T1. ICEO_GATE_IN_OUT_TIME OR
SHSS. SHSS_ETD_TIME - 5 & lt;=T1. ICEO_GATE_IN_OUT_TIME)
AND SHSS. SHSS_ETD_TIME & lt; 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 - &heavy play
OR T1. ICEO_CTN_MOVE_TYPE='ICY - imported heavy tank unloading barge
The OR T1. ICEO_CTN_MOVE_TYPE='FD - &heavy discharging
Empty containers OR T1. ICEO_CTN_MOVE_TYPE='EI - play
Empty containers OR T1. ICEO_CTN_MOVE_TYPE='ED' - discharging
)
AND SHSS. SHSS_ORG_ID=T1. ICEO_ORG_ID
AND SHSS. SHSS_ORG_ID='441')

CodePudding user response:

To merge into writing

CodePudding user response:

Part 1, put the exists to create a table, create an index
Part 2, the need to be updated to create a table, create an index
3, the update data sources to create a table, create an index
4, 1, 2, 3 steps of the data to do the update, both to get the updated data, to replace or to update the original table

CodePudding user response:

Don't go date listed above with the calculation result in index,

CodePudding user response:

Recommend writing stored procedures or false tables unified management fields don't need, try not to use too much and and or so bad for using the index optimization, do not use a subquery

CodePudding user response:

The UPDATE SCONTAINERINFO SC
SET (SC) SPCI_FULL_IN_TIME, SC. REC_VER)=
(SELECT T1. ICEO_GATE_IN_OUT_TIME, SC. REC_VER + 1
The FROM ICODECO T1, SEXPORTMANIFEST SSEM, SHSAILINGSCHEDULE SHSS
WHERE a 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 & lt; 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 & gt; SYSDATE - 30
AND SC. SPCI_ORG_ID='441';

CodePudding user response:

Change the number of records that many words, suggest to merge into,

CodePudding user response:

The 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 an RN
The FROM ICODECO T1, SEXPORTMANIFEST SSEM, SHSAILINGSCHEDULE SHSS
WHERE a 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 & gt;=T1. ICEO_GATE_IN_OUT_TIME OR
SHSS. SHSS_ETD_TIME - 5 & lt;=T1. ICEO_GATE_IN_OUT_TIME)
AND SHSS. SHSS_ETD_TIME & lt; 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') - &heavy approach \ import heavy tank unloading barge \ &heavy empty containers, empty container comes into play, discharging discharging
AND SHSS. SHSS_ORG_ID=T1. ICEO_ORG_ID
T='441' AND SHSS. SHSS_ORG_ID)
ON (SC) SPCI_EXP_BL_ID=T.S SEM_EXP_BL_ID AND T.I CEO_CONTAINER_NO=SC. SPCI_CNT_NO AND RN=1)
WHERE SC. SPCI_CREATE_TIME & gt; SYSDATE - 30
AND SC. SPCI_ORG_ID='441'
AND the EXISTS
AND SC. SPCI_FULL_IN_TIME IS NULL

CodePudding user response:

The merge into SCONTAINERINFO SC
nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull