Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: integer column with auto increments

Re: integer column with auto increments

From: Wayne Menzie <waynem_at_bosmedtechdotcom.nospam>
Date: Wed, 26 Jan 2000 22:27:31 GMT
Message-ID: <8EC7BE1DDwayneshammalammading@129.250.35.141>


postmaster_at_sybrandb.nospam.demon.nl (Sybrand Bakker) wrote in <948913360.7296.0.pluto.d4ee154e_at_news.demon.nl>:

>Wayne Menzie <waynem_at_bosmedtechdotcom.nospam> wrote in message
>news:8EC787085wayneshammalammading_at_129.250.35.141...
>> postmaster_at_sybrandb.nospam.demon.nl (Sybrand Bakker) wrote in
>> <948900665.1338.0.pluto.d4ee154e_at_news.demon.nl>:
>>
>> >Use a sequence for this and call select sequence.nextval into count1
>> >from dual in a pre-insert trigger. Has been explained in detail in
>> >the newsgroups the last few days.
>>
>> Sybrand,
>>
>> I think he wants the numbers to be in a continuous series. In a
>> multiuser setting, a sequence won't give you that. At least, that's
>> my understanding.
>>
>> Wayne
>
>That's true and not true.
>The sequence will be continuous even if the sequence is used in multiple
>sessions.
>Holes will exist if someone doesn't commit a transaction, and when the
>database is rebooted.
>The latter problem is because sequences are cached by default in the
>SGA. That can be turned off.
>IMO, in my experience it is completely unimportant whether the sequence
>is continuous or not.
>The only other solution is using a table where the series number is
>updated on every insert.
>That will definitely be slower and not as robust as sequences, because
>of potential deadlock problems.

I agree with you and your explaination corresponds with my understanding of sequences. I was trying to point out that the original poster wanted to use the Count field to indicate the number of records and a simple BeforeInsert trigger using a sequence to generate that value isn't guaranteed to give him that result and most likely will not in a multiuser environment, due the reasons you have stated above.

Wayne Menzie Received on Wed Jan 26 2000 - 16:27:31 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US