現在有個SQL跑了3.5秒，想優化到1秒以內
update TMP_YQLOAD_VALID tt
set tt.error_msg = tt.error_msg || ';' || chr(13) || chr(10) || '錯誤資訊',
tt.is_operate = decode('N', 'Y', 'Y', 'N')
where exists (SELECT 1
FROM MSC_YQEXPSCON_SUM_VIEW MC,
MSC_YQEXPSCON_VIEW M,
TMP_YQLOAD_VALID T /*臨時表*/ /*出口約橋預裝載視圖*/
WHERE M.SECR_CNT_QUANLITY > MC.CT_NUM
AND (T.ERROR_MSG IS NULL OR T.IS_OPERATE = 'Y')
and TT.ROWID = T.ROWID
and M.SECR_CNT_TYPE = T.SPCI_CNT_TYPE /*箱型*/
AND M.SECR_CNT_SIZE = T.SPCI_CNT_SIZE /*尺碼*/
AND M.SHSS_ORG_ID = '8883' /*ORGID*/
AND M.SSEM_BL_NO = T.BL_NO /*SO號*/
AND MC.BL_NO = T.BL_NO
AND MC.SPCI_CNT_SIZE = T.SPCI_CNT_SIZE
AND MC.SPCI_CNT_TYPE = T.SPCI_CNT_TYPE
AND MC.ORG_ID = '8883' /*ORGID*/
AND T.BL_STATUS = '1' /*0代表進口艙單*/
)
and tt.STOP_VALID = 'N'
uj5u.com熱心網友回復：
create or replace view msc_yqexpscon_sum_view asselect org_id,ty.bl_no,ty.spci_cnt_size,ty.spci_cnt_type,count(distinct ty.spci_cnt_no) as ct_Num
from (select ty.bl_no, ty.spci_cnt_size,ty.spci_cnt_type, ty.spci_cnt_no,ty.org_id
from TMP_YQLOAD_VALID ty
union
select sex.ssem_bl_no,sco.spci_cnt_size,sco.spci_cnt_type,sco.spci_cnt_no,sex.ssem_org_id
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;
uj5u.com熱心網友回復：
大概這個樣子吧，也不知道你的資料量，也沒法加索引。UPDATE TMP_YQLOAD_VALID tt
SET tt.error_msg = tt.error_msg || ';' || chr(13) || chr(10) || '錯誤資訊',
tt.is_operate = 'N'
WHERE EXISTS (SELECT 1
FROM MSC_YQEXPSCON_SUM_VIEW MC,
MSC_YQEXPSCON_VIEW M,
WHERE M.SECR_CNT_QUANLITY > MC.CT_NUM
AND M.SECR_CNT_TYPE = tt.SPCI_CNT_TYPE /*箱型*/
AND M.SECR_CNT_SIZE = tt.SPCI_CNT_SIZE /*尺碼*/
AND M.SHSS_ORG_ID = '8883' /*ORGID*/
AND M.SSEM_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_ID = '8883' /*ORGID*/
)
AND tt.STOP_VALID = 'N' AND (tt.ERROR_MSG IS NULL OR tt.IS_OPERATE = 'Y') AND tt.BL_STATUS = '1' /*0代表進口艙單*/
uj5u.com熱心網友回復：
看看計劃中table assess full 這里能不能優化呢uj5u.com熱心網友回復：
用merge into update 呢？uj5u.com熱心網友回復：
exists部分構建臨時表uj5u.com熱心網友回復：
你這個更改的資料量估計比較大，建議用merge into語法試試。轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/29778.html
標籤：開發