CODEWITHSUNDEEP
sqloracle-databaseddl
Kan
Kan

Reputation: 213

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 ?

Upvotes: 20

Views: 24081

Answers (4)

Vazexon
Vazexon

Reputation: 1

It's not a secret that TRUNCATE TABLE works as a combo of DROP TABLE and CREATE TABLE statements. DROP TABLE (DDL) + CREATE TABLE (DDL) -> TRUNCATE TABLE (DDL)

Yes, it removes data, but it's just a result of DROP TABLE. No DML at all. This answer looks simpler and quite obvious so I think it should be here.

Upvotes: 0

Flow.NK
Flow.NK

Reputation: 408

A simple practical explaination can be :

Truncate reinitializes the identity by making changes in data definition therefore it is DDL, whereas Delete only delete the records from the table and doesn't make any changes in its Definition that's why it is DML.

Like Create a Table Names and Insert Some Initial records.

Created table Inserted Some Records

Delete all records(Without where Clause) and Insert records again. Identity is not reinitialized.

Delete All Records and insert again

Now Truncate Table and Reinsert the records.

Truncate table and reinsert records

Upvotes: 9

Mussa
Mussa

Reputation: 312

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.

Upvotes: 12

Mureinik
Mureinik

Reputation: 311823

TRUNCATE resets the high water mark of the table, effectively eliminating all the previously existing rows. Treating it as a DDL statement allows it to be super-fast, as it allows it to function without retaining undo (rollback) information like DML statements.

Upvotes: 11

Related Questions