Re: query optimizer revisited - this shouldn't be this hard!

[rick.baird@xxxxxxxxxxxxxxx]

Ken,

I had temporarily forgotten (not unusual) your earlier suggestion. Thanks!
it did the trick nicely.

To all others who answered, thank you as well.

This is a really a topic that I would really like to be further discussed,
as it relates to legacy database systems.  see my question in the archives:

http://archive.midrange.com/midrange-l/200403/msg01438.html

Thanks again,

Rick

------original message-----------
Hi Rick -

>ok, reasonable enough.  so I change the opnqryf and the index to match
each
>other better, while having the same results.
>
>CREATE INDEX HISTRY4LA
>     ON HISTRY4 (HPEROD, HITEM, LMRKTC)
>
>and
>
>OPNQRYF FILE((HISTRY4))
>QRYSLT('HPEROD *EQ "06" *AND HITEM *NE 0 *AND LMRKTC *NE "00"')
>KEYFLD((HPEROD) (HITEM) (LMRKTC))
>
>I ran again, and it says it considered my index, but rejected it because
of
>reason 4.
>   4 - The cost to use this access path, as determined by the optimizer,
was
>higher than the cost associated with the chosen access method.

Try the same thing but with OPTIMIZE(*FIRSTIO) as I suggested previously.

OPTIMIZE(*FIRSTIO) tells the optimizer that you want the first record as
quickly as possible, so it is much less likely to build an access path
itself.

Ken
http://www.ke9nr.net/