A detailed explanation of the differences between Oracle Delete and truncate

Grammar
Delete from AA
TRUNCATE TABLE AA

difference
1.delete from the back can write the condition, truncate not.

2.delete from records are deleted, each row of records will go into the log, and truncate delete the entire page at once, so the day to the inside only record page release, in short, delete from the update log, truncate basic No, the use of less transaction log space.

3.delete from the time the table is deleted, an empty page is retained and truncate does not leave any pages in the table.

4. When a DELETE statement is executed with a row lock, the rows in the table are locked for deletion. Truncate always locks tables and pages instead of locking rows.

5. If there is an identity-generated self-increment ID column, the delete from is still incremented from the last number, that is, the seed is unchanged, and after truncate, the seed resumes its initial start.

6.truncate triggers for delete are not triggered because the truncate operation does not record individual row deletions.

Summarize
1.truncate and delete only delete data without deleting the structure of the table (definition)
The drop statement will delete the structure of the table that is dependent on the constraint (constrain), trigger (trigger), index (indexed); Stored procedures/functions that depend on the table are preserved, but become invalid states.

The 2.delete statement is DML, which is put into the rollback segement, and the transaction is committed before it takes effect; if there is a corresponding trigger, execution will be triggered.
Truncate,drop is DDL, the operation takes effect immediately, the original data is not placed in the rollback segment and cannot be rolled back. Operation does not trigger trigger.

The 3.delete statement does not affect the extent occupied by the table, and the high waterline (watermark) remains in its original position.
Apparently the drop statement frees all the space occupied by the table.
Truncate statement by default see space released to minextents extent unless reuse storage is used; Truncate will reset the high watermark (back to the beginning).

4. Speed, in general: drop> truncate > Delete.

5. Security: Use Drop and truncate carefully, especially when there is no backup. Otherwise, it's too late to cry.

6. On the use, want to delete some data rows with delete, note with the WHERE clause.     The rollback segment should be large enough. Want to delete the table, of course with drop
You want to keep the table and delete all the data. If it is unrelated to the transaction, use truncate. If it is related to a transaction, or if you want to trigger trigger, use the Delete
If you are defragmenting the inside of the table, you can use truncate to keep up with reuse stroage and re-import/insert the data.

A detailed explanation of the differences between Oracle Delete and truncate