Why Truncate Command Is Not DML, But DDL

One of the most debatable topic, when we talk about some oracle command is TRUNCATE command. Let it be a tech discussion or even a common interview questions-- WHY TRUNCATE COMMANS IS DDL and NOT DML. 

The reason for this can be TRUNCATE (DDL) and DELETE (DML), both removes records from the table. This leads to some ambiguity among the users, why can’t TRUNCATE be categorized under DML?

YES, it’s worth discussing this, though the Jury ORACLE has its verdict as TRUNCATE is a DDL command, but why. The verdict of Oracle is based on the fact about DML and DDL how is works and its impact over the data and structure. The reader of this article, we assume is aware of basics about DML and DDL commands.

TRUNCATE TABLE : Works like DROP TABLE followed by CREATE TABLE—that is, as DDL rather than DML.

This article i have tried to explain this debatable topic, and why the Oracle Jury has its verdict for TRUNCATE as DDL and not DML.

Consider following:

While executing a DML( INSERT / DELETE / UPDATE ), Oracle lets you perform the operation with where clause, while DDL Statements (DROP /CREATE / ALTER /TRUNCATE). while DDL does not permit a where Clause.

Every DML statement executed, COMMIT command to be used explicitly, for the changes made by the DML statements permanent, until unless AUTO COMMIT is ON. But, when you execute DDL (TRUNCATE), its implicitly AUTO COMMIT. 

Moreover, while executing a DML over a table, then database triggers, if applied on table is executed (based on the DML operations and database triggers applied). Database Triggers has no impact when TRUNCATE command is issued over a table.

With truncate, not just the records are deleted/removed but oracle also performs following task by default:

• Release space in the blocks used by the removed rows except that specified by the MINEXTENTS storage parameter.
• Sets the NEXT storage parameter to the size of the last extent removed from the segment by the truncation process So, it changes the storage definitions and changing the structure of the table by resetting the water high mark.
• Even a cursor gets effected by that truncate statement as library cache gets invalidated.

TRUNCATE command re-initializes high water mark of the table, comparatively a high performance DDL command, as compared to DELETE (DML). It TRUNCATE just like other DDL command ( DROP,ALTER,CREATE) overrides undo (rollback) information like While DELETE(DML) does effect the rollback segments, so the structure.

Following I try to explaining--in a more simpler context:

TRUNCATE just like any other DDL, reinitializes the identity by making changes in data definition therefore it is DDL, while DELETE (DML) removes records from the table and does not have any impact on the structure, so its a DML. To achieve high performance, it bypasses the DML method of deleting data.

If an Analogy then TRUNCATE & DELETE has same impact of records in table, bust as explained earlier in the this article above DDL has no Where clause, while DML can use WHERE clause in statements. 

TRUCATE states once issues there is no ROLLBACK, and it even bypassed ON DELETE triggers, if exist on table.

To read the Artile HOW TRUNCATE DIFFERE FROM DELETE to know the detail difference between these two command, despite logically both performs same operations

Although TRUNCATE TABLE is similar to DELETE, it is classified as a DDL statement rather than a DML statement. It differs from DELETE in the following ways:

• Truncate operations drop and re-create the table, which is much faster than deleting rows one by one, particularly for large tables.
• Truncate operations cause an implicit commit, and so cannot be rolled back. See Section 13.3.3, “Statements That Cause an Implicit Commit”.
• Truncation operations cannot be performed if the session holds an active table lock.
• TRUNCATE TABLE can be used with Performance Schema summary tables, but the effect is to reset the summary columns to 0 or NULL, not to remove rows.