db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Brian Abbott" <br...@dommoni.com>
Subject Re: Support for SQL Limit?
Date Sun, 28 Nov 2004 00:47:17 GMT
Unfortunately I'm not intemately familiar with the internal workings of
Derby but, I would think if you had the appropriate meta-data in the
internal table structures, this would be a very efficient place to execute
this. But, I suppose order by/ResultSet counting would work as well.

Thank you for the info Jeremy!

Brian Abbott

----- Original Message ----- 
From: "Jeremy Boynes" <jboynes@apache.org>
To: "Derby Development" <derby-dev@db.apache.org>
Sent: Saturday, November 27, 2004 12:31 PM
Subject: Re: Support for SQL Limit?


> Brian Abbott wrote:
> > As far as I can tell, Derby does not currently support the SQL Limit
> > command. Am I missing something? If it doesnt, are there any plans to
> > implement it?
> >
>
> LIMIT is really a MySQL feature not an SQL command. Different databases
> implement this in different ways as it was not standardized until SQL03
> (with window functions that I don't think anyone implements yet and
> which are really there for OLAP processing).
>
> One problem with it is that SQL does not define the order in which rows
> are returned; even order by will not define the order for rows with the
> same sort key. This means that if you execute the same query twice you
> may get the rows returned in different orders - this is common on any
> database that uses a parallel query algorithm. As a result the data
> returned by LIMIT/OFFSET may well be non-deterministic.
>
> Subject to these constraints, you can mimic this on the client side by
> controlling how you iterate over the ResultSet - e.g. skip offset rows,
> process limit rows and then close it. This actually gives you a solution
> that is portable between database vendors.
>
> The downside is that the offset rows need to be evaluated and returned
> to the client only to be discarded. However, unless offset is very large
> (which would imply that perhaps you should look for a better query
> strategy anyway) this is probably not where the majority of your
> processing effort is going. Further, if you are using Derby embedded
> then you are simply duplicating what the server would need to do to
> implement offset anyway so the difference is going to be minimal.
>
> However, if you feel that LIMIT/OFFSET is critical or useful
> functionality you might want to open a Jira item to add it as an
> enhancement.
>
> --
> Jeremy


Mime
View raw message