The difference between

  • The DELETE statement deletes a row at a time from the table and stores the row deletion as a transaction in the log for rollback.
  • TRUNCATE TABLE deletes all data from the TABLE at one time, and individual deletion operations are not logged. Deleted rows cannot be restored. And no table-related delete triggers are activated during the delete process. Fast execution speed.
  • The drop statement frees all space occupied by the table.

conclusion

1. In speed, generally, DROP > TRUNCate > DELETE.

2. Be careful when using DROP and TRUNCate. Although it can be restored, it is still necessary to be cautious in order to reduce trouble.

3, If you want to delete some data, use delete, note where clause, rollback segment must be large enough;

If you want to drop a table, use drop; If you want to preserve the table and delete all data, use TRUNCate if the data has nothing to do with a transaction. If you want to trigger a transaction, use delete. If the table is an internal fragment, use TRUNCate to keep up with reuse stroage and import/insert data again.Copy the code

Refer to the article

  • Difference between DROP, TRUNCate, and DELETE