db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Geoff hendrey <geoff_hend...@yahoo.com>
Subject Re: The result offset and fetch first clauses
Date Sun, 03 May 2009 16:33:51 GMT
Hi Alan,

Yes, sounds like what we are doing is nearly identical. The reason for traversing the result
set once, is to create a set of page boundaries that can support a web application that needs
to build a set of links, like an index into a user directory (like the way LinkedIn has alphabetical
links for all your connections). A few definitions for the query below:

orderColumn is the name of the column on which we want to order. 
startAfterVal is a value for the orderColumn representing a "page boundary"
The JDBC driver is set to return a maximum number of results equal to the desired page size.

for ascending ordering:
...WHERE (orderColumn = startAfterVal AND PK > startAfterPK) OR orderColumn > startAfterVal
ORDER BY orderColumn ASC, PK ASK

for descending queries:
...WHERE (orderColumn = startAfterVal AND PK < startAfterPK) OR
orderColumn < startAfterVal ORDER BY orderColumn DESC, PK DESC

The portion of the query that says "(orderCOlumn = startAfterVal AND PK [<|>] startAfterPK)"
insures consistent scroll ordering in the following case:

imagine you have a very large user directory table with thousands of rows with a LASTNAME
column equal to "SMITH". Then you excecute the query above, using LASTNAME as the orderColumn.
The afformentioned portion of the query insures that as you page forward and backward you
are not getting random SMITH rows, but rather the same SMITH rows in identical order. Very
important for any application like a phone book or user directory.

 

 -geoff
“XML? Too much like HTML. It'll never work on the Web!” 
-anonymous 





________________________________
From: Alan Burlison <Alan.Burlison@sun.com>
To: Derby Discussion <derby-user@db.apache.org>
Sent: Sunday, May 3, 2009 2:45:48 AM
Subject: Re: The result offset and fetch first clauses

Geoff hendrey wrote:

> Get the result set. Use a loop to increment integer n by PAGE_SIZE,
> and inside the loop use ResultSet.absolute(n) combined with
> stmt.setFetchSize(1) to retrieve a "marker" row that signifies the
> begining of each "page" of the result set. I use the primary keys of
> these "markers" as page boundaries so that my web application can
> provide links to a set of pages evenly distributes throughout the
> result set.

I use something similar, except instead of traversing the entire result set and storing keys
for each 'page' I retain the keys of the first and last rows in the current 'page'.  For subsequent
fetches I use '> lastKey ... order by ... asc' to scroll forwards and '< firstKey ...
order by ... desc' to scroll backwards.

I too would be interested to know how that approach compares to the new offset/fetch clauses.

-- Alan Burlison
--

Mime
View raw message