Difference between DELETE and TRUNCATE Command

 DELETE and TRUNCATE command used to remove table data. TRUNCATE is more efficient compare to DELETE statement with some side effects. Truncate is DDL statement, so there is no option to rollback the truncated data.You can’t TRUNCATE two tables as a single transaction.

When truncating a table, by default all the space is de-allocated for a table except the space defined by the MINEXTEND in table storage parameters. If you don’t want to de-allocate the currently allocated extend then use REUSE STORAGE clause with TRUNCATE statement.

     TRUNCATE TABLE <table name> REUSE STORAGE;

If you have chance to rollback or want to remove selective data, then you should use the DELETE statement. Delete statement generates a great amount of undo and redo information. This side effect causes DELETE statement takes more time compare to TRUNCATE

Truncate statement sets the high water mark of a table back to zero. On the other hand, DELETE statement does not change the high water mark.

You can’t TRUNCATE primary key defined parent table that is referenced by enabled foreign key constraints in child table – even the child table contains zero row. In this case oracle throw an error.