Overview of the Sequence Generator (192)

The sequence generator provides a sequential series of numbers. The sequence
generator is especially useful in multiuser environments for generating unique
sequential numbers without the overhead of disk I/O or transaction locking. For
example, assume two users are simultaneously inserting new employee rows into the
employees table. By using a sequence to generate unique employee numbers for the
employee_id column, neither user has to wait for the other to enter the next
available employee number. The sequence automatically generates the correct values
for each user.

Therefore, the sequence generator reduces serialization where the statements of two
transactions must generate sequential numbers at the same time. By avoiding the
serialization that results when multiple users wait for each other to generate and use a
sequence number, the sequence generator improves transaction throughput, and a
user’s wait is considerably shorter.

Sequence numbers are Oracle integers of up to 38 digits defined in the database. A
sequence definition indicates general information, such as the following:
■ The name of the sequence
■ Whether the sequence ascends or descends
■ The interval between numbers
■ Whether Oracle should cache sets of generated sequence numbers in memory

Oracle stores the definitions of all sequences for a particular database as rows in a
single data dictionary table in the SYSTEM tablespace. Therefore, all sequence
definitions are always available, because the SYSTEM tablespace is always online.

Sequence numbers are used by SQL statements that reference the sequence. You can
issue a statement to generate a new sequence number or use the current sequence
number. After a statement in a user’s session generates a sequence number, the
particular sequence number is available only to that session. Each user that references
a sequence has access to the current sequence number.

Sequence numbers are generated independently of tables. Therefore, the same
sequence generator can be used for more than one table. Sequence number generation
is useful to generate unique primary keys for your data automatically and to
coordinate keys across multiple rows or tables. Individual sequence numbers can be
skipped if they were generated and used in a transaction that was ultimately rolled
back. Applications can make provisions to catch and reuse these sequence numbers, if
desired.

序列生成器
1. 當一個使用者會話中的語句產生了一個序列號 , 這個值只在當前會話有效.
2. 同一個序列物件為不同的表產生的序列號是相互獨立的.

[@more@]