Database Reference
In-Depth Information
Figure 22.5
Change a Reverse-
Counting Sequence
to a Forward-
Counting
Sequence.
22.1.3
Using Sequences
Sequences are valuable as unique key generators because they never issue a
duplicate value, even when many users are retrieving numbers from the
sequence. For example, let's imagine that you have 10 operators entering
customer information into your online system. Each time a new customer
row is inserted, it uses a number from the CUSTOMER_SEQ for the pri-
mary key, using CUSTOMER_SEQ.NEXTVAL. Even if all 10 operators
simultaneously insert a new customer, Oracle Database 10
g
will give each
session a unique number. There are never any duplicates.
Another interesting feature of sequences is that they never use the same
number again, even if the user cancels the transaction that retrieved the
number. Continuing with the operators entering customer information,
let's imagine that the tenth operator gets the customer entered and it has
retrieved the number 101 from the CUSTOMER_SEQ sequence. Then
the operator cancels the transaction (say, the customer changes his mind
and hangs up the phone). The next operator to retrieve a sequence gets 102.
When using sequences, there may be gaps in the numbers you see in the
table caused by retrieving a sequence number and then not actually com-
mitting the insert. Obviously, this can have serious implications for
