Re: SQL - RRN() not in order

[CRPence <CRPbottle@xxxxxxxxx>]

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

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

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 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.
>
> > 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 FILE;
> > >
> > > <<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?