sql优化2:db2关于 in (常量...)优化建议

最新推荐文章于 2023-10-12 08:58:52 发布

njhart2003

最新推荐文章于 2023-10-12 08:58:52 发布

阅读量3.2k

点赞数

分类专栏： db2 文章标签：优化 sql db2 include 数据库测试

本文链接：https://blog.csdn.net/njhart2003/article/details/2696308

版权

本文探讨了一个导致DB2实例宕机的SQL更新语句，其中包含大量的IN常量。建议通过创建包含所需信息的索引或拆分查询以避免全表扫描来优化查询性能。提出了两种方案：一是创建包含查询字段的索引；二是将大查询拆分为多个小查询，利用绑定变量和行级锁定提高性能。

摘要由CSDN通过智能技术生成

我们在设计程序里，经常碰到需要态组装sql条件的情况，例如，下面是从后台抓取的sql:

update MAT_REQPLAN
set GATHERFLAG=1
where 1=1
and REQPLAN_ID in (select distinct REQPLAN_ID
                  from MAT_MATERIAL
  where MATERIAL_ID in (0,2095543,2095544,2095545,2095546,2095547
  ,2095548,2095549,2095550,2095551,2095552,2095553,2095554,2095555,2095556
  ,2095557,2095558,2095559,2095560,2095561,2095562,2095563,2095564,2095565
  ,2095566,2095567,2095568,2095569,2095570,2095571,2095572,2095573,2095574
  ,2095575,2095577,2095578,2095579,2095580,2095581,2095582,2095583,2095584
  ,2095585,2095586,2095589,2095590,2095591,2095592,2095593,2095594,2095595
  ,2095596,2095597,2095598,2095599,2095600,2095601,2095602,2095603,2095604
  ,2095605,2095606,2095607,2095608,2095609,2095610,2095611,2095612,2095613
  ,2095614,2095615,2095616,2095617,2095618,2095619,2095620,2095621,20