Difference between TRUNCATE and DELETE?

Question

TRUNCATE and DELETE commands does the same job, in both the cases data is manipulated, then why does the DELETE command comes under DML commands and TRUNCATE command comes under DDL commands?

Answer 1

DELETE

1. DELETE is a DML Command.
2. DELETE statement is executed using a row lock, each row in the table is locked for deletion.
3. We can specify filters in where clause
4. It deletes specified data if where condition exists.
5. Delete activates a trigger because the operation are logged individually.
6. Slower than truncate because, it keeps logs.
7. Rollback is possible.

TRUNCATE

1. TRUNCATE is a DDL command.
2. TRUNCATE TABLE always locks the table and page but not each row.
3. Cannot use Where Condition.
4. It Removes all the data.
5. TRUNCATE TABLE cannot activate a trigger because the operation does not log individual row deletions.
6. Faster in performance wise, because it doesn't keep any logs.
7. Rollback is not possible.

Answer 2

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. The main points that put TRUNCATE in the DDL camp on Oracle, are:

1. TRUNCATE can change storage parameters (the NEXT parameter), and those are part of the object definition - that's in the DDL camp.
2. TRUNCATE does an implicit commit, and cannot be rolled back (flashback aside) - most (all?) DDL operations in Oracle do this, no DML does

Answer 3

Well Basically you know the DML AND DDL concept the above difference is just keep in mind but the key difference is that Truncate only manuplates the data without affecting a table structure and key constraints but on the other hand Delete will affect the table with a where conditions.