Knut Anders Hatlen wrote: >> 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? > > Yes, with >= an index scan can go directly to the first row > requested. With an offset clause the index scan would need to start at > the beginning of the index and work its way forward, counting rows. This > is because the index scans don't have an interface that allows you to > say "position on the Nth row". One part of the problem is that the index > may contain deleted rows, newly inserted rows that have not yet been > committed, and deleted rows that have not been committed, so for OFFSET > to work correctly, the scan needs to not count the deleted rows, and > possibly wait for uncommitted changes to be committed depending on the > isolation level. That all makes sense - thanks for the explanation. > Another aspect is that using >= on the index is probably more likely to > yield the correct results in the scenario you're describing, as OFFSET > doesn't guarantee that you start right after the last result on the > previous page if the table has been modified (inserts/deletes) in > between. Yes, not a major issue in our case as the tables don't change that often, but being both correct and fast seems like a win-win situation. >>> 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? > > Yes, the optimizer will try to avoid sorts if possible, also if the > query contains an OFFSET clause. In my case there is an ORDER BY, but it is always on the indexed column, so it sound like I'm in the zone there too. > Note that Derby doesn't currently walk indexes backwards, so a sort is > only avoided if the index is in the same order as the requested ordering > of the result. CREATE INDEX allows you to create both ascending and > descending indexes. > For backwards scrolling the query uses <= and DESC but there's only an ASC index so I'm probably losing out there. However backwards scrolling happens less frequently, so that's a reasonable compromise between index overhead and performance. Thanks for the info, very helpful :-) -- Alan Burlison --