Similarities and differences of Truncate,delete,drop

Similarities and differences of Truncate,delete,drop

Description: This digest is from Oracle Technology user discussion Group

truncate , the similarities and differences of Delete,drop.
Note that the delete here is the DELETE statement without the WHERE clause

Same point:truncateand delete with no WHERE clause, and drop deletes data in the table
Different points:
1.truncateand delete Deletes only the data do not delete the structure of the table (definition)
The DROP statement deletes the table's structure-dependent constraints (constrain), triggers (trigger), indexes (index); Stored procedures/functions that depend on the table are preserved, but become invalid states.
The 2.delete statement is DML, which is placed in the rollback segement, which takes effect after the transaction is committed, and if the corresponding trigger is present, the execution is triggered.
truncate, the 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 that the table occupies, and the high watermark (watermark) keeps the original position.
Obviously the drop statement frees up all the space that the table occupies
truncateStatement by default, the space is released to minextents extent unless the reuse storage is used;truncateThe high watermark will be reset (back to the beginning).
4. Speed, in general: drop>truncate> Delete
5. Security: Be careful with Drop andtruncate, especially when there is no backup. Otherwise, it's too late to cry.
Use, to delete some data rows with delete, note the WHERE clause is taken. 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 has nothing to do with the transaction, usetruncateCan. If it is related to a transaction, or if you want to trigger trigger, or delete.
If you are sorting the fragments inside the table, you can use thetruncateKeep up with reuse stroage, and then re-import/Insert Data