Different points:
1. Differentiate from deleted content:
Truncate and delete only delete data without deleting the structure of the table (definition)
The drop statement will delete the structure of the table, the dependent constraint (constrain), the trigger (trigger), index (indexed); Stored procedures/functions that depend on the table are preserved, but become invalid states.
2. Differentiate from statement types:
The DELETE statement is DML, which is placed in the rollback segement, which takes effect after the transaction is committed, and is triggered when the corresponding trigger is executed.
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.
3. Differentiate from the effect on the table space:
The DELETE statement does not affect the extent occupied by the table, and the high waterline (hi Watermark) remains in its original position.
The drop statement frees all the space occupied by the table
The TRUNCATE statement frees space to minextents extent by default unless reuse storage is used, and truncate resets the high watermark (back to the beginning).
4. From the difference of speed:
General: drop> truncate > Delete
What is the essential difference between a database Drop command and a delete command