Truncating a Data Definition Language (DDL) command is a powerful tool that can be used to reset the high watermark of a table, effectively removing all existing rows. This process is much faster than deleting rows one by one, as it does not require undo (revert) information like DML statements. Oracle classifies truncating as a DDL statement in its Concept Guide, while DELETE is classified as a DML statement. In terms of binary logging and replication, TRUNCATE TABLE is treated as DROP TABLE followed by CREATE TABLE, which is considered DDL instead of DML. To ensure that TRUNCATE TABLE creates the tablespace in its current location, users should add the directory to the innodb_directories configuration before running the command.
When used with partitioned tables, TRUNCATE TABLE preserves the partition; that is, data files and indexes are deleted and re-created, while partition definitions remain intact. Additionally, if data files or indexes have been corrupted, TRUNCATE TABLE can be used to create an empty table. Logically, TRUNCATE TABLE is similar to a DELETE statement that deletes all rows or a sequence of DROP TABLE and CREATE TABLE statements. It also closes all table handles that were opened with HANDLER OPEN. Furthermore, TRUNCATE TABLE cannot delete any data that violates FOREIGN KEY or any other restrictions.
The command is logically (but not physically) equivalent to the DELETE FROM mytable statement (without a WHERE clause).On a system with a large set of InnoDB buffers and innodb_adaptive_hash_index enabled, TRUNCATE TABLE operations can cause a temporary drop in system performance due to an LRU scan that occurs when you delete adaptive hash index entries from an InnoDB table. To prevent this issue, MySQL 8.0 remaps TRUNCATE TABLE to DROP TABLE and CREATE TABLE. In conclusion, TRUNCATE TABLE is an effective way to quickly remove all rows from a table while preserving its structure and columns, constraints, indexes, etc. It is treated for binary logging and replication purposes as DDL instead of DML and is always recorded as an instruction. Additionally, it can be used with performance schema summary tables to reset summary columns to 0 or NULL.
