truncate vs delete

sql | databases

blog > programming > databases > truncate vs delete

TRUNCATE vs DELETE

TRUNCATE is used to delete all records from the table, records to be deleted cannot be selected (as in DELETE). This command is faster than DELETE and also frees up table space occupied by a table. TRUNCATE makes unusable indexes usable - DELETE does not.

If in this case you are using the command:

DELETE FROM [schema_name.]table_name;

you can use command instead:

TRUNCATE TABLE [schema_name.]table_name;

The differences between the TRUNCATE TABLE and DELETE FROM TABLE SQL statements can be very significant, depending on the case in which you use these commands. Below we have a brief overview of the most important differences.

SQL TRUNCATE command

1) It is a DDL (Data Definition Language) command, therefore commands such as COMMIT and ROLLBACK do not apply to this command (the exceptions here are PostgreSQL and MSSQL, whose implementation of the TRUNCATE command allows the command to be used in a transaction)
2) You cannot undo the operation of deleting records, it occurs automatically and is irreversible (except for the above exceptions - provided, however, that the operation is included in the TRANSACTION block and the session is not closed). In case of Oracle - Includes two implicit commits, one before and one after the statement is executed. Therefore, the command cannot be withdrawn while a runtime error will result in commit anyway
3) Deletes all records from the table, records cannot be limited to deletion. For Oracle, when the table is split per partition, individual partitions can be truncated (TRUNCATE) in isolation, making it possible to partially remove all data from the table
4) Frees up the space occupied by the data in the table (in the TABLESPACE - on disk). For Oracle - if you use the REUSE STORAGE clause, the data segments will not be rolled back, i.e. you will keep space from the deleted rows allocated to the table, which can be a bit more efficient if the table is to be reloaded with data. The high mark will be reset
5) Resets the SEQUENCE value assigned to the table to zero. However, the following options can be used: RESTART IDENTITY or CONTINUE IDENTITY
You can also use the CASCADE option (RESTART IDENTITY CASCADE). If CASCADE is defined, then the sequences of all affected tables are reset
6) TRUNCATE works much faster than DELETE
7) TRUNCATE only affects tables or the entire cluster (may be Oracle specific)
8) Oracle Flashback in the case of TRUNCATE prevents going back to pre-operative states
9) Oracle - TRUNCATE cannot be granted (GRANT) without using DROP ANY TABLE
10) TRUNCATE generates a negligible amount of redo and undo
11) The TRUNCATE operation makes unusable indexes usable again
12) TRUNCATE cannot be used when the enabled foreign key refers to another table, then you can:
- execute the command: DROP CONSTRAINT, then TRUNCATE, and then play it through CREATE CONSTRAINT or
- execute the command: SET FOREIGN_KEY_CHECKS = 0; then TRUNCATE, then: SET_FOREIGN_KEY_CHECKS = 1;
13) TRUNCATE requires an exclusive table lock, therefore, turning off exclusive table lock is a way to prevent TRUNCATE operation on the table
14) DML triggers do not fire after executing TRUNCATE (so be very careful in this case, you should not use TRUNCATE, if a delete trigger is defined in the table to perform an automatic table cleanup or a logon action after row deletion). On Oracle, DDL triggers are fired
15) Oracle - TRUNCATE cannot be used in the case of: database link
16) TRUNCATE does not return the number of records deleted
17) Transaction log - one log indicating page deallocation (removes data, releasing allocation of data pages used for storing table data and writes only page deallocations to the transaction log) - faster execution than DELETE. TRUNCATE only needs to adjust the pointer in the database to the table (High Water Mark) and the data is immediately deleted, therefore it uses less system resources and transaction logs
18) Performance (acquired lock) - table and page lock - does not degrade performance during execution
19) TRUNCATE takes up less transaction space than the DELETE statement
20) TRUNCATE cannot be used with indexed views
21) TRUNCATE cannot be used on tables involved in transactional replication or merge replication

SQL DELETE command

1) It is a DML (Data Manipulation Language) command, therefore the following commands are used for this command: COMMIT and ROLLBACK
2) You can undo the operation of removing records by using the ROLLBACK command
3) Deletes all or some records from the table, you can limit the records to be deleted by using the WHERE clause
4) Does not free the space occupied by the data in the table (in the TABLESPACE - on the disk)
5) Does not reset the SEQUENCE value assigned to the table
6) DELETE works much slower than TRUNCATE
7) DELETE can be applied to tables and tables in a cluster (can be Oracle specific)
8) Oracle Flashback works for DELETE
9) Oracle - For DELETE, you can use the GRANT command
10) DELETE generates a small amount of redo and a large amount of undo
11) The DELETE operation does not make unusable indexes usable again
12) DELETE in case foreign key enabled refers to another table, can (or not) be applied depending on foreign key configuration (if not), please:
- execute the command: DROP CONSTRAINT, then TRUNCATE, and then play it through CREATE CONSTRAINT or
- execute the command: SET FOREIGN_KEY_CHECKS = 0; then TRUNCATE, then: SET_FOREIGN_KEY_CHECKS = 1;
13) DELETE requires a shared table lock
14) Triggers fire
15) DELETE can be used in the case of: database link
16) DELETE returns the number of records deleted
17) Transaction log - for each deleted record (deletes rows one at a time and records an entry in the transaction log for each deleted row) - slower execution than TRUNCATE. The table may still contain blank pages after executing the DELETE statement. DELETE needs to read records, check constraints, update block, update indexes, and generate redo / undo. All of this takes time, hence it takes time much longer than with TRUNCATE
18) Performance (acquired lock) - record lock - reduces performance during execution - each record in the table is locked for deletion
19) DELETE uses more transaction space than the TRUNCATE statement
20) DELETE can be used with indexed views
21) DELETE can be used on a table used in transactional replication or merge replication

If you have comments, suggestions or need advice in relation to the above article, please send information via the form: contact

tags: truncate, delete, sql, bazy danych, oracle, postgresql, mysql, mssql, db2
date of publication: 2022-08-09