Similarities and differences between truncate, delete, and drop

The similarities and differences between truncate, delete, and drop are as follows: 1. truncate and delete without the where clause delete and drop both delete the data in the table. Differences: 1. truncate and delete only delete data, and do not delete the table structure (Definition). The drop statement will delete the table structure as dependent on constraints) trigger, index, and the stored procedure/function dependent on the table will be retained but changed to invalid status. 2. The delete statement is dml, which is placed in the rollback segement and takes effect only after the transaction is submitted. If a corresponding trigger exists, it is triggered during execution. Truncate and drop are ddl, and the operation takes effect immediately. The original data is not stored in rollback segement. rollback is not allowed and trigger is not triggered. 3. the delete statement does not affect the extent and high watermark used by the table) keep the original location unchanged. Obviously, the drop statement will release all the space occupied by the table. The truncate statement defaults to the minextents extent, unless you use reuse storage; truncate will reset the high water line (back to the beginning ). 4. Speed. Generally: drop>, truncate> delete 5. security: Use drop and truncate with caution, especially when no backup is available. otherwise, it will be too late to cry. To delete some data rows, use delete. Note that the where clause is included. the rollback segment must be large enough. to delete a table, use drop to retain the table and delete all data. if it is irrelevant to the transaction, use truncate. if it is related to the transaction or you want to trigger the trigger, delete is used. if you are sorting the fragments in the table, you can use truncate to keep up with the reuse stroage, and then re-import/Insert the number.