In Oracle SQL, a sequence is a schema object used to generate unique numeric values, typically
used for creating unique identifiers like primary key values. Sequences are especially useful in
multi-user environments where multiple transactions need unique numbers simultaneously
without conflicts.
Key Features of a Sequence:
1. Automatic Increment:
o Sequences automatically generate the next value when queried.
2. Unique Values:
o Ensures that each number generated is unique.
3. Customizable:
o You can specify the starting value, increment size, maximum value, and other
properties.
4. Independent of Tables:
o Sequences are not tied to any specific table or column.
5. Thread-Safe:
o Multiple users can access a sequence simultaneously without causing duplicates.
Common Use Cases:
Generating Primary Key values.
Creating unique identifiers for rows in a table.
Avoiding concurrency issues when multiple sessions insert data into a table.
Creating a Sequence:
You can create a sequence using the CREATE SEQUENCE statement. Here is the syntax:
CREATE SEQUENCE sequence_name
START WITH start_value
INCREMENT BY increment_value
[MAXVALUE max_value | NOMAXVALUE]
[MINVALUE min_value | NOMINVALUE]
[CYCLE | NOCYCLE]
[CACHE cache_size | NOCACHE];
Key Clauses:
1. START WITH:
o Specifies the starting value of the sequence (default is 1).
2. INCREMENT BY:
o Specifies the value by which the sequence increments (default is 1).
o It can be negative for descending sequences.
3. MAXVALUE / NOMAXVALUE:
o MAXVALUE: Specifies the maximum value for the sequence.
o NOMAXVALUE: No upper limit (default for ascending sequences).
4. MINVALUE / NOMINVALUE:
o MINVALUE: Specifies the minimum value for the sequence.
o NOMINVALUE: No lower limit (default for ascending sequences).
5. CYCLE / NOCYCLE:
o CYCLE: Restarts the sequence when it reaches the maximum or minimum value.
o NOCYCLE: Stops generating values once the limit is reached (default).
6. CACHE / NOCACHE:
o CACHE: Preallocates a specified number of sequence values in memory for faster
access.
o NOCACHE: No preallocation, values are generated one at a time.
Example: Creating a Sequence
CREATE SEQUENCE emp_seq
START WITH 1
INCREMENT BY 1
MAXVALUE 10000
CACHE 20
NOCYCLE;
Starts at 1 and increments by 1.
Maximum value is 10000.
Caches 20 values in memory for performance.
Stops after reaching the maximum value (NOCYCLE).
Using a Sequence:
To use a sequence, you typically call the following pseudocolumns:
1. NEXTVAL:
o Retrieves the next value in the sequence and increments it.
2. SELECT emp_seq.NEXTVAL FROM DUAL;
3. CURRVAL:
o Retrieves the current value of the sequence (after at least one NEXTVAL has been
called).
4. SELECT emp_seq.CURRVAL FROM DUAL;
Example: Inserting Sequence Values into a Table
INSERT INTO employees (employee_id, first_name, last_name)
VALUES (emp_seq.NEXTVAL, 'John', 'Doe');
Altering a Sequence:
To modify an existing sequence:
ALTER SEQUENCE emp_seq
INCREMENT BY 2
MAXVALUE 20000;
Dropping a Sequence:
To delete a sequence:
DROP SEQUENCE emp_seq;
Advantages of Sequences:
1. Avoids duplication issues when generating unique values.
2. Ensures thread safety for multi-user environments.
3. Provides flexibility with custom increments, cycling, caching, etc.
Limitations:
1. Sequences are not tied to a specific table. It's up to the developer to ensure proper usage.
2. Gaps can occur in the sequence values, especially if transactions fail or roll back.
Common Questions:
1. Why use CACHE?
o Improves performance by reducing the number of times Oracle accesses the
sequence in memory.
2. Can I reset a sequence?
o You cannot reset a sequence directly. Instead, drop and recreate it with the desired
starting value.
Let me know if you have further questions or need examples!
