Identity Columns, Sequences, Caching, Ordering, Gaps, and Scalable Identifier Patterns

Learning outcomes

ServiceHub needs stable surrogate identifiers for work orders and events. A developer proposes “gapless sequence numbers” and another proposes an identity column everywhere because it resembles another database engine. Oracle offers both identity columns and explicit sequences, but their caching, ordering, transaction, and RAC behavior must be separated from business numbering requirements.

1. Sequence and identity solve key generation, not business chronology

An Oracle sequence is an independent schema object that generates numeric values. Multiple tables or code paths can use it. An identity column attaches generated-number behavior to a table column and Oracle manages an underlying sequence implementation for that identity. Both are suitable for surrogate identifiers; neither promises gapless numbering or that a larger committed value always corresponds to an earlier/later business event.

2. NEXTVAL and CURRVAL are session mechanics

NEXTVAL advances a sequence and returns a value. CURRVAL returns the value most recently obtained by NEXTVAL in the current session. Calling CURRVAL before the session has called NEXTVAL raises an error. Sequence advancement is not rolled back with the transaction that used the number.

DROP SEQUENCE IF EXISTS servicehub_probe_seq;CREATE SEQUENCE servicehub_probe_seq START WITH 1000 CACHE 20 NOORDER;SELECT servicehub_probe_seq.NEXTVAL AS first_value FROM dual;SELECT servicehub_probe_seq.CURRVAL AS same_session_value FROM dual;SAVEPOINT before_probe;SELECT servicehub_probe_seq.NEXTVAL AS consumed_value FROM dual;ROLLBACK TO before_probe;SELECT servicehub_probe_seq.NEXTVAL AS next_after_rollback FROM dual;-- The rolled-back transaction does not put the sequence number back.

3. CACHE improves throughput but makes unused values unsurprising

With CACHE, Oracle preallocates sequence values in memory so requests need less synchronization. If an instance shuts down or cached values are otherwise abandoned, some numbers can remain unused. NOCACHE reduces that source of gaps but does not make a sequence gapless because rollbacks and other consumption patterns still lose values. Current 26ai SQL Language Reference documents a default cache of 20 when neither CACHE nor NOCACHE is specified.

SELECT sequence_name, min_value, max_value,       increment_by, cycle_flag, order_flag, cache_size, last_numberFROM user_sequencesWHERE sequence_name = 'SERVICEHUB_PROBE_SEQ';

LAST_NUMBER in dictionary metadata is not a promise of the next value an application will receive in every cached/concurrent situation. Treat the sequence as a generator, not an accounting ledger.

4. ORDER, NOORDER, RAC, and scalable identifiers

NOORDER is the default and is normally appropriate for primary-key generation because there is no requirement to serialize requests into request order. ORDER asks Oracle to generate values in order of request; historically that carried additional coordination cost in Real Application Clusters (RAC). Current 26ai RAC documentation notes optimizations for ordered sequences, but that does not turn sequence values into commit timestamps or eliminate gaps. For very high-concurrency loads, Oracle also provides scalable sequence capabilities; evaluate them from the exact 26ai/RAC documentation and key-shape requirements rather than applying a universal cache size.

5. Identity columns: make generation part of table DDL

Identity clauses are useful when the generator belongs conceptually to one table. GENERATED ALWAYS prevents ordinary explicit values; BY DEFAULT allows an explicit value to override generation; BY DEFAULT ON NULL also generates when an explicit NULL is supplied. Choose the contract deliberately because migration/import tooling can need explicit-key behavior.

DROP TABLE IF EXISTS servicehub_identity_probe PURGE;CREATE TABLE servicehub_identity_probe (  probe_id NUMBER GENERATED ALWAYS AS IDENTITY,  note     VARCHAR2(80 CHAR),  CONSTRAINT pk_sh_identity_probe PRIMARY KEY (probe_id));INSERT INTO servicehub_identity_probe(note) VALUES ('generated');-- Deliberately wrong for GENERATED ALWAYS:INSERT INTO servicehub_identity_probe(probe_id, note)VALUES (5000, 'manual');-- Expected identity-column error; do not “fix” by disabling integrity blindly.SELECT probe_id, note FROM servicehub_identity_probe ORDER BY probe_id;

6. Deliberately wrong approach: MAX(id)+1 for a shared key

SELECT MAX(id)+1 looks gapless in a single-user demo, but two concurrent sessions can read the same maximum and race to insert the same next number. A unique constraint then turns the race into an intermittent production error; removing the unique constraint turns it into corrupt identity semantics.

-- Do not use this as a concurrent key generator:SELECT NVL(MAX(probe_id),0) + 1 AS next_idFROM servicehub_identity_probe;-- Safe technical-key patterns:-- 1) identity column owned by the table, or-- 2) explicit sequence.NEXTVAL plus a primary/unique constraint.

If a legal invoice or ticketing system truly requires controlled numbering, model issuance as a business transaction with its own locking, audit, cancellation/void semantics, and jurisdiction-specific rules. Do not redefine “primary key” to mean “official document number.”

7. Hands-on lab: explicit sequence versus identity

Create one sequence-backed table and one identity-backed table. Insert ten rows into each, intentionally roll back one sequence-backed insert, and record the observed IDs. Then inspect USER_SEQUENCES, identity metadata in USER_TAB_IDENTITY_COLS, and primary-key constraints. Your conclusion must state that observed gaps are expected generator behavior, not evidence of lost committed rows.

DROP TABLE IF EXISTS servicehub_sequence_probe PURGE;CREATE TABLE servicehub_sequence_probe (  probe_id NUMBER PRIMARY KEY,  note     VARCHAR2(80 CHAR));INSERT INTO servicehub_sequence_probeVALUES (servicehub_probe_seq.NEXTVAL, 'first committed');COMMIT;INSERT INTO servicehub_sequence_probeVALUES (servicehub_probe_seq.NEXTVAL, 'rolled back');ROLLBACK;INSERT INTO servicehub_sequence_probeVALUES (servicehub_probe_seq.NEXTVAL, 'second committed');COMMIT;SELECT * FROM servicehub_sequence_probe ORDER BY probe_id;SELECT table_name, column_name, generation_type, sequence_nameFROM user_tab_identity_colsWHERE table_name = 'SERVICEHUB_IDENTITY_PROBE';

8. Production judgment and next step

Use identity columns when generation is table-local and the DDL contract fits import/application needs. Use explicit sequences when generation must be shared, independently governed, or explicitly referenced. Prefer caching and NOORDER for scalable surrogate-key generation unless a measured requirement justifies otherwise. Never promise gaplessness from sequence semantics. In RAC, verify the exact workload and current 26ai behavior before selecting ORDER, scalable sequences, or cache policies.

Lesson 5 completes the schema-design chapter with virtual columns, defaults, invisible columns, and 26ai wide-table gating—features that can make evolution safer when their metadata and client impacts are understood.

Authoritative references

• CREATE SEQUENCE — CACHE/NOCACHE, ORDER/NOORDER, KEEP/NOKEEP and sequence semantics
• Sequence pseudocolumns — NEXTVAL and CURRVAL rules
• CREATE TABLE — identity-column clauses and table DDL
• Oracle RAC Administration and Deployment Guide — current RAC sequence-ordering/scalability considerations