Scribd
Upload
5 views1 page

Query 1248

A sequence definition provides essential details such as its name, direction (ascending or descending), interval between numbers, caching options, and cycling behavior. An example is given for creating a sequence named customers_seq to generate unique customer ID numbers. The document also explains how sequences can be used in multiuser environments to generate unique primary keys without locking or I/O overhead.

Uploaded by

nickyyiu
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
5 views1 page

Query 1248

A sequence definition provides essential details such as its name, direction (ascending or descending), interval between numbers, caching options, and cycling behavior. An example is given for creating a sequence named customers_seq to generate unique customer ID numbers. The document also explains how sequences can be used in multiuser environments to generate unique primary keys without locking or I/O overhead.

Uploaded by

nickyyiu
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 1

Sequence Characteristics

A sequence definition indicates general information about the sequence, including its name
and whether the sequence ascends or descends.
A sequence definition also indicates:
• The interval between numbers
• Whether the database should cache sets of generated sequence numbers in memory
• Whether the sequence should cycle when a limit is reached
The following example creates the sequence customers_seq in the sample schema oe. An
application could use this sequence to provide customer ID numbers when rows are added to
the customers table.
CREATE SEQUENCE customers_seq
START WITH 1000
INCREMENT BY 1
NOCACHE
NOCYCLE;
The first reference to customers_seq.nextval returns 1000. The second returns 1001. Each
subsequent reference returns a value 1 greater than the previous reference.
See Also:
• Oracle Database Get Started with Oracle Database Development for a tutorial
that shows you how to create a sequence
• Oracle Database Administrator’s Guide to learn how to reference a sequence in
a SQL statement
• Oracle Database SQL Language Reference for CREATE SEQUENCE syntax and
semantics
Concurrent Access to Sequences
The same sequence generator can generate numbers for multiple tables.
The generator can create primary keys automatically and coordinate keys across multiple rows
or tables. For example, a sequence can generate primary keys for an orders table and a
customers table.
The sequence generator is useful in multiuser environments for generating unique numbers
without the overhead of disk I/O or transaction locking. For example, two users simultaneously
insert new rows into the orders table. By using a sequence to generate unique numbers for
the order_id column, neither user has to wait for the other to enter the next available order
number. The sequence automatically generates the correct values for each user.
Each user that references a sequence has access to his or her current sequence number,
which is the last sequence generated in the session. A user can issue a statement to generate
a new sequence number or use the current number last generated by the session. After a
Chapter 4
Overview of Sequences
4-33
statement in a session generates a sequence number, it is available only to this session.
Individual sequence numbers can be skipped if they were generated and used in a transaction
that was ultimately rolled back.

You might also like