The difference between Oracle truncate and delete

First truncate is a DDL statement, and delete is a DML statement.

The deletion of truncate is much faster than delete, especially when the amount of table data is large, because Truncate does not produce undo during execution, so truncate can no longer roll back. The delete must be rolled back before commit.

Another feature of Truncate is that the table's high watermark is re-zeroed, and delete does not, which is also an advantage of truncate, which searches only for rows of data that are located in blocks below the high watermark. This has a significant impact on full table scan performance.

Truncate has a side effect, and if a table defines a primary key and the primary key is a foreign key to its child table, you cannot truncate the table, even if the child table does not have any data. In this case try to go to the truncate will report: OAR-02266. The reason is that truncate cannot roll back, preventing someone from inserting data into the child table concurrently. Delete is allowed to do this because delete generates redo information, has read consistency, and can be rolled back.

Finally, there is a way to delete the full table data: Drop table and rebuild, this situation is generally not done in the production environment, more for the development testing phase.

The difference between Oracle truncate and delete