Comparison of the similarities and differences between Drop, Delete, and Truncate statements in the database (instance description)

Database deletion statements:
Delete: Used to Delete rows in a table (Note: You can Delete a row or Delete all rows without deleting the table (that is, the table's structure, attributes, and indexes are complete)
Syntax: Delete a row: Delete From table name Where column name = Value
Delete all rows: Delete From table name or Delete * From Table Name

Drop: Used to delete a table (Note: The structure, attributes, and indexes of the table will also be deleted .)
Syntax: Drop Table Name
Truncate: Used to Delete data in a table. (Note: Only data in the table is deleted. If the table itself is not deleted, the Delete statement is the same as the Where clause)
Syntax: Truncate Table name

Comparison of the similarities and differences between Delete statements in the database:

Similarities:

1. truncate, delete without the where clause, and drop can delete data in the table.
2. truncate and delete only delete the table data and retain the table structure

Differences:

1. truncate table tablename

Delete table content release tablespaces retain the table structure (that is, only the data in the table is deleted, and the table itself is not deleted. It is equivalent to a Delete statement that does not write a where clause). It is irrelevant to the transaction if no transaction is used.

The truncate statement releases the space to the minextents extent by default,
Unless reuse storage is used; truncate resets the high waterline (back to the beginning ).

2. delete table tablename [where clause]

Delete is a data operation language (DML). transactions cannot be submitted automatically. You need to submit the transaction with commit.
This operation will be placed in rollback segement and will take effect after the transaction is committed;
If a trigger exists, the trigger is triggered during execution.
The delete statement does not affect the extent used by the table. The high watermark statement keeps the original position unchanged.

3. drop table tablename

Drop is a Data Definition Language (DDL) that can automatically submit transactions;
The drop statement will delete the constraints, triggers, and indexes that the table structure is dependent on. [Delete table data and delete table structure at the same time ];
Stored Procedures/functions dependent on the table will be retained but changed to invalid state.
The drop statement releases all the space occupied by the table.
Data deletion speed. Generally, drop> truncate> delete

Usage:

When you no longer need this table, use drop;
Use truncate to retain the table but delete all records;
When you want to delete some records (always with a where clause), use delete.

Note:
For tables with a primary foreign key relationship, you cannot use truncate but should use a delete statement without the where clause. Because truncate is not recorded in the log, the trigger cannot be activated.

Author Han xuemin