Truncate and DELETE statements in Oracle
First of all, truncate command:
Syntax: TRUNCATE table table;
The data in the table is emptied and the storage space is freed.
It is automatically committed after it is run, including other uncommitted sessions, so once emptied cannot be rolled back.
Only the creator of the table or any other user (such as a DBA) who has permission to delete any table can clear the table.
TRUNCATE TABLE Dept30;
Table truncated.
------------------------------------------------------------------------------------------------------------
Here's a look at the difference between the truncate command and the delete:
1, truncate on various tables whether large or small are very fast. If there is a rollback command delete will be revoked, and truncate will not be revoked.
2. Truncate is a DDL language, and as with all other DDL languages, he will be implicitly committed and cannot use the rollback command with truncate.
3. Truncate will reset the high-level line and all indexes. When a full table and index are fully browsed, the table after the truncate operation is much faster than the table after the delete operation.
4. Truncate cannot trigger any delete triggers.
5. You cannot grant anyone permission to empty another's table.
6. When the table is emptied, the index of the table and table is reset to the initial size, and delete cannot.
7. Cannot empty the parent table. TRUNCATE Table (Schema) table_name drop (reuse) STORAGE The default is drop STORAGE when using Drop STORAGE will shorten the table and table indexes, shrink the table to a minimum, and reset the next parameter. Reuse storage does not shorten the table or adjust next parameters in special cases using reuse ST
The DELETE statement performs the deletion by deleting one row from the table at a time and saving the row's delete operation as a transaction record in the log for the rollback operation.
TRUNCATE table deletes all the data pages from the table at once and does not log the individual deletions into the journal, and deleting rows is not recoverable. Delete triggers related to the table are not activated during the removal process. Execution speed is fast.
===========================================================
TRUNCATE table is functionally the same as a DELETE statement without a 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 one row at a time and records an entry in the transaction log for each row that is deleted. TRUNCATE table deletes data by releasing the data page used to store the table data, and records the release of the page only in the transaction log.
TRUNCATE table deletes all rows in the table, but the table structure and its columns, constraints, indexes, and so on, remain unchanged. The count value used for the new row identity is reset to the seed of the column.
If you want to preserve the identity count value, use DELETE instead.
If you want to delete the table definition and its data, use the DROP table statement.
Truncate and DELETE statements in Oracle
