Oracle Sequences 🔢¶

Prerequisites: Table Management

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!