Re: SQL optimization magic

[Vern Hamberg <vhamberg@xxxxxxxxxxxxxxxxxxxxxxxxx>]

Alexei

Thanks for the info.

The "magic" term should have been quoted - I agree that there really is no
magic. But Joe's original plaint was that he did not know how to get SQL to
do what he wanted, reliably, predictably.

The more we help the optimizer, the better. And there are good suggestions
in the performance appendixes of some manual (different for every release).
But this idea was new to me - I'd never heard it, and it is somewhat
counter-intuitive, I think, to keep maintain an index, esp. on a large
file, for the purpose of NOT using it.. But when I think of it more, it
makes some sense. There's still the tradeoff with index maintenance cost,.

The most gain, as I've seen it, comes in the early stages - savings in
optimization time, in retrieval of the first rows. As you go along with
retrieving a large recordset, a scan is the better choice - but the index
in place will help make that decision sooner. And, in this case, without
the index, the optimizer built one, which takes time, and probably used it,
which might be less efficient than a scan.

Regards

Vern

At 06:31 PM 11/15/02 -0600, you wrote:
> Well, there is no magic.
> I believe it was documented since times immemorial, that optimizer uses
> indexes (access paths) to find statistical information about value
> distribution in the file. And I remember there was a recommendation (maybe
> in a redbook or perhaps SQL class) to build unused indexes over large files
> to help optimizer to make better choice for frequent queries.
> In V5R2, there is a new statistical manager, which can collect statistics
> without indexes:
>
> http://publib.boulder.ibm.com/iseries/v5r2/ic2924/info/rzajq/rzajqmststatsmanager.htm
>
>
>
>     Alexei Pytel
> always speaking for myself