1同行回答
回复 1# vincenthuang
以DB2 V9.7为例，在runstats中与索引相关的参数及作用：
SAMPLED-索引取样：
This option, only used with the DETAILED option, directs RUNSTATS to use a sampling technique when compiling the extended index statistics. When the sampling option is not used, detailed index statistics collection can consume considerable CPU and memory for large tables. The SAMPLED option provides detailed index statistics with nearly the same accuracy but requires less CPU and memory.
DETAILED-扩展信息：
Calculates extended index statistics. These are the CLUSTERFACTOR and PAGE_FETCH_PAIRS statistics that are gathered for relatively large indexes. This option cannot be used for views.
INDEXES：
是部分索引还是全部索引
ON KEY COLUMNS-只对索引列收集列信息及分布信息：
Instead of listing specific columns, you can choose to collect statistics on columns that make up all the indexes defined on the table. It is assumed here that critical columns in queries are also those used to create indexes on the table. If there are no indexes on the table, it is as good as an empty list and no column statistics will be collected. It can be used in the On Cols clause or the On Dist Cols clause. It is redundant in the On Cols clause if specified in both clauses since the WITH DISTRIBUTION clause is used to specify collection of both basic and distribution statistics. XML type columns are by definition not a key column and will not be included for statistics collection by the ON KEY COLUMNS clause. This option cannot be used for views.
以上结合业务需求及性能要求来选择。
如果表特别大，可以考虑索引取样，在精度损失有限的情况下加快收集索引的统计信息，降低对系统资源的消耗；
如果索引特别多，但时间窗口有限，考虑错开对索引收集统计信息。
为降低收集统计的整合时间，可以考虑对索引字段及其他谓词字段收集列统计信息及分布信息，还可以调整索引列的NUM_FREQVALUES、NUM_QUANTILES以达到查询要求（注意与性能消耗基本成正比）
更多runstats信息，参见：http://www-01.ibm.com/support/kn ... 9.7.0%2F3-6-2-4-108收起
以DB2 V9.7为例，在runstats中与索引相关的参数及作用：
SAMPLED-索引取样：
This option, only used with the DETAILED option, directs RUNSTATS to use a sampling technique when compiling the extended index statistics. When the sampling option is not used, detailed index statistics collection can consume considerable CPU and memory for large tables. The SAMPLED option provides detailed index statistics with nearly the same accuracy but requires less CPU and memory.
DETAILED-扩展信息：
Calculates extended index statistics. These are the CLUSTERFACTOR and PAGE_FETCH_PAIRS statistics that are gathered for relatively large indexes. This option cannot be used for views.
INDEXES：
是部分索引还是全部索引
ON KEY COLUMNS-只对索引列收集列信息及分布信息：
Instead of listing specific columns, you can choose to collect statistics on columns that make up all the indexes defined on the table. It is assumed here that critical columns in queries are also those used to create indexes on the table. If there are no indexes on the table, it is as good as an empty list and no column statistics will be collected. It can be used in the On Cols clause or the On Dist Cols clause. It is redundant in the On Cols clause if specified in both clauses since the WITH DISTRIBUTION clause is used to specify collection of both basic and distribution statistics. XML type columns are by definition not a key column and will not be included for statistics collection by the ON KEY COLUMNS clause. This option cannot be used for views.
以上结合业务需求及性能要求来选择。
如果表特别大，可以考虑索引取样，在精度损失有限的情况下加快收集索引的统计信息，降低对系统资源的消耗；
如果索引特别多，但时间窗口有限，考虑错开对索引收集统计信息。
为降低收集统计的整合时间，可以考虑对索引字段及其他谓词字段收集列统计信息及分布信息，还可以调整索引列的NUM_FREQVALUES、NUM_QUANTILES以达到查询要求（注意与性能消耗基本成正比）
更多runstats信息，参见：http://www-01.ibm.com/support/kn ... 9.7.0%2F3-6-2-4-108收起