Mark D Powell wrote:
> 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 --
My mistake. Look again with the following change:
CREATE SEQUENCE seq NOCACHE ORDER;
--
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 Thu Jun 15 2006 - 11:49:55 CDT
