Month: October 2025

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.

We covered SQL/PGQ in April and GRAPH_TABLE in May. This week, let’s look at the operational side: how Oracle 23ai’s property graphs differ from traditional graph database approaches, and why “operational” is the key word.

What makes a graph “operational”?

In most graph database architectures (Neo4j, Amazon Neptune, etc.), graph data is a separate store synchronized from the primary OLTP database. This means:

• Graph queries reflect a snapshot, not current state
• Data pipelines add latency between transactions and graph visibility
• Two systems to maintain, monitor, and backup

Oracle 23ai’s property graphs are defined directly on relational tables. There is no separate graph store. The graph is a logical overlay on the relational data — always current, always transactionally consistent.

-- Create a graph over live operational data
CREATE PROPERTY GRAPH fraud_detection_graph
    VERTEX TABLES (
        accounts   KEY (account_id),
        devices    KEY (device_id)
    )
    EDGE TABLES (
        transactions
            SOURCE KEY (sender_id)      REFERENCES accounts
            DESTINATION KEY (receiver_id) REFERENCES accounts,
        account_logins
            SOURCE KEY (account_id)     REFERENCES accounts
            DESTINATION KEY (device_id) REFERENCES devices
    );

Querying across the live graph:

-- Detect accounts connected to a flagged account within 2 hops
SELECT *
FROM GRAPH_TABLE (
    fraud_detection_graph
    MATCH (flagged) -[]->{1,2} (connected)
    WHERE flagged.is_fraud_flagged = TRUE
    COLUMNS (connected.account_id, connected.balance)
)
WHERE connected.balance > 100000;

Because the graph is defined over live tables, this query reflects every transaction that has occurred up to this moment — no batch sync delay. This is what “operational” means: real-time graph analytics on OLTP data.

We covered IF [NOT] EXISTS in January. Let’s go deeper — specifically into how it integrates with migration tooling and advanced scripting patterns.

Idempotent schema migrations:

The dream of every migration tool is an idempotent script — one you can run multiple times safely, producing the same result. Oracle 23ai makes this achievable natively:

-- Safe initial setup script
CREATE TABLE customers         IF NOT EXISTS ( ... );
CREATE SEQUENCE seq_customer   IF NOT EXISTS START WITH 1;
CREATE INDEX idx_customer_email IF NOT EXISTS ON customers(email);
CREATE VIEW active_customers   IF NOT EXISTS AS SELECT ...;

-- Safe cleanup
DROP TABLE temp_migration_log   IF EXISTS;
DROP INDEX idx_old_email_format IF EXISTS;

Conditional object creation based on environment:

-- Only create the debug table in non-production environments
CREATE TABLE debug_trace_log (
    trace_id    NUMBER,
    trace_time  TIMESTAMP DEFAULT SYSTIMESTAMP,
    trace_msg   CLOB
) IF NOT EXISTS;

Combining with PL/SQL for complex logic:

BEGIN
    -- Create the table if new, then add a column to it
    EXECUTE IMMEDIATE 'CREATE TABLE config_params (
        param_name  VARCHAR2(100) PRIMARY KEY,
        param_value VARCHAR2(4000)
    ) IF NOT EXISTS';

    -- Add column (IF NOT EXISTS works for ALTER too)
    EXECUTE IMMEDIATE 'ALTER TABLE config_params
        ADD (last_modified DATE DEFAULT SYSDATE) IF NOT EXISTS';
END;

ALTER TABLE … ADD column IF NOT EXISTS is new in 23ai and is one of the most requested migration helpers — no more checking ALL_TAB_COLUMNS before adding a column in a script.

For Flyway, Liquibase, or custom migration frameworks targeting Oracle 23ai, these additions dramatically reduce the complexity of migration scripts.