Re: SQL - RRN() not in order

Birgitta,

Thanks for your answer.

You are right, it wouldn't matter if the index belongs to a join file because only the index part is used (had forgotten about that).

That said, this file does not have any deleted records!! Why to go to all the hassle of checking all the possible access paths for getting only the RRN()?

Regards,

Luis Rodriguez

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

message: 8
date: Fri, 25 Jul 2008 20:11:26 +0200
from: "BirgittaHauser"
<>
subject: AW: SQL - RRN() not in order

Hi,

My question is: Why does RRN() needs an index?.

Moreover, the selected
index is a join file(3 Files!!)

With a table scan all rows (including the deleted ones) are
scanned.
If there are a lot of deleted rows in your table, the
optimizer may decide
it is better to skip the deleted rows and use an index scan
instead of a
table scan.

It doesn't matter that the access path lies in a joined
logical file,
because only the access path and not the data are used.

Mit freundlichen Gr??en / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land
among the stars." (Les
Brown)
"If you think education is expensive, try
ignorance." (Derek Bok)
"What is worse than training your staff and losing
them? Not training them
and keeping them!"

Luis Rodriguez

Gesendet: Friday, July 25, 2008 16:11
An: Midrange-L
Betreff: SQL - RRN() not in order


Hi List,

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;
...........................
RRN ( file )
20
471
73
382
593
.
.
.
88
89
90
91
609

Select RRN(file), file.* from FILE;
..................................
RRN ( file ) (Fields...)
1 xxxxx
2 xxxxx
3 xxxxx
4 xxxxx
5 xxxxx
6 xxxxx
etc.

Even this sorts the output by RRN():
Select RRN(file) from FILE WHERE RRN(file) < 700;

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?


Regards,

Luis Rodriguez


IBM Certified Systems Expert
eServer i5 iSeries Technical Solutions