The Oracle TRUNCATE function removes (deletes) all rows from a table or cluster.
When TRUNCATE is used Oracle also deallocates all of the space used by the removed rows unless the MINEXTENTS storage parameter is specified. In that case Oracle retains the space specified by the MINEXTENTS parameter. Oracle also sets the NEXT storage parameter to the size of the last extent removed from the segment by the truncation process.
Using the TRUNCATE statement is often more efficient than dropping and re-creating a table. Dropping and then re-creating a table involves additional steps, including invalidating dependent objects of the table, requiring the regranting of any object privileges on the table, and also requires the re-creation of indexes, integrity constraints, and triggers. It also requires that you respecify its storage parameters. Truncating bypasses these steps.
For any non-trivial number of rows in a given table, the TRUNCATE statement can be faster than clearing the table with a DELETE statement. This is especially true if the table has numerous triggers, indexes, or other dependencies.
Note: Do not confuse the TRUNCATE keyword with the TRUNC function. The TRUNC function is used to truncate either a NUMBER or a datetime value.
Related Code Snippets: