I have summarized main difference between Truncate and Delete command in Oracle.
|DDL command||DML command|
|Cannot be rolled back||Can be rolled back|
|Associated with commit||Need to commit explicitly after|
|Will not fire trigger associated with the table||Will fire trigger associated with the table|
|Cannot be used with where clause||Where clause can be used|
|Reset the high water mark||No association with high water mark|
|Generally executes faster than delete||Generally executes slower than truncate|
|Cannot run truncate for a parent table with constraints||Delete can be applied after deleting the child records|
Note that TRUNCATE table has two variants
1. Truncate table table_name;
Truncate table table_name drop storage;
(drop storage is by default)
Here Truncate reset the high water mark
2. Truncate table table_name REUSE storage;
In this case Oracle will not reset the high water mark and same storage space will allocated for new entries in future.