MIDRANGE dot COM Mailing List Archive



Home » MIDRANGE-L » July 2008

Re: SQL - RRN() not in order



fixed

**Terrence,
I tried this with other (bigger) tables and the optimizer used the PF directly (sequential access).

If I choose the ORDER BY RRN() option the optimizer creates a new access path (CPI4321)

**Vern,
This file has zero deleted records. The optimizer gives a
CPI4328 - Access path of file &n was used by query

And as a reason the code 1 – Record Selection.

Who knows? As stated before, this is not really a problem, just being curios ("inquisitive mind wants to know"?). I think I'll try to drop this on IBM, just for kicks.

Thanks to all,
Luis


------------------------------

message: 9
date: Sun, 27 Jul 2008 05:12:57 +0000
from: vhamberg@xxxxxxxxxxx
subject: Re: SQL - RRN() not in order

Luis

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?

Vern

-------------- Original message --------------
From: Luis Rodriguez <luis.rodriguez2@xxxxxxxxx>

Terrence,

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
scan.

Regards,

Luis Rodriguez

IBM Certified Systems Expert
eServer i5 iSeries Technical Solutions


--- On Sat, 7/26/08, midrange-l-request@xxxxxxxxxxxx
wrote:

------------------------------

message: 2
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
you want
results in a specific order, you must specify
that order
explicitly.
Even selecting from a keyed logical does not
guarantee any
particular
sequence.

This is hard to remember because it so often
happens that
the system
will choose a strategy which returns rows in the
"right" order by
accident. Sigh!

Cheers,
Terry.

------------------------------









Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2014 by MIDRANGE dot 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 here. If you have questions about this, please contact