Re: New Id usign max(id)

From: Mark C. Stock <mcstockX_at_Xenquery>
Date: Thu, 6 Oct 2005 17:03:37 -0400
Message-ID: <H8mdnUcxn8SzDNjenZ2dnUVZ_sqdnZ2d@comcast.com>

"Jason King" <jhking_at_airmail.net> wrote in message news:11kb3pl4dlgete1_at_corp.supernews.com...

> mike wrote:

>> I have a table defined like:
>>
>> CREATE TABLE mytable
>> (
>> NUM INTEGER NOT NULL,
>> ID INTEGER NOT NULL,
>> TITLE VARCHAR(50) NOT NULL,
>> .
>> . (other fields)
>> .
>> )
>>
>> ALTER TABLE mytable
>> ADD CONSTRAINT XPK_IDX PRIMARY KEY(NUM, ID)
>>
>> ALTER TABLE mytable
>> ADD CONSTRAINT XFK FOREIGN KEY (NUM)
>> REFERENCES yourtable(NUM)
>> ON DELETE CASCADE
>>
>> So my NUM, ID relationship would look like:
>>
>> NUM ID
>> 1 1
>> 1 2
>> 1 3
>> 2 1
>> 2 2
>> 2 3
>>
>> I wanted the ID to be sequential, but can't tie a sequence to it
>> because for each NUM I'd like the ID to be 1,2,3, etc. as defined
>>
>> So, my question then is before I do the insert are there any other ways
>> you consider better than doing a query to get the max(id).
>>
>> My select would be: select max(id) from mytable where num=1
>>
>> Mike
>>

> Your select will work.  If you need to have an order within num column 
> (say line within invoice) then keep things as they are.  If mytable.id is 
> not externally meaningful you might make it alone the PK and then you 
> could populate it via a sequence.

concurrency is an issue when using MAX(ID), as there is nothing to prevent two or more operations from grabbing the same MAX(ID) at the same time and attempting to insert using the same value.

in the old days, before oracle supplied SEQUENCE objects, we would record the NEXT_ID in a table (usually one column for the TABLE_NAME, one for the NEXT_ID, and code a SELECT and an UPDATE against the table. that prevented more than one user from attempting to use the same ID, but serialized the transactions. which is why we now have SEQUENCE objects.

if there is no business case that requires sequential IDs with no gaps, the right way to code SA-PKs in Oracle is with a SEQUENCE.

if there is a business case that requires sequential IDs with no gaps, you will either have to live with slower through-put or come up with a scheme improve throughput.

AFAIK, this still holds true in 10gR2, but I do know there are some changes to the way it handles SEQUENCE objects

++ mcs Received on Thu Oct 06 2005 - 16:03:37 CDT