All this talk about the server delivering portions of a dataset based on
ROWNUM ranges is nauseating.

AFAIK, InterBase/Firebird does not have the concept of a row number on the
server. This belongs on a client that is actually fetching data and counting
rows as it goes and keeping them in a cache of its own. The server should
not be required to keep hordes of caches around just to spit out small
slices of datasets efficiently. Nor should it be made to re-execute queries
over and over again just to increment through data.

What should be happening is using bookmarks derived from meaningful data in
your table that allows the server to quickly pull records from the next
segment and then it just brings back rows until you don't want any more.
When you want more records you somehow say, this is the new starting point
and I want some more records starting from there. Interbase/Firebird handles
that very well. IBO even automates with its various searching facilities.

By doing it this way you are able to walk a batch of records more
accurately. Row Nums are potentially inaccurate. They are always relative at
best.

e.g. Someone is browsing a web site and they see recs 1-50, while they are
doing that one of the records they are looking at becomes deleted and then
when they page to rows 51-100 the record that used to be 51 is now 50 and
guess what, they never see that record. If instead their request for more
records was based on records after the value for record 50 they would pick
up that record and not miss it. using a snapshot transaction is definitely
not a solution to this problem either.

ROWNUM is simply for lazy programmers who don't really care if their
applications work 100%. I vote for them to be banished from the engine even
if any such semantics are already there. This part of the complexity should
be solved on a middle tier or client. A server's job is to do transactions,
optimize whatever queries I throw at it and keep data integrity.

I suppose if you really want them it is a snap to write select stored
procedures that provide a row num facility...

Jason Wharton
CPS - Mesa AZ
http://www.ibobjects.com