Oracle’s Memoptimized Rowstore (also called Fast Ingest) was introduced in Oracle 18c for high-speed, append-only insert workloads — particularly IoT, telemetry, and time-series data. Oracle 23ai enhances this feature with better memory management and extended query capabilities over optimized data.
What Fast Ingest does:
Instead of writing each row insert directly to the data file (with full redo logging and block-level locking), Fast Ingest buffers new rows in the Large Pool and flushes them to disk asynchronously. This enables extremely high single-row insert throughput.
Setting up Fast Ingest:
CREATE TABLE sensor_readings (
sensor_id NUMBER,
reading_time TIMESTAMP,
temperature NUMBER(6,2),
humidity NUMBER(6,2)
) MEMOPTIMIZE FOR WRITE;23ai enhancements — MEMOPTIMIZE FOR READ:
The new MEMOPTIMIZE FOR READ option creates a specialized in-memory cache of frequently accessed rows optimized for key-based lookups. Combined with MEMOPTIMIZE FOR WRITE, you get a table that ingests fast AND queries fast:
ALTER TABLE sensor_readings MEMOPTIMIZE FOR READ;
-- Fast key lookup — served from memoptimized buffer
SELECT * FROM sensor_readings
WHERE sensor_id = 42
ORDER BY reading_time DESC
FETCH FIRST 1 ROW ONLY;Monitoring Fast Ingest:
SELECT pool, ROUND(used_bytes/1024/1024) AS used_mb,
ROUND(allocated_bytes/1024/1024) AS allocated_mb
FROM v$memoptimize_write_area;Use cases: Smart meter data ingestion, vehicle telemetry, application performance monitoring, financial tick data. If you’re writing millions of rows per hour to Oracle from IoT or streaming sources, MEMOPTIMIZE FOR WRITE should be in your architecture.
