1) Yes, you're right, I think the *NEs might have caused the QO to get
confused, even though the selection criteria was included in the keyfields,
in the same order.

2) No, I don't think it did, unless it built a temporary table (or view)
with selection criteria, and then built the index over the tempfile.
I could have missed something in the QO messages - they tend to get wordy -
but I don't think I saw any temporary files being built.
I know that SQL indexes can't specify selection criteria, so I imagine that
the QO can't build them that way either, unless it's using non-SQL access

Ken's suggestion to use OPTIMIZE(*FIRSTIO) caused it to begin reading the
file immediately, with no builds.  This was what I wanted.


----original message-------
1) Keep in mind that you're doing *NE lookups on the last two fields,
not *EQ. Indexes are much less useful on *NE lookups since you're close
to reading all the records anyway.

2) Could the index it did build have included record selection in the
index? I don't know if the optimizer shows that data in its message.


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2022 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.