Re: Please help with no-gap autoincrement field

g_chime@xxxxxxxxx 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@xxxxxxxxx (g_chime@xxxxxxxxx) 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@xxxxxxxxxxxxxxxxxxx
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
.

Relevant Pages

  • Re: Sean Pitman: definitions wanted
    ... but are claiming and asserting without evidence that this ... gaps) is the only possible mechanism for evolution. ... *regardless* of the size of the sequence or the number of amino acids ...
    (talk.origins)
  • Re: Please help with no-gap autoincrement field
    ... My boss wants to have an autoincrement index with no gaps, ... I am new to ORACLE and based on my limited knowledge, ... autoincrement field with no gaps? ... as the source of the key rather than a sequence. ...
    (comp.databases.oracle.misc)
  • Re: Managing an UPDATE executed concurrently?
    ... as I understood what was being discussed was using identity to generate a unique id versus developing and testing a procedure to generate a sequence which never allow gaps. ... If at any time Requirements specify design, you get yourself into these issues. ... What they do want to do is have the ability to identify a voucher and information about that voucher for example maybe expiry date. ...
    (microsoft.public.sqlserver.programming)
  • Re: The Starting Point Problem - for Howard Hershey
    ... There is only a lack of viable mechanistic evidence to the contrary ... of the genome and for entire genomes. ... Sequence data comparisons do not explain the mechanism. ... mechanism can actually cross these gaps from one steppingstone to the ...
    (talk.origins)
  • Re: Please help with no-gap autoincrement field
    ... MySQL has a built-in autoincrement feature ORACLE doesn't seem to. ... I will post agreement with Sybrand, Malcom, and Mark. ... as the source of the key rather than a sequence. ... You can since gaps normally do ...
    (comp.databases.oracle.misc)
Loading