Difference between ORACLEDELETE & amp; TRUNCATE

Usage: deletefromtablename; truncatetabletablename; similarities: delete and truncate can clear table data. Differences: delete statements are used for tables and views. delete can delete a single piece of data; delete is a DML language. truncate can be used only for tables. truncate can be used to delete the entire table.

Usage: delete from tablename; truncate table tablename; similarities: delete and truncate can clear table data. Differences: delete statements are used for tables and views; delete can delete a single data entry. delete is a DML language. truncate can only be used for tables. truncate can only be used to delete the entire table.

Usage:

Delete from tablename;

Truncate table tablename;

BothSimilarities:

Delete and truncate can be used to clear table data;

BothDifferences:

The delete statement is used for tables, views, and so on. delete can be used to delete a single piece of data. delete is a DML language;

Truncate can only be used for tables. truncate deletes the whole table data. truncate is a DDL Definition Language;

That is to say, after a delete operation is deleted, data can be rolled back and rolled back. However, after a truncate operation is deleted, data cannot be recovered.

Execution efficiency:

The efficiency of truncate statement deletion is much higher than that of delete statement.

Implementation Principles:

Delete: before deleting a piece of data, copy the data to the Undo segment. To delete hundreds or tens of millions of data entries, copy the data to the Undo segment. Someone will ask, before the deletion, why should we copy the data to the revocation segment? Do not forget to use rollback to restore the data after the deletion. The recovered data should be found in the revocation Segment Based on the log file and then replied. There is one more copy operation in the middle, and an additional security guarantee is added, so the loss point efficiency is positive;

While

The truncate statement is directly deleted without the need to copy to the Undo segment. The efficiency is improved significantly, but risks also exist. For example, if the data is deleted by mistake, the deletion of data is irrecoverable because it is DDL and the background implicitly commits transactions. Use caution.