Oracle TRUNCATE tips

Oracle TRUNCATE statement allows you to quickly delete all rows of a table or cluster.

In release 11.2 truncate has been enhanced to allow you to remove all of the segment space consumed by a table on disk.  This was, you can truncate a table and remove all space associated with the table, shrinking the space until the table begins accepting rows again.

WARNING: The Oracle TRUNCATE statement is a DLL command and cannot be rolled back.

The Oracle docs note the syntax for the Oracle TRUNCATE statement as follows:

TRUNCATE 
   { TABLE [ schema. ]table
     [ { PRESERVE | PURGE } MATERIALIZED VIEW LOG ]
   | CLUSTER [ schema. ]cluster
   }
   [ { DROP | REUSE } STORAGE ]

The Oracle TRUNCATE statement is faster than the DELETE command because it is a DDL command and generates no rollback data. When using Oracle TRUNCATE on a clustered table, the data must be removed from the entire cluster, not just the one table. Any referential integrity constraints on a table must be disabled before it can be truncated. Like a table DROP, a truncation is not recoverable. If a table is truncated, you cannot roll back if you made a mistake. Use Oracle TRUNCATE carefully.

 The database does not actually delete any rows with the Oracle TRUNCATE statement, but with the Oracle TRUNCATE resets the table definition to an empty table by resetting the high water mark. Because of the nature of the Oracle TRUNCATE statement, the database has no way to actually retrieve the old rows.  As the truncated table begins to grow, the space where the old rows were written will be overwritten with the new rows.

 

---