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 <[email protected]> To: Derby Discussion <[email protected]> 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 --
