Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (athena.apache.org: local policy)
Date: Mon, 20 Oct 2008 05:35:44 -0700
From: Rick Hillegas <Richard.Hillegas@Sun.COM>
Subject: Re: Most efficient way to get max row id?
In-reply-to: <eb9696930810191352k37ce5ac7ob62d1df8c637cfcf@mail.gmail.com>
Sender: Richard.Hillegas@Sun.COM
To: Derby Discussion <derby-user@db.apache.org>
Message-id: <48FC7B20.3030800@sun.com>
MIME-version: 1.0
Content-type: text/plain; format=flowed; charset=ISO-8859-1
Content-transfer-encoding: 7BIT
References: <eb9696930810191352k37ce5ac7ob62d1df8c637cfcf@mail.gmail.com>
User-Agent: Thunderbird 2.0.0.17 (Macintosh/20080914)

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
>
>