db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jan Hlavatý <hla...@code.cz>
Subject Re: Support for SQL Limit?
Date Mon, 29 Nov 2004 06:40:29 GMT
Dain Sundstrom wrote:
> Most people are looking for limit and offset, so they can implement
> result paging.  Without support for limit and offset most people use
> setMaxRows in the statement for limit and something like relative(int)
> in the result set for the offset.

> The problem I see, is the planner
> does not know how much the user intends to skip until after the query is
> processed, but maybe this can't be optimized anyway.

Yes, that kind of paging needs to count all the records from the beginning
to the end of page, which is on average 50% of all the records in the database.
That does not work well with higher isolation levels, especially without
read-only transactions and multiversion architecture...

What _can_ be optimized however is a different paging method, in which you
don't use record numbers (which make sense only in context of whole resultset
you're trying to page and will shift as records are added/deleted),
but use ordering index key value to position your page in the result set.
You select first N records that have ordering key >= page starting value,
in the order of the ordering index, to get the page.
This type of paging only provides navigation to next/previous/first/last page, but
that is a good thing when the underlying result set is changing a lot anyway, and is
even good for the concurrency (by locking just the page, not whole resultset
or all skipped records from beginning).
Query planner should recognize I'm trying to retrieve a limited number of records
in specific index order starting with specific value, and return the correct records
by planning record retrieval based on the ordering index, not on how the records
happen to be stored (which would be some random records that fulfill the >= predicate
as SapDB does, which is totally useless).

Jan

Mime
View raw message