Re: Index advisor and system cross-reference file

There's this little thought in the back of my head whispering something
about temporary indexes the system creates on the fly and actually uses
them. This may be somewhere between using an existing index and
generating a full table scan. But anyway, I digress. Now I remember
where I was going. Turning off the suggestions may not really help
performance since it may have to do all this anyway. Sure you may have
existing indexes but if they don't meet the criteria it can't use them.
The criteria may change based on your where by clause and your order by
clause (and possibly others). So it may have to do all this evaluation
anyway to decide whether or not to use an existing index or do a full
table scan. Of which I suspect can be a real whammy on a large QADBIFLD.
The advisor may simply be a "Well, I did all this work already; I may as
well tell you the index I've already determined I need or created on the
fly". But I don't know enough to give a definitive answer.
So while some may decry that the minimal performance offered by turning
off the advisor is like thinking that a step ladder does, technically,
make you closer to the moon; I can't say whether or not you're on to a
real performance thing.

I still think the better advantage may come from restructuring your system
catalog requests to use existing indexes, or PMR IBM to add the
appropriate index to the system cross reference.



Rob Berendt