db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Dain Sundstrom <dsundst...@gluecode.com>
Subject Re: Support for SQL Limit?
Date Sun, 28 Nov 2004 18:44:56 GMT
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.

-dain


On Nov 28, 2004, at 8:03 AM, Jan Hlavatý wrote:

> Daniel John Debrunner wrote:
>> Derby supports limiting the number of rows returned by a query through
>> the standard JDBC java.sql.Statement.setMaxRows() method.
>
> And does it affect the execution plan? Knowing the maximum number of 
> records
> would be extremely useful for query optimizer, which could select a 
> much faster
> plan using indexes which otherwise has higher "per record" cost over 
> mass methods
> like full table scan...
>
> This was (is?) one of the major shortcomings in SapDB - it always
> realized query for first few records starting with specific value in 
> an (existing) index order
> by full table scan and sort, thinking the index scan would cost much 
> more.
>
> Jan
>


Mime
View raw message