db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Geoffrey Hendrey <geoff_hend...@yahoo.com>
Subject Re: The result offset and fetch first clauses
Date Wed, 06 May 2009 19:57:36 GMT

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 <Alan.Burlison@sun.com> 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

View raw message