What are the similarities and differences of truncate,delete,drop?

This article to share with you is about truncate,delete,drop similarities and differences is what, Xiaobian think quite practical, so share to everyone to learn, I hope you can read this article after some harvest, not much to say, follow Xiaobian to see it.

Note: delete here refers to delete statements without where clauses

same point

truncate and delete without a where clause, as well as drop, delete data within a table

Difference:

1. truncate and delete delete only data without deleting table structure (definition)

The drop statement removes constraints, triggers, and indexes on which the structure of the table depends; stored procedures/functions that depend on the table remain, but become invalid.

2. delete statement is dml, this operation will be put into the rollback segmentation, the transaction committed only after the 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.

The delete statement does not affect the extent occupied by the table, and the high watermark remains in place.

Obviously, the drop statement frees up all the space occupied by the table

The truncate statement frees space to minextents by default, unless reuse storage is used; truncate resets the high watermark (back to the beginning).

4. Speed, in general: drop>; truncate >; delete

5. Security: Be careful with drop and truncate, especially if there is no backup. Otherwise, it's too late to cry.

If you want to delete some data rows, use delete, note that you have a where clause. The rollback segment should be large enough.

If you want to delete a table, drop it.

You want to keep the table but delete all data. If unrelated to the transaction, use truncate. If it's transaction-related, or if you want to trigger, delete.

If you are defragmenting the table, you can use truncate to keep up with reuse stroage, and then reimport/insert data

The above is what the similarities and differences of truncate,delete,drop are. Xiaobian believes that some knowledge points may be seen or used in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.