Thanks. That makes a lot of sense.


Luis Rodriguez

IBM Certified Systems Expert
eServer i5 iSeries Technical Solutions

--- On Wed, 7/30/08, midrange-l-request@xxxxxxxxxxxx <midrange-l-request@xxxxxxxxxxxx> wrote:

message: 3
date: Wed, 30 Jul 2008 10:53:56 -0500
from: CRPence <>
subject: Re: SQL - RRN() not in order

The index may be used when either large number of
deleted rows exist
or when the amount of memory to page is easily managed by
index vs the
dataspace. That is because the index has the RRN [or RRN
can be easily
calculated from], and that index can be more efficiently
paged than the
entire dataspace with a full table scan. So when all of
the information
is available in an index which is smaller than the data, it
can be much
more efficient to use the index instead, especially when
there is no

Note: To further clarify on the Join Logical File
index... There is
not just one index for all three files. In the three file
join, the
database creates the primary index as declared in the DDS,
and then
creates additional indexes on the second and third file
which assist in
effecting matches for join and selection. Given no
selection, each
index created on each file is much like a CREATE INDEX
which is used for
implementation of a dynamic join when the JLF is opened;
just that there
is not a separate object, to which the index can be

Regards, Chuck

Luis Rodriguez wrote:

I agree (and am very conscious of) with the fact that
the only way to
guarantee a result ordered is with the ORDER BY

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.

Terrence Enger wrote:

Let me remind people--I confess, myself
included--that if
you want results in a specific order, you must
specify that order
explicitly. <<SNIP>>

Luis Rodriguez wrote:

Running some tests (V5R3) I found this
strange (at least for me)
results with SQL RRN: (FILE is a (DDS) file
with no index and
zero deleted records) Select RRN(file) from

<<SNIP unordered SELECT RRN(A) vs
ordered SELECT RRN(A),A.* >>

Running the sentence under debug (STRDBG)
shows that SQL decided
to use an index (LF). My question is: Why does
RRN() needs an
index?. Moreover, the selected index is a join
file(3 Files!!)
This is not giving any problem for me right
now, I'm just curious
why this is so. Any ideas?


This thread ...

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