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!"
-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von 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
midrange.com
