db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From vadali <shlomivak...@gmail.com>
Subject Re: Most efficient way to get max row id?
Date Wed, 25 Mar 2009 12:29:20 GMT

Thank you Rick!

That was really helpful!

I need to use option 2, since i cannot retrieve automatically generated
identity numbers on a multi-columns insert
(https://issues.apache.org/jira/browse/DERBY-3609?page=com.atlassian.jira.plugin.system.issuetabpanels%3Aall-tabpanel).

that select you posted is exactly what I need in order to maintain the
identities myself for the time being, starting with the last one (+ inc
size) every time the program restarts (or the connection was lost).



Rick Hillegas-2 wrote:
> 
> Hi Amir,
> 
> 1) If what you need to know is the max value on disk right now, then you 
> will need to issue a "select max(...)" statement.
> 
> 2) Maybe, however, all you need to know is the max value that was ever 
> on disk. Of course, this can be a different number than (1) because you 
> may have deleted rows at the upper end.
> 
> If all you need to know is (2), then you can get the answer from the 
> system catalogs. Here's a little script which creates a table with an 
> identity column, inserts and deletes some rows, and then selects the max 
> value that was ever in the identity column. The last query gives you the 
> answer to (2):
> 
> drop table s;
> create table s
> (
>    a int generated always as identity (start with 3, increment by 3),
>    b int
> );
> 
> insert into s( b ) values ( 1 ), ( 2 ), ( 3 ), ( 4 );
> delete from s where b = 4;
> insert into s( b ) values ( 11 ), ( 12 ), ( 13 ), ( 14 );
> 
> select * from s order by b;
> 
> -- now find the max value that was ever put in the identity column
> select c.autoincrementvalue - c.autoincrementinc
> from sys.syscolumns c, sys.systables t
> where t.tablename = 'S'
> and t.tableid = c.referenceid
> and c.columnname = 'A';
> 
> For more information, please see the Reference Guide section describing 
> the SYSCOLUMNS system catalog.
> 
> Hope this helps,
> -Rick
> 
> Amir Michail wrote:
>> Hi,
>>
>> I was wondering what is the most efficient way to get the max
>> automatically generated row id.
>>
>> select max(...) is slow.
>>
>> Amir
>>
>>   
> 
> 
> 

-- 
View this message in context: http://www.nabble.com/Most-efficient-way-to-get-max-row-id--tp20060117p22700688.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.


Mime
View raw message