Why is DDL truncated in Oracle

Why is DDL cut off?


I have an interview question that was asked during my interview. I answered the question, but the interviewer wasn't that convinced of my answer. So, please, will someone correct me with my understanding?

Question: Why is "Truncate" DDL? Where is "Delete" DML? Both do almost the same job (remove lines)

Ans. Using Truncate will deallocate all of the space allocated by the data without being stored in the undo table space. In the case of Delete, however, all data is moved to the undo table space and then all data is deleted.

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


Reply:


The distinction between DML and DDL isn't as straightforward as their names suggest, so things get a little mushy at times.

Oracle is clearly classified as DDL in the Concepts Guide, but as DML.

In my opinion, the most important points in the DDL camp for Oracle are:

  • Storage parameters (the parameters) can change, and these are part of the object definition - that is in the DDL warehouse.
  • is implicit and irreversible (flashback aside) - most (all?) DDL operations in Oracle do this, not DML.

The fact that no triggers are executed also sets it apart from normal DML operations (however, some direct path DML operations also skip triggers, so this is not a clear indicator).

The same documentation notes that generate UNDO, but not, so your statement is correct in this regard. (Note that some are generated so that the truncation can be replayed in the event of a restore / restore.) However, some operations can also result in a reduced UNDO (I'm not sure there is any)

So I would summarize it like this:

  • is not "transactional" in the sense that it is committed, irreversible, and can change object storage attributes. So it's not just ordinary DML - Oracle classifies it as DDL.
  • is a common DML statement.



I think this is similar to how they all work on a table ie they are all table level commands. While 'erase' is similar to all of the work on a row, that is, they are all row level commands.

We use cookies and other tracking technologies to improve your browsing experience on our website, to show you personalized content and targeted ads, to analyze our website traffic, and to understand where our visitors are coming from.

By continuing, you consent to our use of cookies and other tracking technologies and affirm you're at least 16 years old or have consent from a parent or guardian.

You can read details in our Cookie policy and Privacy policy.