Note: The delete statements without the where clause share the same points: truncate, delete without the where clause, and drop both delete table data.
Note: The delete statements without the where clause share the same points: truncate, delete without the where clause, and drop both delete table data.
Differences:
1. truncate and delete: delete only data. Do not delete the table structure (Definition)
The drop statement deletes the constraints (constrain), triggers (triggers), and indexes that the table structure is dependent on.
Stored Procedures/functions will be retained, but will become invalid.
2. The delete statement is dml, which is placed in the rollback segement and takes effect only after the transaction is committed. If a corresponding trigger exists,
It will be triggered during execution.
Truncate, drop is ddl, and the operation takes effect immediately. The original data is not stored in rollback segment and cannot be rolled back. The operation is not triggered.
Trigger.
3. The delete statement does not affect the extent used by the table, and the high watermark keeps the original position unchanged.
Obviously, the drop statement releases all the space occupied by the table.
The truncate statement is released to the minextents extent by default, unless reuse storage; truncate
Will reset the high water line (back to the beginning ).
4. Speed. Generally, drop> truncate> delete
5. Security: Be careful when using drop and truncate, especially when there is no backup. Otherwise, it will be too late to cry.
To delete some data rows, use delete. Note that the where clause should be included. The rollback segment should be large enough.
To delete a table, use drop
You want to keep the table and delete all the data. If it is not related to the transaction, use truncate. If it is related to the transaction or you want to trigger the trigger
Yes. delete is used.
If you are sorting out the fragments in the table, you can use truncate to keep up with the reuse stroage, and then re-import/insert data/
Practical application:
Delete from table; // delete all data
Truncate table; // The auto_increatement modulation starts from 0, actually starting from 1
