Now a SQL ran 3.5 seconds, want to optimize to 1 seconds
The update TMP_YQLOAD_VALID tt
Set the tt. Error_msg=tt. Error_msg | | '; '| | CRH (13) | | CRH (10) | |' error message ',
Tt. Is_operate=decode (' N ', 'Y', 'Y', 'N')
Where the exists (SELECT 1
The FROM MSC_YQEXPSCON_SUM_VIEW MC,
MSC_YQEXPSCON_VIEW M,
TMP_YQLOAD_VALID T/temporary table * * export preloaded view about bridge//* */
WHERE Margaret spellings ECR_CNT_QUANLITY & gt; MC. CT_NUM
AND (T.E RROR_MSG IS NULL OR T.I S_OPERATE='Y')
And TT. The ROWID=T.R OWID
And Margaret spellings ECR_CNT_TYPE=T.S PCI_CNT_TYPE box/* */
AND Margaret spellings ECR_CNT_SIZE=T.S PCI_CNT_SIZE/* */size
ORGID='8883' AND Margaret spellings HSS_ORG_ID/* */
AND Margaret spellings SEM_BL_NO=T.B L_NO/* SO */

AND MC. BL_NO=T.B L_NO
AND MC. SPCI_CNT_SIZE=T.S PCI_CNT_SIZE
AND MC. SPCI_CNT_TYPE=T.S PCI_CNT_TYPE
AND MC.ORG ORGID _ID='8883'/* */
='1' AND T.B L_STATUS/* 0 (import manifest */
)
And tt. STOP_VALID='N'

CodePudding user response:

The create or replace the view msc_yqexpscon_sum_view as
The select org_id, ty bl_no, ty. Spci_cnt_size, ty. Spci_cnt_type, count (distinct ty. Spci_cnt_no) as ct_Num
The from (select ty bl_no, ty. Spci_cnt_size, ty. Spci_cnt_type, ty. Spci_cnt_no, ty.org _id
The from TMP_YQLOAD_VALID ty
The union
The select sex ssem_bl_no, sco spci_cnt_size, sco. Spci_cnt_type, sco. Spci_cnt_no, sex. Ssem_org_id
The from sexportmanifest sex, scontainerinfo sco
Where sex. Ssem_exp_bl_id=sco. Spci_exp_bl_id
) ty
Group by bl_no spci_cnt_size, spci_cnt_type org_id,

CodePudding user response:

Probably like this, I do not know the amount of data, you can't add index,
The UPDATE TMP_YQLOAD_VALID tt
SET the tt. Error_msg=tt. Error_msg | | '; '| | CRH (13) | | CRH (10) | |' error message ',
Tt. Is_operate='N'
WHERE the EXISTS (SELECT 1
The FROM MSC_YQEXPSCON_SUM_VIEW MC,
MSC_YQEXPSCON_VIEW M,
WHERE Margaret spellings ECR_CNT_QUANLITY & gt; MC. CT_NUM
AND Margaret spellings ECR_CNT_TYPE=tt. SPCI_CNT_TYPE box *//*
AND Margaret spellings ECR_CNT_SIZE=tt. SPCI_CNT_SIZE/* */size
ORGID='8883' AND Margaret spellings HSS_ORG_ID/* */
AND Margaret spellings SEM_BL_NO=tt. BL_NO/* SO */
AND MC. BL_NO=tt. BL_NO
AND MC. SPCI_CNT_SIZE=tt. SPCI_CNT_SIZE
AND MC. SPCI_CNT_TYPE=tt. SPCI_CNT_TYPE
AND MC.ORG ORGID _ID='8883'/* */
)
AND tt. STOP_VALID='N' AND (tt) ERROR_MSG IS NULL OR tt) IS_OPERATE='Y') AND tt. BL_STATUS='1'/* 0 (import manifest */

CodePudding user response:

Look at the plan table of assess can optimize the full here

CodePudding user response:

Use the merge into the update?

CodePudding user response:

Exists some build temporary tables

CodePudding user response:

You change the amount of data to estimate is large, suggest using the merge into grammar a try,