Kan Kan - 2 months ago 39
SQL Question

Why is truncate a DDL statement?

The table structure remains same after truncate statement and only records get deleted (with auto commit). Then what is the reason that truncate is a DDL statement ?


I'd add here some explanation: By default, when truncating a table , you are not just removing rows, you are implicitly telling Oracle Database to also perform the following tasks:

Deallocates all space 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.

Also it can't be a DML ,right? Where clause can't be specified or comes along with DDL statement. If truncate is a DML statement then Oracle would have allowed us to use truncate along with where clause.
Note: A WHERE clause in SQL specifies that a SQL Data Manipulation Language (DML) statement should only affect rows that meet specified criteria.