Re: Please help with no-gap autoincrement field

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 --
Received on Wed Jun 14 2006 - 10:02:41 CDT