Re: Please help with no-gap autoincrement field

From: DA Morgan <damorgan_at_psoug.org>
Date: Wed, 14 Jun 2006 09:42:38 -0700
Message-ID: <1150303363.88933@bubbleator.drizzle.com>

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 chance of losing numbers from a sequence can be dropped to virtually zero with simple error handling.

CREATE TABLE unused_seq_numbers (
seqno NUMBER,
when TIMESTAMP(9),
reason VARCHAR2(250));

CREATE SEQUENCE seq;

DECLARE
  i NUMBER;
  x unused_seq_numbers.reason%TYPE;
BEGIN
   SELECT seq.NEXTVAL
   INTO i
   FROM dual;

   RAISE ZERO_DIVIDE;
EXCEPTION
   WHEN OTHERS THEN

     x := SQLERRM;
     INSERT INTO unused_seq_numbers
     (seqno, when, reason)
     VALUES
     (i, SYSTIMESTAMP, x);
     COMMIT;

END;
/

SELECT * FROM unused_seq_numbers;

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Wed Jun 14 2006 - 11:42:38 CDT