Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Why is truncate a DDL statement?

Tags:

sql

oracle

ddl

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 ?

like image 855
Kan Avatar asked Sep 26 '14 12:09

Kan


People also ask

How TRUNCATE is DDL?

TRUNCATE is a DDL(Data Definition Language) command and is used to delete all the rows or tuples from a table. Unlike the DELETE command, the TRUNCATE command does not contain a WHERE clause. In the TRUNCATE command, the transaction log for each deleted data page is not recorded.

Why TRUNCATE is data definition language?

In SQL, the TRUNCATE TABLE statement is a Data Definition Language (DDL) operation that marks the extents of a table for deallocation (empty for reuse). The result of this operation quickly removes all data from a table, typically bypassing a number of integrity enforcing mechanisms.

Why TRUNCATE is DML?

To achieve high performance, TRUNCATE TABLE bypasses the DML method of deleting data. Thus, it does not cause ON DELETE triggers to fire, it cannot be performed for InnoDB tables with parent-child foreign key relationships, and it cannot be rolled back like a DML operation.

Why is TRUNCATE command used?

The SQL TRUNCATE TABLE command is used to delete complete data from an existing table. You can also use DROP TABLE command to delete complete table but it would remove complete table structure form the database and you would need to re-create this table once again if you wish you store some data.


1 Answers

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.

like image 199
Mussa Avatar answered Sep 22 '22 14:09

Mussa