# 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](../course-plan.md) · Appendix entry: [APPEND HINT](../appendix.md#append-hint) · Related: [PARALLEL DML](parallel-dml.md), [INSERT SELECT](insert-select.md), [FORALL](forall.md), [EXCHANGE PARTITION](exchange-partition.md), [TRUNCATE](truncate.md)

---

## 1. What It Is and When

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.

## 2. Syntax

```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.

## 3. Examples on the Course Schema

### 3a. The archive copy

```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;
```

### 3b. Checking that it really was direct path

```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';
```

### 3c. The NOLOGGING sequence

```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.

### 3d. Direct path from PL/SQL — APPEND_VALUES with FORALL

```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.

### 3e. Parallel direct path

```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](parallel-dml.md).

### 3f. Indexes on a huge load — unusable, load, rebuild

```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.

## 4. What Breaks at Scale

- **The HWM only grows.** Direct path never reuses free space below it. A table that is `DELETE`d and reloaded with `APPEND` every night doubles in allocated size until someone `TRUNCATE`s 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](exchange-partition.md).
- **`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.

## 5. Pitfalls

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.

## 6. Related

- [PARALLEL DML](parallel-dml.md) — multiplying the throughput.
- [INSERT SELECT](insert-select.md) — the statement it decorates.
- [FORALL](forall.md) — `APPEND_VALUES`.
- [EXCHANGE PARTITION](exchange-partition.md) — loading a live table without locking it.
- [TRUNCATE](truncate.md) — resetting the HWM.