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,2095622
,2095623,2095624,2095625,2095626,2095627,2095628,2095629,2095630,2095631
,2095632,2095633,2095634,2095635,2095636,2095637,2095638,2095639,2095640
,2095641,2095642,2095644,2095645,2095646,2095647,2095648,2095649,2095650
,2095651,2095652,2095653,2095656,2095657,2095658,2095659,2095660,2095661
,2095662,2095663,2095664,2095665,2095666,2095667,2095668,2095669,2095670
,2095671,2095672,2095673,2095674,2095675,2095676,2095677,2095678,2095679
,2095680,2095681,2095682,2095683,2095684,2095685,2095686,2095687,2095688
,2095689,2095690,2095691,2095692,2095693,2095694,2095695,2095697,2095698
,2095699,2095700,2095701,2095703,2095706,2095709,2095711,2095712,2095713
,2095714,2095715,2095716,2095718,2095719,2095720,2095721,2095722,2095723
,2095724,2095725,2095726,2095727,2096396,2096397,2096398,2096399,2096400
,2096401,2096402,2096403,2096404,2096405,2096406,2096407,2096408,2096409
,2096410,2096411,2096412,2096413,2096414,2096415,2096392,2096393,2096394
,2096395,2096416,2096417,2096418,2096419,2096420,2096421,2096423,2096424
,2096425,2096426,2096427,2096428,2096429,2096430,2096431,2096432,2096433
,2096434,2096435,2096436,2096437,2096438,2096439,2096440,2096441,2096442
,2096443,2096444,2096445,2096446,2096447,2096448,2096449,2096450,2096451
,2096452,2096453,2096454,2096455,2096456,2097118,2097119,2097120,2097121
,2097122,2097123,2097124,2097125,2097126,2097128,2097131,2097132,2097133
,2097134,2097136,2097137,2097138,2097139,2097140,2097141,2097143,2097144
,2097147,2097148,2097149,2097150,2097151,2097152,2097153,2097154,2097155
,2097156,2097158,2097159,2097160,2097161,2097162,2097163,2097293,2097294
,2097295,2097297,2097298,2097299,2097301,2097302,2097303,2097304,2097305
,2097306,2097307,2097309,2097310,2097312,2097315,2097316,2097317,2097318
,2097319,2097320,2097321,2097322,2097323,2097324,2097325,2097326,2097327
,2097328,2097329,2097330,2097331,2097332,2097333,2097335,2097336,2097337
,2097403,2097404,2097405,2097406,2097407,2097409,2097410,2097411,2097412
,2097413,2097414,2097416,2097417,2097418,2097419,2097420,2097421,2097422
,2097423,2097424,2097425,2097426,2097427,2097428,2097429,2097430,2097431
,2097432,2097433,2097434,2097435,2097436,2097437,2097438,2097439,2097440
,2097441,2097442,2109452,2109453,2109456,2109457,2109460,2109461
)
);
这个sql比较变态，已经把实例搞宕机了，调整数据库参数后，可以执行，但性能较差，原因是没有使用表MAT_MATERIAL中字段MATERIAL_ID上面的索引，而使用全表扫描。
优化的思路：尽量创造条件，使优化器使用索引，避免全表扫描。
方案一：
以全索引扫描代替全表扫描，即使得索引含有查询所需的全部信息，建索引如下：
create unique index MAT_MATERIAL_I10 on MAT_MATERIAL(MATERIAL_ID) include (REQPLAN_ID);
这是我一再建议的，主键创建时，尽量多include几个经常查询的字段。
方案二：
方案一实际是因地制宜，比较讨巧的做法，当查询条件改变时，如果不是查REQPLAN_ID字段，那么就要创建另外索引，这对于动态查询是不太现实的，不可能在每个字段上加索引。
而对于这种动态改变查询条件的场景，我推荐一个比较通用的做法：化整为零，即把这些条件拆成单个查询！
可能好多同事要惊诧了，这是什么优化？！在sql开发过程中一般的思路，是能用一sql解决问题的就尽量用一条语句解决。
但我在实际调优过程中，发现这一思路不全对，要分情况讨论，特别是DB2！
来看优化后的语句：
for x as select MATERIAL_ID from tmp do -- 在程序里可以用数组绑定变量，注意，一定要绑定变量
set i=x.MATERIAL_ID;update MAT_REQPLAN
set GATHERFLAG=1
where 1=1
and REQPLAN_ID in (
select REQPLAN_ID
from MAT_MATERIAL
where MATERIAL_ID =i
);
end for;测试执行时间，ID=1为优化后的执行时间：
ID 2
------- ----------------------
1. 0.312000
2. 0.687999
这样的sql性能提高一倍多！
原因就是使用了索引，这样做的好处还有：
1、使用了绑定变量，对于不同选择结果，执行语句是一样的，不再重新解析；
2、使用了表MAT_MATERIAL中字段MATERIAL_ID上面的索引
3、避免表扫描，锁定粒度变为行级