Oracle 23ai Lightweight Tables: When You Don’t Need Everything

Oracle 23ai introduces guidance and improvements around Lightweight Tables — a design philosophy and set of options for creating tables that omit expensive features when they’re not needed, resulting in lower overhead for specific workloads.

The concept isn’t entirely new, but 23ai formalizes and extends the options:

Heap tables without segment-level features:

CREATE TABLE event_log (
    log_time   TIMESTAMP DEFAULT SYSTIMESTAMP,
    log_level  VARCHAR2(10),
    log_msg    VARCHAR2(4000)
)
SEGMENT CREATION IMMEDIATE
NOCOMPRESS
NOLOGGING    -- skip redo logging (use only if data is recoverable from source)
NOMONITORING;

Deferred segment creation:

CREATE TABLE staging_area (
    id     NUMBER,
    data   VARCHAR2(4000)
) SEGMENT CREATION DEFERRED;
-- No space allocated until the first row is inserted

Index-Organized Tables (IOT) for key-based access:

For tables where you always access by primary key and rarely do full scans:

CREATE TABLE session_cache (
    session_token  VARCHAR2(64) PRIMARY KEY,
    user_id        NUMBER,
    expiry         TIMESTAMP,
    payload        VARCHAR2(4000)
) ORGANIZATION INDEX
  COMPRESS 1;

The primary key is the physical storage key — lookups are O(log n) with no separate index, and the data itself is B-tree ordered.

23ai enhancement: Oracle 23ai improves the optimizer’s ability to choose between heap, IOT, and external table access paths automatically, reducing the manual tuning burden for these decisions.

Recommendation: For staging tables, working sets, cache-like structures, and append-only logs, consider which table features you actually need. Removing what you don’t need is always faster than adding features you’ll never use.