From derby-user-return-10947-apmail-db-derby-user-archive=db.apache.org@db.apache.org Wed May 06 19:58:22 2009 Return-Path: Delivered-To: apmail-db-derby-user-archive@www.apache.org Received: (qmail 33760 invoked from network); 6 May 2009 19:58:22 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.3) by minotaur.apache.org with SMTP; 6 May 2009 19:58:22 -0000 Received: (qmail 87256 invoked by uid 500); 6 May 2009 19:58:21 -0000 Delivered-To: apmail-db-derby-user-archive@db.apache.org Received: (qmail 87228 invoked by uid 500); 6 May 2009 19:58:21 -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 87220 invoked by uid 99); 6 May 2009 19:58:21 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 06 May 2009 19:58:21 +0000 X-ASF-Spam-Status: No, hits=-0.0 required=10.0 tests=SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (athena.apache.org: local policy) Received: from [68.142.207.76] (HELO web31813.mail.mud.yahoo.com) (68.142.207.76) by apache.org (qpsmtpd/0.29) with SMTP; Wed, 06 May 2009 19:58:12 +0000 Received: (qmail 35716 invoked by uid 60001); 6 May 2009 19:57:50 -0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s1024; t=1241639870; bh=jg+3Ic/nZQtxSpvqh367ZzqHDBt7B864rm9WnAfj844=; h=Message-ID:X-YMail-OSG:Received:X-Mailer:Date:From:Subject:To:MIME-Version:Content-Type; b=pAVCg4kgcS/tw3zwFbjJiyPGkwS45REjEFjG5gW/JR0OI9Q9L46FGEYq4HQpek1FkBwMBN4pmGakdSS9DquOo8nxGghD7xzgrXiRw/Oby3PHjlh1Yu7WaBeq6g4ROFgVicybkHiZMgu0VJN2XrA6q0PMS6T0ia8yP+8tJ2BceTI= DomainKey-Signature:a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=Message-ID:X-YMail-OSG:Received:X-Mailer:Date:From:Subject:To:MIME-Version:Content-Type; b=Q0CL1ZT8F0NadZjYyw5rT/m/iPBa+xZX8GGW46agRo5iDK4dNuDBVFmg3NcyQ0GGf+6GBv48Kh1FxKNg2khJwquTgcvHX3t/rLsPIyUtNnWhK13nf9tvs29rLzFhqD+spEzBkrr5WvDSShRuG29ly7wStspH6fLSqEOsO5Bscnc=; Message-ID: <837846.35260.qm@web31813.mail.mud.yahoo.com> X-YMail-OSG: EUDMUNkVM1kc34.BxPasNQLTUGh_W8e7pKPJytOMNX1hayOndCIweBKwOIyrKF7cJ43s.7HKPsl9y7EN7qfhkEzUFy7fMqCzlsqjyV5x2MlO_xQpOL2Zgem5mKBgnIWTHnmJPrqdmlWWQqJLhSt7NeUd7OfMffnNQGzxtybFD_J3NrNh4BoAwHySF9OhJPyTyeUeOuz6pvJ9uzlsVDic5uOVFgS4aPDm9OgZlH3QBFgzKFncfw9v8VPZ4y6novUr0MHY2MqujdVs7K4bPg82yqQw1.rGt9nUghChtcRltUz7AWDs9Y0vh38Qmf6KJAKE5GpVWl6JhT7D3OCC0w-- Received: from [68.142.242.53] by web31813.mail.mud.yahoo.com via HTTP; Wed, 06 May 2009 12:57:50 PDT X-Mailer: YahooMailWebService/0.7.289.11 Date: Wed, 6 May 2009 12:57:50 -0700 (PDT) From: Geoffrey Hendrey Subject: Re: The result offset and fetch first clauses To: Derby Discussion MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Checked: Checked by ClamAV on apache.org Alan, I think we are both looking to the derby guys to "bless" the use of < or > in the where clause as being more efficient than the current implementation of OFFSET/LIMIT As you say, assumption is that indexes exist on the ordered columns. Sent from my iPhone On May 6, 2009, at 12:45 AM, Alan Burlison wrote: Dag H. Wanvik wrote: I am afraid that with embedded driver, you will only save a little CPU (by avoiding some JDBC calls) since under the hood, the code siphons off the rows till it hits the offset, so if you have a large offset, you will still incur reading of those rows (modulo page caching). In client/server driver context the savings are larger, of course, in that fewer rows are sent over the wire. For simple queries that can use an index, the optimizer could make use of the offset information to avoid reading the entire row when skipping rows before offset, just counting rows in the index to get to the first qualifying row, but this optimization is not yet implemented. My understanding is that for queries that use >=, =< etc Derby can already use an index scan if the column being compared has an index on it - in my case it does. So by switching to RESULT/OFFSET I'd lose that benefit, correct? Often, this feature is used together with ORDER BY which would entail some sorting of the result set and then all the rows would have to be read anyway. Again, for some simple queries, sort avoidance is used by the optimizer, so optimization is still possible for for such queries. What if the ORDER BY clause only uses indexed columns? Presumably Derby can just return the rows in index order in that case, and no sort is required? -- Alan Burlison --