Deep Dive — INSERT SELECT

The database copies rows faster than any loop can: one statement, one pass, and with APPEND it never even touches the buffer cache.

Used in
Chapter 1 — SQL First Chapter 9 — Huge Tables
Appendix entry
INSERT SELECT
Related
APPEND HINT PARALLEL DML MERGE EXCHANGE PARTITION

INSERT ... SELECT inserts the result of a query into a table. Anything you would do with a cursor loop that only inserts — archiving, copying, transforming, splitting into several tables — is one of these statements.

Variants:

  • INSERT INTO t SELECT ... — single target.
  • INSERT ALL INTO t1 ... INTO t2 ... SELECT ... — every row goes to every target (multi-table insert).
  • INSERT FIRST WHEN c1 THEN INTO t1 WHEN c2 THEN INTO t2 ELSE INTO t3 SELECT ... — each row goes to the first matching target.
  • CREATE TABLE t AS SELECT ... (CTAS) — when the target does not exist yet; direct-path by default, NOLOGGING optional.

SQL 
INSERT [hint] INTO <table> [(<col>, ...)]
SELECT ... FROM ... [WHERE ...]
[LOG ERRORS [INTO <err_table>] [(<tag>)] [REJECT LIMIT { <n> | UNLIMITED }]];

-- multi-table
INSERT [ALL | FIRST]
  [WHEN <cond> THEN] INTO <t1> [(<cols>)] VALUES (<exprs>)
  [WHEN <cond> THEN] INTO <t2> [(<cols>)] VALUES (<exprs>)
  [ELSE INTO <t3> ...]
SELECT ... FROM ...;

-- new table
CREATE TABLE <t> [NOLOGGING] [PARALLEL n] AS SELECT ... ;

RETURNING INTO is not available with INSERT ... SELECT — only with single-row INSERT ... VALUES.

SQL 
INSERT INTO orders_archive (order_id, customer_id, order_date, amount, status)
SELECT order_id, customer_id, order_date, amount, status
FROM   orders
WHERE  order_date < DATE '2025-01-01';

INSERT INTO orders_archive SELECT * FROM orders works until someone adds a column to one table and not the other. Listing columns turns that into a compile error instead of shifted data.

SQL 
ALTER SESSION ENABLE PARALLEL DML;
INSERT /*+ APPEND PARALLEL(a, 8) */ INTO orders_archive a
  (order_id, customer_id, order_date, amount, status)
SELECT /*+ PARALLEL(o, 8) */ order_id, customer_id, order_date, amount, status
FROM   orders o
WHERE  order_date < DATE '2025-01-01';
COMMIT;      -- required before reading orders_archive in this session (ORA-12838)

See APPEND HINT and PARALLEL DML for the mechanics and restrictions.

SQL 
INSERT FIRST
  WHEN status = 'CANCELLED' THEN
    INTO orders_cancelled (order_id, customer_id, order_date, amount)
    VALUES (order_id, customer_id, order_date, amount)
  WHEN order_date < DATE '2025-01-01' THEN
    INTO orders_archive (order_id, customer_id, order_date, amount, status)
    VALUES (order_id, customer_id, order_date, amount, status)
SELECT order_id, customer_id, order_date, amount, status
FROM   orders
WHERE  status = 'CANCELLED' OR order_date < DATE '2025-01-01';

One scan of orders, two targets. The loop version would scan once and insert row by row into two tables; the two-statement version would scan twice.

SQL 
INSERT INTO order_summary (summary_id, customer_id, order_month, total_amount, order_count)
SELECT order_summary_seq.NEXTVAL,
       customer_id,
       TRUNC(order_date, 'MM'),
       SUM(amount),
       COUNT(*)
FROM   orders
WHERE  order_date >= DATE '2025-01-01'
GROUP  BY customer_id, TRUNC(order_date, 'MM');

NEXTVAL in the select list is allowed (not inside DISTINCT, GROUP BY, ORDER BY or subqueries — wrap the aggregate in an inline view if needed).

SQL 
CREATE TABLE orders_2024 NOLOGGING PARALLEL 8 AS
SELECT /*+ PARALLEL(o, 8) */ *
FROM   orders o
WHERE  order_date >= DATE '2024-01-01' AND order_date < DATE '2025-01-01';

Direct-path, no undo for the rows, minimal redo with NOLOGGING. Back it up afterwards.

SQL 
EXEC DBMS_ERRLOG.CREATE_ERROR_LOG('ORDERS_ARCHIVE');

INSERT INTO orders_archive (order_id, customer_id, order_date, amount, status)
SELECT order_id, customer_id, order_date, amount, status FROM orders_stage
LOG ERRORS INTO err$_orders_archive ('stage load') REJECT LIMIT UNLIMITED;

  • Conventional insert of 40M rows goes through the buffer cache block by block, generates full undo and redo, and maintains every index row by row. Direct path (APPEND) skips the cache and the undo; NOLOGGING skips most redo.
  • Indexes. For a load that adds a large fraction of the table, it is usually faster to mark indexes UNUSABLE, load, then REBUILD ... NOLOGGING PARALLEL. Direct path maintains indexes in bulk at the end anyway, but a rebuild of a big index is cheaper than a bulk merge into it.
  • One transaction. A 40M-row conventional insert is one transaction: undo sizing, and a failure at row 39M rolls back everything. Direct path needs almost no undo. Chunking (partition by partition, or DBMS_PARALLEL_EXECUTE) gives restartability.
  • Triggers fire per row for conventional and direct-path alike — and an enabled trigger silently turns APPEND into a conventional insert.

  1. INSERT INTO t SELECT * without a column list — positional mapping breaks the day a column is added.
  2. ORA-12838 — reading a table in the same session after a direct-path insert, before COMMIT.
  3. NOLOGGING on a database in FORCE LOGGING mode (every Data Guard setup) does nothing — and where it does work, the table is unrecoverable until the next backup.
  4. NEXTVAL inside GROUP BY/DISTINCT queries: ORA-02287. Aggregate in an inline view, NEXTVAL outside.
  5. INSERT ALL with WHEN clauses evaluates every WHEN for every row; INSERT FIRST stops at the first match. Choose deliberately.
  6. Sequence gaps and CACHE: a 40M-row insert with CACHE 20 hits the sequence 2M times. ALTER SEQUENCE ... CACHE 10000 for bulk loads.
  7. A SELECT with ORDER BY in an INSERT ... SELECT sorts for nothing — unless you are deliberately clustering rows for an index (ORDER BY the index columns can help compression and clustering factor).