16

I have an interview question, which was asked during my interview. I answered the question, but interviewer was not so convinced with my answer. So, anyone please correct me with my understanding ?

Q. Why Truncate is DDL Where as Delete is DML ? Both do almost same job (removing rows)

Ans. When we are using Truncate, we are de-allocating the whole space allocated by the data without saving into the undo-table-space. But, in case of Delete, we are putting all the data into undo table-space and then we are deleting all the data.

Please, if anyone knows best answer for the above, please explain.

Improve this question
1
  • Comments are not for extended discussion; this conversation has been moved to chat.
    – Paul White ♦
    Commented Aug 29, 2017 at 15:29

3 Answers 3

Reset to default
17

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 (the NEXT parameter), and those are part of the object definition - that's in the DDL camp.
  • TRUNCATE does an implicit commit, 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.
Improve this answer
2
  • When you create a table, you can specify things like what tablespace it goes in, initial size, next extent size, etc. Those are storage attributes. Yes, they're part of a table's (or index, or partition) metadata.
    – Mat
    Commented Mar 13, 2013 at 19:25
  • Note that the SQL standard lists TRUNCATE in the chapter "Data manipulation" together with INSERT, DELETE and UPDATE.
    – user1822
    Commented Apr 15, 2017 at 12:32
2

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, updateas the all work on a rows i.e. they all are row level commands.

Improve this answer
1

Delete deletes the data, Truncate also does the same. Then why truncate is a DDL command since it is acting on data, ideally it should also be under DML. This has been most asked in interviews. It is because when we apply truncate internally it drops the table first then re-creates the schema/structure of that table. It is because of this Truncate is a DDL command..So Truncate is DROP+CREATE action

Improve this answer
1
  • 1
    Can you please share any refrence where this nature of truncate is documented ?
    – RahulDeep Attri
    Commented Dec 30, 2021 at 11:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.