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.
http://db.apache.org/derby/docs/10.5/ref/rrefsqlj20937.html
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
--
