db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bernt M. Johnsen" <Bernt.John...@Sun.COM>
Subject Re: Top 1 - Limit 1
Date Sat, 16 Sep 2006 19:41:48 GMT
Tim Dudgeon wrote:
> 
> 
> Bernt M. Johnsen wrote:
> 
>> Tim Dudgeon wrote:
>>
>>>
>>> Yes, but is there an equivalent to the TOP or LIMIT keywords that other
>>> databases use?
>>
>>
>> Not in SQL (neither in Derby nor the SQL standard).
>> The closest equivalent is Statement.setMaxRows(i).
>>
> 
> So that would not be applied at the query level?
> 
> Statement stmt = con.createStatement();
> stmt.setMaxRows(1);
> stmt.execute("SELECT * FROM a_very_large_table");
> 
> would run very inefficiently?

No. The db does not need to materialize the whole table,and will not do
so either.

> And how about
> 
> SELECT * FROM a_very_large_table ORDER BY some_column ?
> 
> 
> using a SQL keyword like TOP or LIMIT presumably allows these to be
> optimised in some way, but stmt.setMaxRows(1) does not?


No. A full table scan is needed in both cases unless you have an index
on some_column. If you have an index, both cases will be approximately
equal in preformance.

LIMIT/TOP is proprietary SQL dialects and should be avoided if you need
your SQL to be protable.

> 
> Tim
> 
> 


-- 
Bernt Marius Johnsen, Database Technology Group,
Staff Engineer, Technical Lead Derby/Java DB
Sun Microsystems, Trondheim, Norway

Mime
View raw message