Second, understand the difference between drop, truncate and delete

Time:2021-1-16

(1) The process of delete statement is to delete a row from the table each time, and at the same time, save the deletion operation of the row as a transaction record in the log for rollback operation.

Truncatetable can delete all the data from the table at one time. It does not record the individual deletion operation record in the log, and the Deleted Row cannot be recovered. In addition, the table related delete trigger will not be activated in the process of deletion. The execution speed is fast.

(2) Space occupied by tables and indexes.

When the table is truncated, the space occupied by the table and index will be restored to the original size,

The delete operation does not reduce the space occupied by the table or index.

The drop statement releases all the space occupied by the table.

(3) Generally speaking, drop > truncate > delete

(4) Application scope.

Truncate can only be used for table; delete can be used for table and view

(5) Truncate and delete only delete data, while drop delete the whole table (structure and data).

(6) Truncate and delete without where: delete only the data without deleting the structure (definition) of the table. The drop statement will delete the constraint and trigger index that the structure of the table depends on. The stored procedure / function that depends on the table will be retained, but its status will change to invalid.

(7) The delete statement is DML (data maintain language). This operation will be put into the rollback segment and will take effect only after the transaction is committed. If there is a corresponding trigger, it will be triggered during execution.

(8) Truncate and drop are DLLs (data definition language). The operation takes effect immediately. The original data cannot be rolled back without being put into the rollback segment

(9) Drop and truncate should be used cautiously without backup. To delete some data rows, delete and pay attention to the combination of where to restrict the scope of influence. The rollback segment should be large enough. To delete a table, drop is used; if you want to keep the table and delete the data in the table, if it has nothing to do with the transaction, truncate is used. If it is related to a transaction or the teacher wants to trigger trigger trigger, delete is still used.

(10) The truncatetable table name is fast and efficient because:
Truncatetable has the same function as delete statement without where clause: both delete all rows in the table. However, truncatetable is faster than delete and uses less system and transaction log resources. The delete statement Deletes one line at a time and records an entry for each deleted line in the transaction log. Truncatetable removes data by releasing the data pages used to store table data, and only records the page release in the transaction log.

(11) Truncatetable deletes all rows in the table, but the table structure and its columns, constraints, indexes, etc. remain unchanged. The count value used for the new row ID is reset to the seed of the column. If you want to keep the identity count value, use delete instead. If you want to delete a table definition and its data, use the drop table statement.

(12) For tables referenced by foreign key constraint, truncatetable cannot be used, but delete statement without where clause should be used. Since truncatetable is not logged, it cannot activate triggers.

1、 Delete

1. Delete is a DML. When a delete operation is performed, a row is deleted from the table each time, and the deletion of the row is recorded in the redo and undo table spaces at the same time, so as to roll back and redo the operation. However, it should be noted that the table space should be large enough, and manual commit operation is required to take effect, and the operation can be undone through roll back.

2. Delete can delete the data satisfying the condition in the table according to the condition. If the where clause is not specified, all records in the table will be deleted.

3. The delete statement does not affect the extent occupied by the table, and the high water mark remains unchanged.

2、 Truncate

1. Truncate is DDL and will be implicitly committed. Therefore, it cannot be rolled back and trigger will not be triggered.

2. Truncate will delete all the records in the table, and reset the high water line and all the indexes. By default, it will free up the space to miniextents and extents, unless reusestorage is used,. The log will not be recorded, so the execution speed is very fast, but the operation cannot be undone through rollback (if a table truncates accidentally, it can be recovered, but it cannot be recovered through rollback).

3. For the table referenced by foreign key constraint, truncatetable cannot be used, but delete statement without where clause should be used.

4. Truncatetable cannot be used for tables that participate in indexed views.

3、 Drop

1. Drop is DDL and will be submitted implicitly. Therefore, it cannot be rolled back and trigger will not be triggered.

2. The drop statement removes the table structure and all data, and releases all the space occupied by the table.

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

Conclusion:

1. In terms of speed, generally speaking, drop > truncate > delete.

2. When using drop and truncate, we must pay attention to that although it can be recovered, we should be cautious in order to reduce the trouble.

3. If you want to delete some data, use delete. Pay attention to the where clause. The rollback segment should be large enough;

If you want to delete a table, drop is used;

If you want to keep the table and delete all the data, if it has nothing to do with the transaction, you can use truncate;

If it is related to a transaction, or you want to trigger trigger trigger, use delete;

If you want to defragment the internal table, you can use truncate to keep up with reusestrage, and then re import / insert the data.

summary

Here is the article about the difference between drop, truncate and delete. For more information about the difference between drop truncate and delete, please search previous articles of developer or continue to browse the following articles. I hope you can support developer more in the future!