Oppolzer - Informatik / Blog

D.N. wrote:

Bernd,

I would suggest not doing it. The majority of the time the optimizer is going to
pick the best access path based off of the correct information. On the small
percent of queries that you have problems with after that , then you may want to
think about different tricks, such as concatenating a space or adding 0. If all
that fails and you feel it would be faster if some other access were used, then
try playing with the stats. If you get the access path you were after, then put
an opthint into your plan_table, undo the changes you made to the statistics and
rebind using your opthint. You should, hopefully still have the access path you
wanted and the optimizer can continue doing its job correctly. If you're app is
all dynamic SQL, I suggest rewriting the sql to get the access you were after
rather than keeping false stats.

D.N.

>>> Bernd.Oppolzer@T-ONLINE.DE 1/10/03 11:15:19 AM >>>
Hello DB2 experts,

in our site, one of the DBAs does manipulate the statistic values of SYSINDEXES
in a regular manner; he puts 0.99 in the column CLUSTERRATIO of every non
unique index.

We observed that DB2 uses sequential prefetch with an access over one of these
indexes, where, in our opinion, list prefetch should be used. The real
clusterratio value, as computed by RUNSTATS, is 0.67.

The SELECT needed 40 seconds; a similar access with list prefetch involved due
to a multiple index scan needed only 4 seconds.

What do you think of this manipulation of CLUSTERRATIO ? Any suggestions are
welcome.

Regards

Bernd