Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have an interview question, which being asked during my interview. I answered the question, but interviewer was not convinced with my answer. So, please let me know the what would be the correct answer.

Q. Why Truncate is DDL where as Delete is DML ?

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.

share|improve this question
1  
This question is answered in detail here. SELECT is not DML because no data is changed in the underlying table. –  Mike Fal Mar 13 '13 at 17:45
    
i read the answer, but i didn't find any clarification, why truncate said to be ddl. If you please highlight the sentence, i will appreciate. –  jWeaver Mar 13 '13 at 17:50
2  
I believe the interviewer was challenging you to defend your answer and test whether or not you understood the questions or if you were simply repeating something you read somewhere. –  Mike Fal Mar 13 '13 at 17:54
1  
@Mike: Not the same, at least as far as RDBMS. This question (I believe) is in the context of Oracle; that question is SQL Server specific. That said, any discussion of DDL/DML/whatever should probably be database-agnostic, which that question isn't. –  Jon Seigel Mar 13 '13 at 17:58
2  
Having said go ahead and repost the select=DML? question, it has already been asked here –  Jack Douglas Mar 13 '13 at 18:20

2 Answers 2

up vote 5 down vote accepted

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.
share|improve this answer
    
what do you mean by object storage attribute ?? are you talking about meta data ?? –  jWeaver Mar 13 '13 at 19:21
    
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 Mar 13 '13 at 19:25
    
thanks for your explanation !! –  jWeaver Mar 13 '13 at 19:26

truncate is auto commited and it remove data and space also. space is consider as table structure.table structure is going to change so it is DDl but delete only delete data not space so it works only on data so it DML

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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