Re: Optimizer NOT using available indexes

[Richard Theis <theis_richard@xxxxxxxxxx>]

Antonio,
I think the detailed answer from Vernon is relevant, but I would like to
add my 2 cents.
You are under the impression that the optimizer has not used your access
pathes.  It's not true: the optimizer has used them to collect information,
and with this information the conclusion was to not use them to retrieve
the data.
You are also under the impression that using an existing access path to
retrieve the data is always a winning strategy.  In the good ol' days, it
was often the case: the processor was slow (VERY slow), the caches (if any)
small, the main memory, microscopic.
Now, the processors (often a bunch of) are fast (VERY fast), there are
plenty of caches everywhere (at processor, job, system,  pool, IOP, ...
level), the main memory is huge.  The optimizer is also much more
sophisticated, and has access to additional statistics, especially in
V5R2/R3.  In many cases now, the good strategy is a very simple one: BRUTE
FORCE.
The Vernon's suggestion of EVIs is a good one, it is as efficient with AND
as with OR: in most cases, if used, the optimizer derives bitmaps from EVIs
and cross them to find the RRNs of the selected records. Also, an EVI
gather more detailed statistics about the key values. You could also look
at the statistics thru iSeries Navigator.
Bonne réception/Best regards
Richard THEIS
  iSeries Education, France