Re: integer column with auto increments

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.

Regards,

Sybrand Bakker, Oracle DBA Received on Wed Jan 26 2000 - 13:00:42 CST