Multivalue Inserts: Loading Data Faster in Oracle 23ai

Oracle 23ai enhances multivalue INSERT syntax, making it simpler to insert multiple rows in a single SQL statement without using INSERT ALL or PL/SQL loops.

Classic single-row INSERT (unchanged):

INSERT INTO products (product_id, name, price)
VALUES (1, 'Widget A', 9.99);

Multirow INSERT with VALUES list:

INSERT INTO products (product_id, name, price)
VALUES
    (1, 'Widget A',  9.99),
    (2, 'Widget B', 14.99),
    (3, 'Gadget X', 49.99),
    (4, 'Gadget Y', 79.99);

This is now standard Oracle SQL syntax. Before 23ai, you needed either:

-- Old INSERT ALL approach
INSERT ALL
  INTO products VALUES (1, 'Widget A',  9.99)
  INTO products VALUES (2, 'Widget B', 14.99)
SELECT 1 FROM DUAL;

Or a PL/SQL FORALL loop for bulk performance.

Performance characteristics

The new multirow VALUES syntax is parsed as a single statement, reducing parse overhead and network round trips. For small-to-medium batch inserts (hundreds of rows), it’s significantly more efficient than individual INSERT statements in a loop, and more portable than INSERT ALL.

Combining with RETURNING:

DECLARE
    TYPE t_ids IS TABLE OF NUMBER;
    v_ids t_ids;
BEGIN
    INSERT INTO products (product_id, name, price)
    VALUES (5, 'Pro Kit', 199.99),
           (6, 'Pro Kit+', 249.99)
    RETURNING product_id BULK COLLECT INTO v_ids;
END;

For application developers inserting from REST APIs or batch processing jobs, multirow VALUES is a welcome simplification. It’s been standard in PostgreSQL and MySQL for years. Oracle’s implementation follows the ANSI SQL standard.

Author: matheusdba