Why is truncate DDL?
The DML versus DDL distinction isn't as clear as their names imply, so things get a bit muddy sometimes.
Oracle clearly classifies TRUNCATE
as DDL in the Concepts Guide, but DELETE
as DML.
The main points that put TRUNCATE
in the DDL camp on Oracle, as I understand it, are:
TRUNCATE
can change storage parameters (theNEXT
parameter), and those are part of the object definition - that's in the DDL camp.TRUNCATE
does an implicitcommit
, and cannot be rolled back (flashback aside) - most (all?) DDL operations in Oracle do this, no DML does.
The fact that TRUNCATE
doesn't run ON DELETE
triggers also sets it apart from normal DML operations (but some direct path DML operations also skip triggers, so that's not a clear indicator).
That same documentation notes that DELETE
generates UNDO, but TRUNCATE
doesn't, so your statement is correct in this respects. (Note that TRUNCATE
does generate some REDO
so that the truncation can be replayed in case of restore/recovery.) But some NOLOGGING
operations can also produce reduced UNDO (not sure about none at all), so that's not a clear indicator either in my opinion.
So I'd sum it up as:
truncate
is not "transactional" in the sense that it commits and can't be rolled back, and can modify object storage attributes. So it's not ordinary DML - Oracle classifies it as DDL.delete
is an ordinary DML statement.
I think that truncate
is similar to drop
, alter
, create
as they
all work on a table i.e. they all are table level commands.
While 'delete' is similar to insert
, select
, update
as the all work on a rows i.e. they all are row level commands.