Differences between the truncate and delete commands in Oracle databases

First, let's talk about the truncate command:
Syntax: truncate table;
The data in the table is cleared and the storage space is released.
After the task is run, the task is automatically submitted, including other uncommitted sessions. Once the task is cleared, the task cannot be rolled back.
Only the table creator or other users (such as DBA) with the permission to delete any table can clear the table.
Truncate table dept30;
Table truncated.

The following describes the differences between the truncate command and the delete command:
1. TRUNCATE is very fast on various tables, whether large or small. If the ROLLBACK command DELETE is used, the TRUNCATE command is not used.
2. TRUNCATE is a DDL language. Like all other DDL languages, it will be implicitly submitted and cannot use the ROLLBACK command for TRUNCATE.
3. TRUNCATE will reset the high horizontal line and all indexes. When you completely browse the entire table and index, the table after the TRUNCATE operation is much faster than the table after the DELETE operation.
4. TRUNCATE cannot trigger any DELETE trigger.
5. You cannot grant anyone the permission to clear tables of others.
6. After the table is cleared, the index of the table and the table is reset to the initial size, while the delete statement is not.
7. The parent table cannot be cleared. Truncate table (schema) table_name DROP (REUSE) STORAGE by default is drop storage. When drop storage is used, the TABLE and TABLE index are shortened, and the TABLE is reduced to the minimum range, and reset the NEXT parameter. Reuse storage does not shorten the table or adjust the NEXT parameter to use reuse st in special cases.

The DELETE statement deletes a row from the table each time, and saves the DELETE operation of the row as a transaction record in the log for rollback.
The truncate table deletes all data pages from the TABLE at one time and does not store the individual deletion operation records in logs. Deleting rows cannot be recovered. In addition, table-related deletion triggers are not activated during the deletion process. Fast execution speed.

The truncate table function is the same as the DELETE statement without the WHERE clause: both DELETE all rows in the TABLE. However, truncate table is faster than DELETE and uses less system and transaction log resources.
The DELETE statement deletes a row at a time and records one row in the transaction log. Truncate table deletes data by releasing the data pages used to store TABLE data, and only records the release of pages in transaction logs.
Truncate table deletes all rows in the TABLE, but the TABLE structure, its columns, constraints, and indexes remain unchanged. The Count value used by the new row ID is reset to the seed of the column.
To retain the ID Count value, use DELETE instead.
To delete TABLE definitions and data, use the drop table statement.

The above only introduces the usage of the two commands. The following describes my recommended usage. The truncate command is fast and consumes less resources, if you can determine whether the data in the current table is completely useless, you can use this command, which is quick. If the data may be useful, you 'd better back up the data. Otherwise, once you use this command, you will regret it, of course, the delete command is still advantageous. If the subsequent data is not useful but is unknown or deleted by mistake, you can use the rollback command to roll back directly, of course, the rollback command can be used to add a rollback point, locate rollback, and restore data, which is more convenient. Therefore, it is highly recommended that you use the delete command when deleting data for beginners. This is troublesome, but to be safe, this is my lesson. The misuse of the truncate command clears a table. Fortunately, this table does not matter. Otherwise, I will only thank the world. Of course, it is strongly recommended that you back up useful data before performing any operations, regardless of whether it is used or not.

For more information about Oracle, see Oracle topics page http://www.bkjia.com/topicnews.aspx? Tid = 12