# Deep Dive — TRUNCATE

> DDL, not DML: it resets the table's high-water mark instead of deleting rows. Seconds instead of hours — and it commits your transaction on the way.

Used in: [Chapter 9 — Huge Tables](../course-plan.md) · Appendix entry: [TRUNCATE](../appendix.md#truncate) · Related: [EXCHANGE PARTITION](exchange-partition.md), [APPEND HINT](append-hint.md), [COMMIT](commit.md), [EXECUTE IMMEDIATE](execute-immediate.md), [DBMS_ASSERT](dbms_assert.md)

---

## 1. What It Is and When

`DELETE` visits every row: undo for each, redo for each, index entries removed one by one, triggers fired, and the space stays allocated. `TRUNCATE` rewrites the segment header: the high-water mark goes back to zero, extents are released (or kept with `REUSE STORAGE`), indexes are reset the same way. No per-row work at all.

Use it for: emptying staging tables, dropping a partition's contents, resetting work tables between runs. The price: it is DDL — it **commits the current transaction**, cannot be rolled back, fires no triggers, and needs the table to be free of enabled foreign-key references.

## 2. Syntax

```sql
TRUNCATE TABLE <table> [PRESERVE | PURGE MATERIALIZED VIEW LOG] [DROP STORAGE | DROP ALL STORAGE | REUSE STORAGE] [CASCADE];
ALTER TABLE <table> TRUNCATE PARTITION <p> [, <p2> ...] [DROP | REUSE STORAGE] [UPDATE [GLOBAL] INDEXES] [CASCADE];
ALTER TABLE <table> TRUNCATE SUBPARTITION <sp> ...;
ALTER TABLE <table> DROP PARTITION <p> [UPDATE [GLOBAL] INDEXES];
TRUNCATE CLUSTER <cluster>;
```

- `DROP STORAGE` (default) — deallocate extents beyond `MINEXTENTS`. `REUSE STORAGE` — keep them (faster, and the space is about to be reused anyway). `DROP ALL STORAGE` (11.2.0.2+) — deallocate everything including the first extent.
- `CASCADE` (12c+) — also truncate child tables whose FK to this table is `ON DELETE CASCADE`.
- `UPDATE INDEXES` on a partition truncate — keep global indexes usable (does the maintenance now, or asynchronously in 12c+); without it, global indexes become `UNUSABLE`.

## 3. Examples on the Course Schema

### 3a. Staging table reload — TRUNCATE + APPEND

```sql
TRUNCATE TABLE orders_stage REUSE STORAGE;             -- ms; HWM reset, extents kept
INSERT /*+ APPEND */ INTO orders_stage SELECT ... FROM external_orders_ext;
COMMIT;
```

This pair is the standard nightly-load shape. `DELETE` + `APPEND` instead would grow the table every night ([APPEND HINT](append-hint.md) §4).

### 3b. Partition instead of DELETE

```sql
-- DELETE of 40M rows: hours, 40M undo records, 40M index deletes
DELETE FROM orders WHERE order_date >= DATE '2024-12-01' AND order_date < DATE '2025-01-01';

-- the same, as DDL: seconds
ALTER TABLE orders TRUNCATE PARTITION p_2024_12 UPDATE INDEXES;
```

Requires the table to be partitioned on `order_date` so that "December 2024" *is* a partition — this is the main reason large tables are range-partitioned by date.

### 3c. From PL/SQL — dynamic, validated, and outside the transaction

```sql
CREATE OR REPLACE PROCEDURE purge_month (p_partition VARCHAR2) IS
BEGIN
  EXECUTE IMMEDIATE 'ALTER TABLE orders TRUNCATE PARTITION ' ||
                    DBMS_ASSERT.SIMPLE_SQL_NAME(p_partition) || ' UPDATE INDEXES';
END;
/
```

Call it **before** the transactional work of a job, or after its final commit — never between an `UPDATE` and its intended `COMMIT`:

```sql
BEGIN
  UPDATE orders SET status = 'X' WHERE order_id = 1;
  purge_month('P_2024_12');            -- commits the UPDATE silently
  RAISE_APPLICATION_ERROR(-20001, 'validation failed');   -- too late: the UPDATE is permanent
END;
```

### 3d. Foreign keys

```sql
TRUNCATE TABLE customers;
-- ORA-02266: unique/primary keys in table referenced by enabled foreign keys

-- options:
ALTER TABLE orders DISABLE CONSTRAINT orders_cust_fk;
TRUNCATE TABLE customers;
ALTER TABLE orders ENABLE NOVALIDATE CONSTRAINT orders_cust_fk;    -- or ENABLE VALIDATE if the data allows

-- 12c+, if orders_cust_fk is ON DELETE CASCADE:
TRUNCATE TABLE customers CASCADE;                                  -- truncates orders too
```

`ORA-02266` is raised even if the child table is empty — the check is on the constraint's existence, not on rows.

### 3e. Global indexes on partition truncate

```sql
ALTER TABLE orders TRUNCATE PARTITION p_2024_12;                  -- global indexes → UNUSABLE
SELECT index_name, status FROM user_indexes WHERE table_name = 'ORDERS';
ALTER INDEX orders_cust_gix REBUILD PARALLEL 8;                   -- or:

ALTER TABLE orders TRUNCATE PARTITION p_2024_12 UPDATE INDEXES;   -- global indexes stay VALID
```

12c+ does the global-index maintenance asynchronously: the truncate returns immediately, the index entries are marked orphaned and cleaned by the `SYS.PMO_DEFERRED_GIDX_MAINT_JOB` job (or `DBMS_PART.CLEANUP_GIDX`). Local indexes are truncated along with the partition — no maintenance needed, which is the argument for local indexes on partitioned tables.

### 3f. Locked rows

```sql
TRUNCATE TABLE orders_stage;
-- ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
```

Another session holds a row lock (an uncommitted `INSERT` into the stage table). DDL needs an exclusive table lock; it waits `DDL_LOCK_TIMEOUT` seconds (default 0 = fail immediately). `ALTER SESSION SET ddl_lock_timeout = 60` gives it a minute.

## 4. What Breaks at Scale

- `DELETE` of tens of millions of rows is the operation that fills undo, generates the most redo per useful byte, and leaves the table the same size with free space scattered inside — every later full scan still reads the empty blocks up to the HWM. `TRUNCATE` (or partition truncate/drop) is not an optimization of `DELETE`; it is the correct operation, and the table design (partitioning) should make it available.
- `DROP STORAGE` on a table with hundreds of thousands of extents can itself take a while (extent deallocation in the dictionary / bitmap). `REUSE STORAGE` when the space will be refilled.
- Materialized view logs on the table: `TRUNCATE` by default purges the MV log (`PURGE MATERIALIZED VIEW LOG`), which forces the next refresh to be complete rather than fast.
- Statistics are not touched; after a truncate-and-reload the old stats may describe a very different table. Gather after the load (or rely on 12c online stats gathering for direct-path loads into empty tables).
- Flashback Query cannot see truncated rows (no undo was generated). Recovery is RMAN / Flashback Database — which is the DBA's reminder: truncating the wrong table is a restore, not an undo.

## 5. Pitfalls

1. Implicit `COMMIT` — in the middle of a transaction, silently.
2. No `ROLLBACK`, no Flashback Query.
3. `ORA-02266` — enabled FK referencing the table, even with an empty child.
4. `ORA-00054` — DDL lock conflict with any uncommitted DML on the table; set `DDL_LOCK_TIMEOUT`.
5. Global indexes `UNUSABLE` after partition truncate without `UPDATE INDEXES` — queries fail with `ORA-01502` or fall back to full scans depending on `SKIP_UNUSABLE_INDEXES`.
6. Triggers do not fire — audit triggers miss the deletion entirely.
7. Truncating a table other schemas depend on for MV fast refresh — the MV log is purged.
8. `TRUNCATE` on a table in someone else's schema needs `DROP ANY TABLE`, not `DELETE` — a much bigger privilege. Wrap it in a definer's-rights procedure in the owning schema and grant `EXECUTE`.

## 6. Related

- [EXCHANGE PARTITION](exchange-partition.md) — the other zero-row-work partition operation.
- [APPEND HINT](append-hint.md) — the natural partner for reloads.
- [COMMIT](commit.md) — DDL and implicit commits.
- [EXECUTE IMMEDIATE](execute-immediate.md) / [DBMS_ASSERT](dbms_assert.md) — dynamic partition names.