What is the difference between DELETE and TRUNCATE?

What is the difference between DELETE and TRUNCATE?

Delete, truncate,

They all delete the data in the table, but cannot delete the table structure. delete can delete the data of the entire table or delete one or more data that meet the conditions in the table, the truncate operation can only delete the data of the entire table. Generally, the delete operation is called the delete operation, and the truncate operation is called the truncation table.

Comparison between truncate and delete operations

Operation

Rollback

High waterline

Space

Efficiency

Truncate

No

Decrease

Reclaim

Fast

Delete

Yes

Unchanged

Do not recycle

Slow

TRUNCATE and DELETE have the following differences:

1. TRUNCATE is very fast on various tables, whether large or small. If the ROLLBACK command DELETE is used, the TRUNCATE command is not used.

2. TRUNCATE is a DDL language. Like all other DDL languages, it will be implicitly submitted and cannot use the ROLLBACK command for TRUNCATE.

3. TRUNCATE will reset the high horizontal line and all indexes. When you completely browse the entire table and index, the table after the TRUNCATE operation is much faster than the table after the DELETE operation.

4. TRUNCATE cannot trigger any DELETE trigger.

5. You cannot grant anyone the permission to clear tables of others.

6. After the table is cleared, the index of the table and the table will be reset to the initial size, while the delete statement cannot.

7. The parent table cannot be cleared.