Deep Dive — APPEND HINT

Direct-path insert: write formatted blocks straight to the datafiles, above the high-water mark, with almost no undo. Five to ten times faster — with a list of conditions that silently turn it off.

Used in
Chapter 9 — Huge Tables
Appendix entry
APPEND HINT
Related
PARALLEL DML INSERT SELECT FORALL EXCHANGE PARTITION TRUNCATE

A conventional insert finds free space in existing blocks (via the freelist / ASSM bitmaps), reads those blocks into the buffer cache, modifies them, generates undo for every row and redo for both data and undo, and maintains indexes row by row.

A direct-path insert formats new blocks in the session's PGA and writes them directly to the datafiles above the table's high-water mark (HWM). No buffer cache, no free-space search, undo only for the extent map (not the rows), and index maintenance done in bulk at the end. With NOLOGGING on the table, redo drops to almost nothing too.

Use it for: bulk loads and copies of millions of rows — archiving, staging, rebuilding. Not for: OLTP inserts, small batches, tables where free space below the HWM must be reused.

SQL 
INSERT /*+ APPEND */ INTO <table> [(<cols>)] SELECT ... ;          -- direct path for INSERT ... SELECT
INSERT /*+ APPEND_VALUES */ INTO <table> VALUES (...);              -- 11.2+: direct path for VALUES, i.e. FORALL
INSERT /*+ APPEND PARALLEL(<t>, <n>) */ ...                         -- with ENABLE PARALLEL DML: parallel direct path
MERGE /*+ APPEND */ INTO ...                                        -- direct path for the INSERT branch only
COMMIT;                                                             -- required before the table is readable in this session

Silently falls back to conventional insert when:

  • the table has an enabled trigger or an enabled foreign key constraint (referential integrity);
  • the table is in a cluster;
  • the insert is in a distributed transaction or across a database link;
  • the table has deferred constraints, or certain object/LOB configurations in old versions;
  • APPEND is used on INSERT ... VALUES (use APPEND_VALUES).

Verify in the plan: LOAD AS SELECT = direct path; LOAD TABLE CONVENTIONAL = it fell back.

SQL 
INSERT /*+ APPEND */ 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';
COMMIT;

SQL 
EXPLAIN PLAN FOR
INSERT /*+ APPEND */ INTO orders_archive SELECT * FROM orders WHERE order_date < DATE '2025-01-01';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
| Id  | Operation                        | Name           |
|   0 | INSERT STATEMENT                 |                |
|   1 |  LOAD AS SELECT                  | ORDERS_ARCHIVE |       ← direct path
|   2 |   PARTITION RANGE ITERATOR       |                |
|   3 |    TABLE ACCESS FULL             | ORDERS         |

If line 1 says LOAD TABLE CONVENTIONAL, find the trigger or FK:

SQL 
SELECT trigger_name, status FROM user_triggers WHERE table_name = 'ORDERS_ARCHIVE';
SELECT constraint_name, status FROM user_constraints
WHERE  table_name = 'ORDERS_ARCHIVE' AND constraint_type = 'R';

SQL 
-- 1. is it even possible?
SELECT force_logging FROM v$database;              -- YES → NOLOGGING is ignored; stop here
-- 2. switch off logging for the load
ALTER TABLE orders_archive NOLOGGING;
INSERT /*+ APPEND */ INTO orders_archive SELECT * FROM orders WHERE order_date < DATE '2025-01-01';
COMMIT;
ALTER TABLE orders_archive LOGGING;
-- 3. the loaded blocks are NOT in the redo stream: back up now, or a media recovery loses them
-- (RMAN: BACKUP ... or at least note the SCN and back up the tablespace)

NOLOGGING affects only direct-path operations; conventional DML is always logged. And any standby database (Data Guard) requires FORCE LOGGING, which overrides this entirely.

SQL 
DECLARE
  CURSOR c IS SELECT * FROM orders WHERE order_date < DATE '2025-01-01';
  TYPE t_rows IS TABLE OF c%ROWTYPE;
  l_rows t_rows;
BEGIN
  OPEN c;
  LOOP
    FETCH c BULK COLLECT INTO l_rows LIMIT 50000;
    EXIT WHEN l_rows.COUNT = 0;
    FORALL i IN 1 .. l_rows.COUNT
      INSERT /*+ APPEND_VALUES */ INTO orders_archive VALUES l_rows(i);
    COMMIT;             -- must commit before the next FORALL touches the table again
  END LOOP;
  CLOSE c;
END;

Each FORALL is one direct-path load; each needs its COMMIT before the table can be inserted into again in the session (ORA-12838). This is only worth it when the rows had to pass through PL/SQL; otherwise 3a in one statement.

SQL 
ALTER SESSION ENABLE PARALLEL DML;
INSERT /*+ APPEND PARALLEL(a, 8) */ INTO orders_archive a
SELECT /*+ PARALLEL(o, 8) */ * FROM orders o WHERE o.order_date < DATE '2025-01-01';
COMMIT;

Eight PX servers each write their own extents. See PARALLEL DML.

SQL 
ALTER INDEX orders_archive_cust_ix UNUSABLE;
ALTER SESSION SET skip_unusable_indexes = TRUE;        -- default TRUE since 10g
INSERT /*+ APPEND PARALLEL(a, 8) */ INTO orders_archive a SELECT /*+ PARALLEL(o, 8) */ * FROM orders o WHERE ...;
COMMIT;
ALTER INDEX orders_archive_cust_ix REBUILD NOLOGGING PARALLEL 8;
ALTER INDEX orders_archive_cust_ix NOPARALLEL;         -- don't leave the degree on the index

Direct path maintains indexes in bulk anyway, but for loads that double a table, rebuilding is cheaper than merging. Unique indexes cannot be skipped (skip_unusable_indexes does not apply to them) — keep the PK usable.

  • The HWM only grows. Direct path never reuses free space below it. A table that is DELETEd and reloaded with APPEND every night doubles in allocated size until someone TRUNCATEs it. Pair APPEND with TRUNCATE (or partition maintenance), never with DELETE.
  • Exclusive table lock. A direct-path insert takes a TM lock in exclusive mode on the table (or partition, with the partition-extended syntax INSERT INTO orders PARTITION (p_2024_12)): no other session can insert/update/delete that table until you commit. On a live table, load into a staging table and EXCHANGE PARTITION.
  • ORA-12838 — the session cannot read or modify the table again until COMMIT. A procedure that inserts with APPEND and then does SELECT COUNT(*) on the same table fails.
  • NOLOGGING + no backup = unrecoverable. After a media failure, RMAN restores the datafile from before the load and recovery has no redo for those blocks: ORA-01578/ORA-26040 "block was loaded using the NOLOGGING option". The DBA's rule is right: NOLOGGING load → backup immediately.
  • Silent fallback turns your 10-minute load into a 2-hour one with no error. Always check the plan (3b) the first time, and after any schema change.

  1. ORA-12838 — reading the table before commit.
  2. Fallback to conventional because of a trigger or FK — no warning; check the plan.
  3. APPEND on INSERT ... VALUES — ignored; APPEND_VALUES.
  4. DELETE + APPEND cycles — table growth.
  5. NOLOGGING on a FORCE LOGGING database — no effect; on a non-forced one — a backup gap.
  6. The hint must be immediately after INSERT (or MERGE); INSERT INTO t /*+ APPEND */ is a comment, not a hint.
  7. Expecting undo to be small for the indexes — direct path generates undo/redo for index maintenance normally; on a heavily indexed table that can be most of the cost (3f).
  8. Direct-path into a table with a SELECT from the same table in the same statement — allowed, but the exclusive lock applies at commit time; fine.