Truncate, delete without WHERE clause, and drop both delete table data.
Differences:
1. differentiate from the deleted content:
Truncate and delete: delete only data. Do not delete the table structure (Definition)
The drop statement deletes the table structure, constrain, trigger, and index. Stored Procedures/functions that depend on the table are retained, but it changes to the invalid status.
2. Statement types:
The delete statement is DML, which is put into the rollback segement and takes effect only after the transaction is committed. If a trigger exists, it is 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. Trigger is not triggered.
3. Identify the impact of table space from the following aspects:
The delete statement does not affect the extent used by the table. The high watermark statement keeps the original position unchanged.
Drop statement to release all the space occupied by the table
By default, the truncate statement releases the space to the extent of minextents, unless reuse storage is used; and The truncate will reset the high water line (back to the beginning ).
4. Differences in slave speed:
Generally, drop> truncate> Delete
5. security considerations:
Be careful when using drop and truncate, especially when there is no backup.
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
Delete all data if you want to keep the table. If it is not related to the transaction, use truncate. If it is related to the transaction or you want to trigger the trigger, use Delete.
If you want to organize fragments in the table, you can use truncate to keep up with the reuse Stroage, and then re-import/insert data.
The difference between the truncate Delete drop command.
