Re: Please help with no-gap autoincrement field

"Mark D Powell" <Mark.Powell_at_eds.com> wrote in message news:1150379462.507860.35520_at_h76g2000cwa.googlegroups.com...
:
: DA Morgan wrote:
: > 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
:
:
: I believe that you will still lose numbers when the database is bounced
: or for a low use sequence when Oracle flushes the sequence cache to
: make room for another sequence's values. You would need to use the
: nocache option in which case you might as well be using a single row
: table to get the value. But even with nocache if the user does not
: commit the row the value is gone and you end up with a gap.
:
: The only way I know to ensure a sequential ordering without resorting
: to single threading access to the table is to perform the sequencing in
: batch rather than at time of insert.
:
: HTH -- Mark D Powell --
:

in the old days we managed multiple interleaved table-based sequences -- ie, instead of a single table-based sequence for each table, we had multiple sequences for each table, with different starting points and different increments, and chose one based on the userid or some other segregating factor

with current technology. this approach can be enhanced by wrapping it up in an autonomous transaction that determines which queue to use, grabs the number, and commits -- allowing the possibility of auditing UID requests.

++ mcs Received on Thu Jun 15 2006 - 09:08:42 CDT