How to Use the Oracle SQL TRUNCATE Statement: Differences from DELETE and Optimization

When deleting large amounts of data, have you ever experienced issues like “the process never finishes” or “the UNDO tablespace overflowed”?

The Oracle TRUNCATE statement is a powerful command that instantly erases all data in a table and releases storage capacity. However, if used without correctly understanding how it differs from DELETE, it runs the risk of leading to irreversible accidents.

In this article, we will explain the basic syntax of the TRUNCATE statement, a detailed comparison with the DELETE statement, the mechanism of performance improvement through resetting the HWM (High Water Mark), and how to handle errors encountered in practice, based on Oracle Database 19c.

Conclusion: Shortest Path (To-Do List)

For those short on time, here are the key points for executing the TRUNCATE statement.

Check Target: Re-confirm that it is safe to delete all records in the table (you cannot use a WHERE clause).
Check Constraints: Confirm that the table is not referenced by Foreign Keys (FK).
Backup: Since you cannot revert this operation, back up data using CREATE TABLE ... AS SELECT if necessary.
Execute: Run the TRUNCATE TABLE command (it commits automatically).

1. What is the TRUNCATE Statement? Mechanisms and Benefits

The TRUNCATE statement is DDL (Data Definition Language) that “physically cuts off” table data.

The Decisive Difference from the DELETE Statement (DML vs DDL)

A standard DELETE statement is DML (Data Manipulation Language); it deletes one row at a time and records a history (UNDO data) stating “this was deleted” for each row. Therefore, deleting large amounts of data takes time, and a rollback (cancellation) is possible.

On the other hand, the TRUNCATE statement is DDL. Instead of processing data rows individually, it releases the “area (extents)” themselves where the data is stored.

Ultra-fast: Log generation is minimal (only updates to the data dictionary).
Immediate Commit: It is committed (finalized) simultaneously with execution and cannot be rolled back.

Resetting the HWM (High Water Mark)

Oracle tables have a water level line called the HWM, indicating “data has existed up to this point.”

Case of DELETE: Data disappears, but the HWM does not go down. Even if empty space is created, Oracle scans considering “there might be data,” so search speed may not improve.
Case of TRUNCATE: The HWM is reset (initialized). This returns unused space to the OS or tablespace, drastically speeding up the next Full Table Scan.

[Diagram: Movement of HWM]

Initial State (Full of Data)
[■■■■■■■■■■] <- HWM

After DELETE Execution (Contents are empty, but the frame remains)
[□□□□□□□□□□] <- HWM (Position does not change = Reads up to here during search)

After TRUNCATE Execution (Frame removed)
[ ] <- HWM (Reset = Search is fast)

2. Comparison Table: TRUNCATE vs DELETE

Here is a comparison table to help you judge which to use in practice.

Feature	TRUNCATE Statement	DELETE Statement
SQL Type	DDL (Data Definition Language)	DML (Data Manipulation Language)
Condition (WHERE)	Impossible (Delete all only)	Possible
Processing Speed	Very Fast	Slow (Proportional to data volume)
Rollback	Impossible (Auto-commit)	Possible (Before COMMIT)
Triggers	Not fired (DELETE triggers ignored)	Fired
HWM (Water Mark)	Reset	Maintained
Required Privileges	`DROP ANY TABLE`, etc.	`DELETE` privilege
Main Use Cases	Work table initialization, total data replacement	Deleting specific data, business logic

3. Basic Syntax and Execution Procedure

Basic Syntax

TRUNCATE TABLE [schema_name.]table_name [DROP STORAGE | REUSE STORAGE];

DROP STORAGE (Default): Returns the released space to the tablespace.
REUSE STORAGE: Retains the space (Reduces the load of re-allocation if you intend to INSERT the same amount of data immediately after).

Execution Example (Practical Code)

The following is an example of emptying the WORK_LOG table owned by user SCOTT.

Note: The SQL below does not include multi-byte characters.

Prerequisites:

Target Table: WORK_LOG
Executing User: Table owner or holder of DROP ANY TABLE privilege.

-- 1. Check current data count (Optional)
SELECT count(*) FROM WORK_LOG;

-- 2. Create backup just in case (Recommended)
-- Creating a backup table using CTAS (Create Table As Select)
CREATE TABLE WORK_LOG_BK AS SELECT * FROM WORK_LOG;

-- 3. Execute TRUNCATE
TRUNCATE TABLE WORK_LOG;

-- 4. Verify the result
SELECT count(*) FROM WORK_LOG;

[Explanation of Execution Results]

If the command is successful, Table truncated. is displayed. The result of the SELECT immediately following will be 0 records. This operation is committed immediately, so data will not return even if ROLLBACK; is executed.

4. Troubleshooting (Common Errors)

Here are common errors encountered when executing TRUNCATE and how to deal with them.

ORA-02266: unique/primary keys in table referenced by enabled foreign keys

Cause: An enabled Foreign Key constraint exists in another table (child table) that references data in the table you are trying to delete (parent table).
Solution:
- Delete data in the child table first, or disable the constraint.
- Add the CASCADE option to TRUNCATE (Oracle 12c and later).
Caution: CASCADE will also delete data in the child table along with it. This is very dangerous, so basically handle this by “disabling the constraint.”

[Workaround: Temporarily Disabling Constraints]

-- Disable the constraint on the child table
ALTER TABLE CHILD_TABLE DISABLE CONSTRAINT FK_CONSTRAINT_NAME;

-- Now you can truncate the parent table
TRUNCATE TABLE PARENT_TABLE;

-- Enable the constraint again (Ensure data consistency first)
ALTER TABLE CHILD_TABLE ENABLE CONSTRAINT FK_CONSTRAINT_NAME;

ORA-00054: resource busy and acquire with NOWAIT specified…

Cause: Another session is locking that table (e.g., someone performed an INSERT or UPDATE and has not committed).
Solution: Wait for the locking session to commit or rollback, or identify and terminate that session.

5. Operational and Security Precautions

1. Privilege Management

TRUNCATE is an operation that deletes data, but the required privilege is not the DELETE privilege, but the privilege to “change/destroy” the table (being the owner or having the DROP ANY TABLE system privilege). Be careful not to grant excessive privileges to general application users.

2. Importance of Backups

The biggest risk is that it “disappears in an instant” and “cannot be restored.” When executing in a production environment, be sure to take a backup beforehand using EXPDP (Data Pump) or CTAS.

3. Flashback Limitations

Oracle’s Flashback Table feature can usually revert to a point in the past. However, although the object ID remains the same with a truncated table (since it is DDL), the data structure is reset, so you cannot restore it using Flashback Table (unless you are using the Flashback Database feature).

6. FAQ (Frequently Asked Questions)

Q1: What happens to indexes after TRUNCATE?

A1: The contents of the indexes become empty along with the data, but the index definitions (structure) themselves remain. Special reconstruction (REBUILD) is usually unnecessary, but since statistical information is reset, we recommend re-gathering statistics if necessary.

Q2: Are serial numbers in ID columns (IDENTITY columns) reset?

A2: It depends on the version and settings, but basically, they are not reset. If you want to return the serial number to 1, separate operations such as ALTER TABLE ... MODIFY ... RESTART START WITH 1; are required.

Q3: Can I TRUNCATE only specific partitions?

A3: Yes, it is possible. If the table is partitioned, you can empty only a specific partition at high speed using TRUNCATE TABLE table_name PARTITION (partition_name);.

7. Summary

The TRUNCATE statement is a very convenient tool in Oracle Database operations.

Fast: Generates almost no UNDO logs and can delete millions of records in seconds.
Efficiency: Resets the HWM, releasing storage capacity and improving search performance.
Caution: No WHERE clause, no rollback, triggers do not fire.

It is ideal for “periodic cleaning of log tables” or “initialization of test environments,” but please perform operations carefully. First, try out the difference in behavior from DELETE in your development environment.

Target Version: 19c

[reference]
Oracle Database SQL Language Reference, 19c