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 <[email protected]> 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 --
