互联网服务
索引是否会使用的问题
如果我pckcachesz设置的很大，然后第一次的时候，我全表扫描去查找符合要求的东西，如select * from XXX where XXX.aaa='bbb'，那么这条SQL的执行计划就被缓存了。然后我建立了一个索引，并且我知道用这个索引的访问代价远低于全表扫描，但是这个时候DB2去执行的时候，会的走索引吗？毕竟这条sql的全表扫描的执行计划被缓存了。
7同行回答
关于什么时候SQL会被重新编译这个问题，可以参考 Serge Rielau (瑞赛奇) 的回答。他是DB2 优化器的架构师，应该是比较权威的：
There are four classes of changes that cause recompilation of a query:
1) Something disappeared or substantially changed which was used in the query ,
For example a table or used function was recreated, or an index that is being used has been dropped. This case applies to regular static as well and would cause a rebind of the entire package or even a recompile of the SQL routine as a whole.
2) Something was added to an object that required "maintenance" in every using query.
This example would include a new trigger or a new RI constraint. In a new INSERT trigger for example every INSERT statement must be recompiled to pick up the callout to the trigger. Again this is mandatory and affects regular static SQL as well
3) A "goody" was added which may or may not be beneficial for the query plan. This includes a new index, stats view or runstats execution. The query isn't broken here. REOPT(ONCE) queries, VALIDATE RUN and dynamic SQL is affected by this. Static SQL will ignore such changes following a "If it ain't broken, don't fix it" philosophy.
4) The package cache was too small and the (dynamic, REOPT(ONCE) validate run query has been pushed out or db2 was recycled.
原始链接在这里：
https://www.ibm.com/developerwor ... pl?lang=en#comments
你的情况符合第三种：
A "goody" was added which may or may not be beneficial for the query plan.
瑞赛奇说加了一个index后静态的SQL不会重编译，动态的会。收起
There are four classes of changes that cause recompilation of a query:
1) Something disappeared or substantially changed which was used in the query ,
For example a table or used function was recreated, or an index that is being used has been dropped. This case applies to regular static as well and would cause a rebind of the entire package or even a recompile of the SQL routine as a whole.
2) Something was added to an object that required "maintenance" in every using query.
This example would include a new trigger or a new RI constraint. In a new INSERT trigger for example every INSERT statement must be recompiled to pick up the callout to the trigger. Again this is mandatory and affects regular static SQL as well
3) A "goody" was added which may or may not be beneficial for the query plan. This includes a new index, stats view or runstats execution. The query isn't broken here. REOPT(ONCE) queries, VALIDATE RUN and dynamic SQL is affected by this. Static SQL will ignore such changes following a "If it ain't broken, don't fix it" philosophy.
4) The package cache was too small and the (dynamic, REOPT(ONCE) validate run query has been pushed out or db2 was recycled.
原始链接在这里：
https://www.ibm.com/developerwor ... pl?lang=en#comments
你的情况符合第三种：
A "goody" was added which may or may not be beneficial for the query plan.
瑞赛奇说加了一个index后静态的SQL不会重编译，动态的会。收起
走索引收起
浏览944
会走索引，如果DDL有变动，package cache中相关的旧的编译好的包，会失效，并重新生成。收起
浏览916