Someone earlier thought that the optimizer might use an index if there is a high percentage of deleted records - the index would not have any entries for the deleted records and would, therefore, be faster for getting all the active records.
Now that may have been just a guess but it makes sense - did the message that said the index was used give any further information?
-------------- Original message --------------
From: Luis Rodriguez <luis.rodriguez2@xxxxxxxxx>
I agree (and am very conscious of) with the fact that the only way to guarantee
a result ordered is with the ORDER BY clause.
That said, what I don't really understand is SQL using an index for reporting
the RRN(). One would imagine that kind of things would be better doing a table
IBM Certified Systems Expert
eServer i5 iSeries Technical Solutions
--- On Sat, 7/26/08, midrange-l-request@xxxxxxxxxxxx
date: Sat, 26 Jul 2008 00:13:35 -0400
from: Terrence Enger
subject: Re: SQL - RRN() not in order
Let me remind people--I confess, myself included--that if
results in a specific order, you must specify that order
Even selecting from a keyed logical does not guarantee any
This is hard to remember because it so often happens that
will choose a strategy which returns rows in the
"right" order by