求sql大神帮忙优化一下这条sql

carson王 发布于 2016/06/22 17:17
阅读 481
收藏 0
delete from mlsbackup.MBLTPCUS c where 
c.MBL_ID_NO in (SELECT a.MBL_ID_NO FROM mlsbackup.MBLTPCUS a,mlstst.MBLTPCUS b where a.MBL_ID_NO = b.MBL_ID_NO and a.MBL_NO = a.MBL_NO and a.MBL_PROV = b.MBL_PROV) 
and c.MBL_NO in (SELECT a.MBL_NO FROM mlsbackup.MBLTPCUS a,mlstst.MBLTPCUS b where a.MBL_ID_NO = b.MBL_ID_NO and a.MBL_NO = a.MBL_NO and a.MBL_PROV = b.MBL_PROV)
and c.MBL_PROV in (SELECT a.MBL_PROV FROM mlsbackup.MBLTPCUS a,mlstst.MBLTPCUS b where a.MBL_ID_NO = b.MBL_ID_NO and a.MBL_NO = a.MBL_NO and a.MBL_PROV = b.MBL_PROV);
加载中
0
slym_yq
slym_yq

找到答案了

https://yq.aliyun.com/ask/30781/?spm=5176.100241.asklist.7.r5ro6I

阿里云上线的云栖社区里面的大牛完美的解决了我的问题,认识了阿里大牛。好棒,里面的技术类的干货真多,问答,优质的博文,让我流连忘返,学习到很多实用的知识,还有一群热爱It的小伙伴们,云栖社区,赞一个!

0
cs_sharp
cs_sharp
where in 可以改成 where exists可以提高效率
0
ahdkk
ahdkk
with t1 as (
SELECT a.MBL_ID_NO
FROM mlsbackup.MBLTPCUS a, mlstst.MBLTPCUS b
WHERE a.MBL_ID_NO = b.MBL_ID_NO AND a.MBL_NO = a.MBL_NO AND a.MBL_PROV = b.MBL_PROV
),
t2 as (
      SELECT a.MBL_NO
      FROM mlsbackup.MBLTPCUS a, mlstst.MBLTPCUS b
      WHERE a.MBL_ID_NO = b.MBL_ID_NO AND a.MBL_NO = a.MBL_NO AND a.MBL_PROV = b.MBL_PROV
),
t3 as (
      SELECT a.MBL_PROV
      FROM mlsbackup.MBLTPCUS a, mlstst.MBLTPCUS b
      WHERE a.MBL_ID_NO = b.MBL_ID_NO AND a.MBL_NO = a.MBL_NO AND a.MBL_PROV = b.MBL_PROV
)
DELETE FROM mlsbackup.MBLTPCUS c
WHERE c.MBL_ID_NO IN (select * from t1)
 AND c.MBL_NO IN (select * from t2)
AND c.MBL_PROV IN (select * from t3);



ahdkk
ahdkk
不要觉得这样写的很罗嗦,数据量非常大的时候就会看到性能的提升了
0
IdleMan
IdleMan

delete from mlsbackup.MBLTPCUS c where 
 exists(select null from  
 mlsbackup.MBLTPCUS a,mlstst.MBLTPCUS b where 
     a.MBL_ID_NO = b.MBL_ID_NO and a.MBL_NO = a.MBL_NO and a.MBL_PROV = b.MBL_PROV 
 and c.MBL_ID_NO=a.MBL_ID_NO
 and c.MBL_NO =a.MBL_NO
 and c.MBL_PROV= a.MBL_PROV)




如果删除的数据量很大且是一次性的执行,建议分批删除

0
jolphin
jolphin
delete from mlsbackup.MBLTPCUS c 
where exists (
select null 
from mlstst.MBLTPCUS b 
where c.MBL_ID_NO=b.MBL_ID_NO
  and c.MBL_NO =b.MBL_NO
  and c.MBL_PROV= b.MBL_PROV);

delete from mlsbackup.MBLTPCUS c 
where (c.MBL_ID_NO,c.MBL_NO,c.MBL_PROV) in (
select b.MBL_ID_NO,b.MBL_NO,b.MBL_PROV 
from mlstst.MBLTPCUS b
where c.MBL_ID_NO=b.MBL_ID_NO
  and c.MBL_NO =b.MBL_NO
  and c.MBL_PROV= b.MBL_PROV);
0
ToBeHJH
ToBeHJH
你可以先去了解一下merge into的特性,使用merge into 来改写就OK了
返回顶部
顶部