Oracle Sequences 🔢
Mentor's Note: Before Identity Columns existed (Pre-12c), Sequences were the only way to get "Auto-incrementing" IDs in Oracle. They are still powerful today because a single sequence can be shared across many tables! 💡
🌟 The Scenario: The Token Machine 🎟️
Imagine a machine that prints tokens (1, 2, 3...).
- The machine doesn't care who takes the token.
- Once a token is taken, it's gone.
- A Sequence is that token machine for your database.
💻 1. CREATE SEQUENCE
CREATE SEQUENCE emp_id_seq
START WITH 1
INCREMENT BY 1
NOCACHE;
💻 2. Using the Sequence (NEXTVAL) 🛠️
To get the next number, we use .NEXTVAL.
INSERT INTO employees (id, name)
VALUES (emp_id_seq.NEXTVAL, 'Vishnu');
🏗️ Architect's Note: Cache Performance 🛡️
Sequences have a CACHE option (e.g., CACHE 20).
- The Architect's Secret: If you use
CACHE, Oracle pre-generates numbers and keeps them in memory. This is extremely fast for high-concurrency apps. - The Warning: If the database crashes, the cached numbers are lost. You will see a "gap" in your IDs (e.g., 1, 2, 21, 22).
- Architecture Tip: For most business apps, gaps are fine. Use CACHE for performance!