Explain the differences between DELETE, TRUNCATE, and DROP in Oracle

Explain the differences between DELETE, TRUNCATE, and DROP in Oracle
Preface
In the past, when I was working on a project, I seldom paid attention to the details in the knowledge point. Today, I have the opportunity to differentiate several common functions in Oracle. Syntax: delete from table_name
Truncate table table_name
The drop table table_name statement differs from the delete from statement that can be followed by write conditions. The truncate statement is not followed by drop statements.
The delete from record is deleted one by one, and each deleted line of record is logged, while the truncate deletes the entire page at a time. Therefore, only the page is released in the log. In short, delete from updates log, truncate basically does not update the log, and less transaction log space is used.
After deleting an empty table from the delete from statement, an empty page is retained, and no page is left in the truncate statement.
When the DELETE statement is executed using the row lock, the rows in the table are locked for deletion. Truncate always locks tables and pages, rather than locking rows.
If there is an auto-incremental id column generated by identity, after delete from, it will still increase from the last number, that is, the seed will not change, and after truncate, the seed will restore the initial state.
Truncate does not trigger the delete trigger, because the truncate operation does not record the deletion of each row. To sum up, truncate and delete only the data and do not delete the table structure (Definition). The drop statement will delete the structure of the table by the constrain, trigger ), index; the stored procedure/function that depends on the table will be retained, but will change to invalid status.
The delete statement is dml, which is placed in the rollback segement and takes effect only after the transaction is committed. If a trigger exists, the trigger is triggered during execution. Truncate, drop is ddl, the operation takes effect immediately, the original data is not placed in rollback segment, cannot be rolled back, the operation does not trigger.
The delete statement does not affect the extent used by the table. The high watermark statement 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 is used. Truncate will reset the high water line (back to the beginning ).
Speed. Generally, drop> truncate> delete.
Security: Use drop and truncate with caution, especially when no backup is available. Otherwise, it will be too late to cry.
To delete some data rows, use delete. Note that the where clause is included. The rollback segment must be large enough. To delete a table, use drop. If you want to retain the table and delete all the data, use truncate if it is not related to the transaction. If it is related to a transaction or you want to trigger a trigger, delete is used. 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.