But obviously some sort of access path exists over RRN, as RPG can chain
(quickly) via RRN.
DDS is not SQL and native I/O is not SQL either!
Accessing a record with RRN will be the fastest access with native I/O.
Accessing a record with RRN and SQL will be the slowest access, because
always a table scan will be used.
Oracle has the ROWID pseudo-column, which allows fast access to a
particular row.
The IBM i has the ROWID() function, but it doesn't work the same.
It would appear that DB2 for z/OS support a ROWID() like Oracle's.
Relative record no is special to DDS and System i. (And special enhancements
for System i or DDS will be rarely integrated into SQL!)
In SQL DDL for I, it is possible to create identity columns which is an
existing column in a table which's will be automatically generated as soon
as a row will be inserted (independent if the insert occurs with SQL or
native I/O or even UPDDTA).
It is also possible to generate a unique key constraint or a primary key
constraint over the identity column and use the column for referential
integrities.
It is also possible to define a column with data type ROWID, which's value
will also be generated automatically at insert time, but contrary to an
identity column there is no guarantee that the ROWID is generated in an
ascending sequence.
Neither an identity column nor a column with data type ROWID can be
generated with DDS and with RPG you have no native data type for ROWID.
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: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] Im
Auftrag von Charles Wilt
Gesendet: Friday, 09. January 2009 19:07
An: RPG programming on the IBM i / System i
Betreff: Re: RRN and Database Files
On Fri, Jan 9, 2009 at 12:32 PM, Birgitta Hauser <Hauser@xxxxxxxxxxxxxxx>
wrote:
For reasons unknown, the query engine on the i will do a full table scan
when you have RRN in the where clause of a statement.
A full table scan will be used, because indexes (or access paths) can only
be created over existing columns within the based table.
RRN is no "real" column and therefore no index can be used.
Even in release 6.1. where derived indexes, where new columns can be
defined
can be created it is not possible to create an index over the RRN.
But obviously some sort of access path exists over RRN, as RPG can
chain (quickly) via RRN.
Oracle has the ROWID pseudo-column, which allows fast access to a
particular row. The IBM i has the ROWID() function, but it doesn't
work the same. It would appear that DB2 for z/OS support a ROWID()
like Oracle's.
MS SQL Server doesn't support RRN or ROWID.
Not saying that use use is a good idea. But if RRN() is going to be
there, I see no reason why it shouldn't return a single row quickly.
Charles
midrange.com
