Re: Do Sequences Have Any Disadvantages?

From: Andrew C. Moll <acmoll_at_acm.org>
Date: 1997/02/12
Message-ID: <330263AA.22A7@acm.org>#1/1

Chris Halioris wrote:
>
> Tom McCready wrote:
> >
> > I would like to hear when it is appropriate to use Sequences
> > and when it is not. Are there disadvantages to Sequences which
> > would force us to keep our own table of high keys, incremented
> > by one each time a record is added?

.
. (stuff snipped out)
.

> If you use a sequence to populate this column this cannot occur. The
> drawback is that you are not guaranteed a contiguous set of numbers
> (i.e., some numbers may get wasted). If the insert fails, the sequence
> will have been incremented in the database and there's no turning back.
> 99.9% of the time this is not an issue because you are using the sequence
> to populate an identifier that is meaningless to the end user (or anyone
> else for that matter) and is just used as an identifier.

For situations where a contiguous number is required, I think it is best to have a single-row table which is locked by the first person starting the transaction. We have this requirement much more than than .1% of all sequences!

We also had problems in our HA-CMP environment, running Oracle 7.0.16. With sequence caching turned on, the sequences got out of sync between the instances. We ended up migrating all users of the sequences onto their own instance. I'd be curious to hear how to synchronize sequences across instances in a way that does not adversely affect performance.

Drew Moll
Fairfax, VA USA Received on Wed Feb 12 1997 - 00:00:00 CST