 |
|
EnterpriseDB: SEQUENCE
Oracle Tips by Burleson Consulting
|
As I
mentioned earlier in this chapter, a SEQUENCE is just a number
generator (basically a counter). In many instances, a sequence is
used as the Primary Key of a table. If you remember our discussion
above, a Primary Key uniquely identifies each row of a table. A
number generator will do that nicely. Of course, a sequence cannot
guarantee that all of the other columns in the row are unique. That
would be a job for constraints.
INCREMENT
Sequences can increment or decrement. You can start at 100 and
count down to 0 or you can start at 0 and count up to 100.
A
sequence can skip more than one number by using the increment
keyword and passing a numeric value. An incrementing sequence can
be created to start at 0 and skip 5 so each call would return 0, 5,
10, 15, etc.
A
positive INCREMENT creates an incremental sequence and sets the
number to skip on each call. A negative value creates a decrement
sequence and sets the number to reduce the sequence value on each
call.
MAXVALUE|MINVALUE|NO MAXVALUE|NO MINVALUE
You
can set the maximum (for incrementing) or minimum (for decrementing)
value allowed for a sequence or you can allow it to count to the
maximum allowed by the system. On some older systems, the maximum
is around 2 billion (+/-). On some systems, it can be larger (9
million trillion). Either way it's a pretty large number. If your
system supports 8 byte integers, it should support the larger value.
CYCLE|NO CYCLE
You
can choose what happens when the sequence reaches its maximum
value. If you create a SEQUENCE with the CYCLE keyword, it will
start over at the beginning (either minvalue or maxvalue depending
on if it is an increment or decrement). If you choose NO CYCLE, it
will raise an error.
CACHE|NOCACHE
You
can cache a sequence for better response time. A sequence fetch is
a serializing action. If you are fetching a sequence value
frequently, that can cause contention. If you allow a cache, you
will reduce that contention.
This
is an excerpt from the book "EnterpriseDB:
The Definitive Reference" by Rampant TechPress.
