When should you create detailed distribution statistics on indexes in DB2?
DB2 9 auto runstat uses following syntax for generating statistics for your table.
runstats on table t1 with distribution and sampled detailed indexes all
Please note that it is using distribution statistics on the table but only sample detailed statistics on indexes as a full detailed statistics on indexes require a large amount of time and memory for large tables. Sampled detailed index statistics produce almost same quality as non-sampled but with only 5% more overhead than non-detailed.
When you create full detailed statistics on indexes, know what you are doing and consult this DB2 technote.
The experts suggest that you follow this rule of thumb.
For dimension tables:
runstats on table t1 with distribution and
(sampled) detailed indexes all
For facts tables:
runstats on table t1 with (distribution) and indexes all