Truncate remove all records from a table and it is much faster than delete operations. Users generally ask, how to delete all records from a table to load fresh data into it? Which is faster among these two and what is the difference between truncate and delete.
- First of all, truncate is DDL command whereas delete is DML command.
- Truncate doesn’t use any “where” conditions whereas for delete command you need to specify “where” condition to delete particular records from a table.
- Delete allow you to rollback where truncate doesn’t allow you to rollback because truncate is ddl command and commit is automatically accepted there.
- Truncate deallocate all space used by removed rows except specified by MINEXTENTS storage parameters. So table size will be almost zero or in few KB after truncate operation but after delete operations table size will not decrease, it will remain almost the same. If table size is more even after truncate then you can extract the table ddl and check initially allocated MINEXTENTS storage parameters.
How to Truncate table in Oracle?
You need to specify table name in truncate table statement. TRUNCATE TABLE table_name; The above truncate statement will remove all records from a table.