Return-Path: Delivered-To: apmail-db-derby-user-archive@www.apache.org Received: (qmail 3495 invoked from network); 3 May 2009 16:34:22 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.3) by minotaur.apache.org with SMTP; 3 May 2009 16:34:22 -0000 Received: (qmail 36304 invoked by uid 500); 3 May 2009 16:34:22 -0000 Delivered-To: apmail-db-derby-user-archive@db.apache.org Received: (qmail 36243 invoked by uid 500); 3 May 2009 16:34:22 -0000 Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: List-Id: Reply-To: "Derby Discussion" Delivered-To: mailing list derby-user@db.apache.org Received: (qmail 36234 invoked by uid 99); 3 May 2009 16:34:21 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Sun, 03 May 2009 16:34:21 +0000 X-ASF-Spam-Status: No, hits=2.2 required=10.0 tests=HTML_MESSAGE,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (athena.apache.org: local policy) Received: from [68.142.206.168] (HELO web31815.mail.mud.yahoo.com) (68.142.206.168) by apache.org (qpsmtpd/0.29) with SMTP; Sun, 03 May 2009 16:34:13 +0000 Received: (qmail 77172 invoked by uid 60001); 3 May 2009 16:33:51 -0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s1024; t=1241368431; bh=Kd7KdbD/3UAgQUDTcoydwHi6heWNkiBDVWcAeX9eLBs=; h=Message-ID:X-YMail-OSG:Received:X-Mailer:References:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type; b=mcvizIQg8bLO8CUWU1sUao9crIf4Y9gIovs6UFtG8rUUYmpYD5nJ+dZsXa006Kfyqh89iEF0Bx//tCCd3sYriBMVDyjrWUs69chtA8Wj6g/dWWCxZhF6XbKgEwnomadiyI3iu0Ml/pgu5PJlpACkyX5H70kdqAvsMuTjF+3UdJ4= DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=Message-ID:X-YMail-OSG:Received:X-Mailer:References:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type; b=pWb8gWMZ5Ft3QDPRaXxSm5AzBTcqAH3sXk1JTgGLFgQWYyISaEEfOszBN/1Lc3gWc8bJYbVwL3uwmM3mbC6DQ2+OV9fhtLQcewG4hXDx3u8NEo8GTBZTic4E8kP2MJnIhndCz34n/sVcYC5D0BlGZCeHzTuV4HtCTJWuUrmgv0w=; Message-ID: <381839.73382.qm@web31815.mail.mud.yahoo.com> X-YMail-OSG: wuDepucVM1mafjtj7F.35_TktSLBpT.ulZAsMfR4AxcN0BTnsGJatTaHbqlJEVzIxiqlatzk__BgNhrj3MSV1yHKnj8cIGE3N7VYHVVxE399.1Sdf_OqYV5H8nOVubu1COOg.eH3EI6xF3Wge3TN2dna15mB.XonfwZjIVhL3A9iBwceFbscEwHLTRrztEqiMXzGj.V9C7Iif8O5RKTJOC748rC73D6T31kfxwH1UgY49Em1Ar4tQkEszRW3xTrjAr5G7_S13hXhNXFGRlMCawx4xdrYfVf5ySffqGwZGVAdmuQzcB4KNFNSOBjg3BAJ5G8ZHRlrcTi4zz194sEMlGPw5IHf5caPA1l1qw-- Received: from [64.81.244.91] by web31815.mail.mud.yahoo.com via HTTP; Sun, 03 May 2009 09:33:51 PDT X-Mailer: YahooMailRC/1277.43 YahooMailWebService/0.7.289.11 References: <493507.15988.qm@web31814.mail.mud.yahoo.com> <49FD67CC.9000906@sun.com> Date: Sun, 3 May 2009 09:33:51 -0700 (PDT) From: Geoff hendrey Subject: Re: The result offset and fetch first clauses To: Derby Discussion In-Reply-To: <49FD67CC.9000906@sun.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-1225137645-1241368431=:73382" X-Virus-Checked: Checked by ClamAV on apache.org --0-1225137645-1241368431=:73382 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable Hi Alan,=0A=0AYes, sounds like what we are doing is nearly identical. The r= eason for traversing the result set once, is to create a set of page bounda= ries 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 alphabetica= l links for all your connections). A few definitions for the query below:= =0A=0AorderColumn is the name of the column on which we want to order. =0As= tartAfterVal is a value for the orderColumn representing a "page boundary"= =0AThe JDBC driver is set to return a maximum number of results equal to th= e desired page size.=0A=0Afor ascending ordering:=0A...WHERE (orderColumn = =3D startAfterVal AND PK > startAfterPK) OR orderColumn > startAfterVal ORD= ER BY orderColumn ASC, PK ASK=0A=0Afor descending queries:=0A...WHERE (orde= rColumn =3D startAfterVal AND PK < startAfterPK) OR=0AorderColumn < startAf= terVal ORDER BY orderColumn DESC, PK DESC=0A=0AThe portion of the query tha= t says "(orderCOlumn =3D startAfterVal AND PK [<|>] startAfterPK)" insures = consistent scroll ordering in the following case:=0A=0Aimagine you have a v= ery large user directory table with thousands of rows with a LASTNAME colum= n equal to "SMITH". Then you excecute the query above, using LASTNAME as th= e 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.=0A=0A =0A=0A -geoff=0A=E2=80=9CXML? T= oo much like HTML. It'll never work on the Web!=E2=80=9D =0A-anonymous =0A= =0A=0A=0A=0A=0A________________________________=0AFrom: Alan Burlison =0ATo: Derby Discussion =0ASent= : Sunday, May 3, 2009 2:45:48 AM=0ASubject: Re: The result offset and fetch= first clauses=0A=0AGeoff hendrey wrote:=0A=0A> Get the result set. Use a l= oop to increment integer n by PAGE_SIZE,=0A> and inside the loop use Result= Set.absolute(n) combined with=0A> stmt.setFetchSize(1) to retrieve a "marke= r" row that signifies the=0A> begining of each "page" of the result set. I = use the primary keys of=0A> these "markers" as page boundaries so that my w= eb application can=0A> provide links to a set of pages evenly distributes t= hroughout the=0A> result set.=0A=0AI use something similar, except instead = of traversing the entire result set and storing keys for each 'page' I reta= in the keys of the first and last rows in the current 'page'. For subseque= nt fetches I use '> lastKey ... order by ... asc' to scroll forwards and '<= firstKey ... order by ... desc' to scroll backwards.=0A=0AI too would be i= nterested to know how that approach compares to the new offset/fetch clause= s.=0A=0A-- Alan Burlison=0A--=0A --0-1225137645-1241368431=:73382 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable
Hi Alan,

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

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

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

for descending queries:
...WHERE (orderColumn =3D startAfterVal AND PK <= startAfterPK) OR=0AorderColumn < startAfterVal ORDER BY orderColumn DES= C, PK DESC

The portion of the query that says "(orderCOlumn =3D star= tAfterVal AND PK [<|>] startAfterPK)" insures consistent scroll order= ing in the following case:

imagine you have a very large user direct= ory table with thousands of rows with a LASTNAME column equal to "SMITH". T= hen you excecute the query above, using LASTNAME as the orderColumn. The af= formentioned portion of the query insures that as you page forward and back= ward 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
=E2=80=9CXML? Too much like HTML. It'll never work on the Web!<= span style=3D"font-style: italic;">=E2=80=9D
-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
Su= bject: Re: The result offset and fetch first clauses
<= br>=0AGeoff hendrey wrote:

> Get the result set. Use a loop to in= crement integer n by PAGE_SIZE,
> and inside the loop use ResultSet.a= bsolute(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 th= at my web application can
> provide links to a set of pages evenly di= stributes throughout the
> result set.

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

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

-- Alan Burlison
--
--0-1225137645-1241368431=:73382--