--- In IBDI@y..., "Jason Wharton" <jwharton@i...> wrote:

> All this talk about the server delivering portions of a dataset

based on

> ROWNUM ranges is nauseating.

Are arrays that are accessed by indexes nauseating too? Should one
use linked lists instead and iterate through the list from the
beginning to fetch the Nth element?

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

So only tables are needed for safe storage of data? If we need only a
subset of the data on the table we use SELECT * FROM THETABLE then
process the dataset to choose the rows that we really need and sort
them according to any complex criteria?

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

>

So each time one record is deleted form the table, we recalculate the
bookmarks for all the records that would come after the deleted
record in all the ordered views that we use in our application and
update the auxiliary fields that we have added to our tables for this
purpose? If we add new views of data in the future, we add new
auxiliary fields to the tables as needed? Of course our database is
not normalised and we are not joining three or four tables in a query.

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

What if he typically starts by browsing record 2900 or so (as one
does when viewing the message index in a Yahoo group such as this to
see the list of the more recent messages)? Programmers shouldn't be
trusted to choose the right tools for the job?

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

Are intrepid programmers supposed to reinvent the wheel for each new
application? ROWNUM is simply the index within a dataset and has its
uses as other posters have described. You have mentioned one possible
misuse of fetching subsets of a dataset. David Jencks pointed out in
the follow-up to your message that the programmer should not rely on
a sorting order decided by the RDBM. This can always be avoided with
a "safety net" ORDER BY clause on a unique field.

My problem with your approach is you underestimate the complexity of
writing and maintaining middleware. You maintain that transactions
are a desirable feature for an RDBM when it is possible to write
transactional middleware without the RDBM supporting transactions.
The same logic applies to user requests for selective fetching of
rows. Most web sites need only lightweight session objects (if any)
for authentication purposes. The amount of memory and processing
resources used by middleware is not less than what would be needed if
the database had to do a lot of extra work.

Partial query results, alongside case-independent sorting of results,
is a must-have for wide deployment of any RDBM on the web. If it
cannot be achieved with the available human resources, it's no ones
fault. But certainly users should not face gratuitous name-calling
for demanding it.

Fred Toussi