Differences between Oracle truncate table and delete tabel

I have read some related posts and articles on the Internet, which are basically the same, but there are some differences in some places. Here I will summarize the differences between the two.

 

1.

Delete generates rollback and related lock information. If you delete a table with a large data volume, it takes up a lot of rollback segments. truncate is a DDL operation and does not generate rollback.

Truncate table does not generate rollback information and redo records so it is much faster than Delete.
In default, it deallocates all space within t the space allocated by minextents unless you specify reuse storage clause.

2.

Delete does not free up space from tablespace. It requires alter tablespace AAA coalesce;

3.

After truncate adjusts high water mark and delete does not. truncate, The hwm of table is returned to the positions of initial and next (default), and delete is not allowed.

4.

Truncate can only be set to table. Delete can be set to table, view, and synonym.

5.

The truncate table object must be in the current mode, or have the permission to drop any table, while delete must be in the current mode, or be authorized to delete on schema. table or delete any table.

 

6.

Truncate is the DDL language. Delete is the DML language. However, it should be noted that truncate cannot be used on PL/SQL. It can only be used with dynamic SQL.

The DDL language is automatically submitted. If the command is complete, rollback is not allowed.

Truncate is much faster than Delete.

7.

When truncate is used, the index is also deleted, but the delete operation does not.