Do Sequences Have Any Disadvantages?
Tom McCready
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?
Many of our applications use a "one-up" number. The Sybase
implementation has some disadvantages. How about the Oracle
implementation? Thank you,
Tom McCready, DBA, Library of Congress 202-707-5511
--
MZ
Jared Hecker
Sequences are appropriate in a variety of situations. E.g., as a surrogate
key when the natural key is concatenated; when you need a unique number
(e.g., a PO system); I could come up with other examples, but I'm sure you
can, too.
Oracle's sequences work very well. I have even been able to rig a system
where distributed (non-replicated) tables share a sequence across servers!
Not recommended, btw.
I am also a (light) Sybase DBA, and I would be curious as to what
disadvantages you are referring to. If nothing else, I can confirm whether
you have similar problems in Oracle.
From what you mention in your message, Oracle sequences seem appropriate.
Regards,
jh
Tom McCready <mccr...@mail.loc.gov> wrote:
Jared Hecker, CODBA | Oracle and Sybase Architect and DBA
ja...@planet.net | - consulting in the
7627...@compuserve.com | NYC/NJ region
Chris Halioris
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?
> Many of our applications use a "one-up" number. The Sybase
> implementation has some disadvantages. How about the Oracle
> implementation? Thank you,
> Tom McCready, DBA, Library of Congress 202-707-5511
> --
around the potential problems you will encounter when trying to
implement your alternative solution. Let's say you have a table called
BOOKS, whose primary key is book_id, which is nothing more than a
"one-up" number that you referred to earlier.
Now if you have an application used by more than one person that creates
records in the BOOK table you have a potential problem. User A goes to
commit a new record and you select the next sequence number from your
table and put it in the record as book_id. While this is going on and
before the record gets committed so that the rest of the world can see it
User B goes to save a new record as well. Since User B can't see the new
record User A created yet as the commit has not completed, User B ends up
with the same book_id.
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.
Chris Halioris
Tactics, Inc.
Robert W. Swisshelm
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?
> Many of our applications use a "one-up" number. The Sybase
> implementation has some disadvantages. How about the Oracle
> implementation? Thank you,
> Tom McCready, DBA, Library of Congress 202-707-5511
> --
> MZ
The only 'disadvantage' that I can think of is that it there is no
guarantee that every sequence number will be used.
For example, assume that a process claims a sequence number, these does
a rollback rather than a commit. That sequence number is 'lost'.
If it is an absolute requirement that you track each value, you could do
a commit after the sequence number is generated but before you change
any of your real data.
--
Bob Swisshelm
Eli Lilly and Company
swis...@lilly.com
Andrew C. Moll
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
jmj2...@glaxowellcome.com
Just be carefull to put a "no-cache" option on a
sequence. The default is to cache sequences. So
when the instance is started it will cache a
certain number from your sequences. This makes
selects from it faster, but when ever the server
goes down (either from shut-down or crash) you
loose that set of numbers.
John Jones
Oracle Consultant
jmj2...@glaxowellcome.com
ZEITGEIST
In article <33035A...@glaxowellcome.com>, jmj2...@glaxowellcome.com
says...
Is this a "bad" thing ? Depending on the number you cache, the
benefits of the fast select may far outweigh the lost numbers. Also,
if I am not mistaken, Oracle will only go out and grab the next set
of numbers when it runs out. Therefore, the only time you would lose the
entire set of numbers is if the crash or shutdown occurs right after a refresh.
Heck, you may only lose a few.
Just my two cents....
Brett C.
zge...@ee.net