现在有个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'
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'
解决方案 »
- 【急】怎样优化这条sql
- 关于oracle表空间的问题...
- 一句SQL求解
- 导入dmp时失败,提示:未启用partitioning特性,如何解决?
- 原始排序问题!
- 在ORACLE 8.1.7中如何将一个用户数据库复制成另一个用户数据库.
- 我在安装完ora92要配置管理服务器,在创建一个新的资料档案库时有个错误!
- 急急急急急!!!!!!大家好,请大家帮一下,在表空间里那么多表,到底在哪个表里建立啊,请高手指点迷经
- 这个链接字符串的语句为什么不对?
- 请教SQL Server中SQL语句在Oracle中的写法
- 单线程导出,多线程导入疑问
- oracle中merge into查询越来越慢,大家知道什么原因吗,
select 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;
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代表进口舱单*/