db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Alan Burlison <Alan.Burli...@sun.com>
Subject Re: The result offset and fetch first clauses
Date Wed, 06 May 2009 07:45:06 GMT
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 

Alan Burlison

View raw message