Re: Please help with no-gap autoincrement field

From: Brian Peasland <oracle_dba_at_nospam.peasland.net>
Date: Wed, 14 Jun 2006 16:18:31 GMT
Message-ID: <J0uyMx.InH@igsrsparc2.er.usgs.gov>

g_chime_at_yahoo.com wrote:

> My boss wants to have an autoincrement index with no gaps, even when
> the insert fails.
> Since MySQL has this feature, some of the code depends on it.
> 
> I am new to ORACLE and based on my limited knowledge, a "before insert"
> type trigger won't do the job and was unable to make a "after
> insert" type trigger work.
> 
> My question was / is: is there an automated way to have an
> autoincrement field with no gaps?
> 
> I understand that the reason for having such a field is questionable.
> Also, I know that changing the code that depends on such an index is an
> option. Still, I am wondering if it can be easily implementid in
> ORACLE.
> 
> Thanks.
> 
> Mark D Powell wrote:

>> Mark Townsend wrote:
>>> Malcolm Dew-Jones wrote:
>>>> g_chime_at_yahoo.com (g_chime_at_yahoo.com) wrote:
>>>> : I am converting from MySQL to ORACLE and having problems with an
>>>> : autoincrement field.
>>>> : MySQL has a built-in autoincrement feature ORACLE doesn't seem to.
>>>>
>>>> The fact that oracle sequences are sequential numbers is just an (un)happy
>>>> coincidence. They would actually have been better off to generate large
>>>> very random numbers instead of sequential numbers because then noone would
>>>> be confused as to how to use the result - i.e. as a unique index that has
>>>> very little other meaning.
>>>>
>>> And in a data warehouse you are probably better of using a random
>>> character string than a random number (or even a number stored as a
>>> character string)
>> I will post agreement with Sybrand, Malcom, and Mark. With the
>> possible exception of invoice numbers and some legal documents gaps in
>> generated keys do not matter since the key is just an artificial unique
>> identifier. No significance should be placed on this value other than
>> using it to join rows of various related tables.
>>
>> Invoice numbers and documents that must be legally accounted for should
>> normally have their values assigned in batch as part of the
>> transaction. Most likely these processes will use a single row table
>> as the source of the key rather than a sequence.
>>
>> The purpose of a sequence is to allow high concurrent access to a
>> resource. If you have only one insert source then you do not need to
>> use a sequence for performance reasons. You can since gaps normally do
>> not matter, but with only a single source you can use a reference row
>> as the key value source and it will only increment as part of a
>> successful transaction.
>>
>> Even with a single insert source you should use a sequence number for
>> generated keys if there are not audit or legal requirements that force
>> use of a sequential key. We managed to get non-sequential invoice
>> numbers past audit. The auditors were not real happy but since we
>> managed to create a table with the taken values and use it to generate
>> a report of missing number (gaps) the auditors had to accept it since
>> the customer was insistent that we generate the invoice number when we
>> ship. For concurrency we need a sequence and a sequence normally means
>> gaps.
>>
>> HTH -- Mark D Powell --

>

The only way I can think of to ensure that you do not have gaps in the number is to implement your own scheme. Create a table with one column which contains the sequence value. When you need the "next" number, explicitly lock the table, read the value, increment it by one, then then update the table. Your transaction can then roll this back since all you are doing is DML on the table.

Note that this serializes your application. Concurrent users will have to wait for another user to unlock the table. This method is not recommended and should be avoided at all costs.

Every time my boss has insisted on no gaps in the sequence, I ask questions and find out that this is just what is expected. In all my years, I have yet to see a valid reason not to allow gaps. Even your checking account allows gaps in the check numbers when you void a check. And most companies really do not require explicitly sequential invoice numbers without gaps. These numbers are purely artificial. The only real requirement is that the numbers be unique for each instance of the entity.

If gaps are not allowed, then how do you restrict someone from deleting a record? Or updating the sequence value? While mySQL and SQL Server will use autoincrementing fields, you can easily remove a row from the table....oops...a gap in the sequence was just created.

HTH,
Brian

-- 
===================================================================

Brian Peasland
oracle_dba_at_nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown

Received on Wed Jun 14 2006 - 11:18:31 CDT