Truncate in Oracle
Syntax: TRUNCATE table table;
You can delete all records in a table by using the TRUNCATE statement (DDL language). When you use the TRUNCATE statement to delete data, it is usually much faster than using the DELETE statement because no fallback information is generated when the data is deleted using the TRUNCATE statement, so the execution of the TRUNCATE statement operation cannot be rolled back, and there are fewer system and transaction log resources to use.
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.
In the TRUNCATE statement, you can also use the reuse storage keyword, which means that the space occupied by the deletion of records after the record is saved, in contrast, if the drop storage keyword is used, the space occupied by the record is reclaimed immediately after the record is deleted. By default, the drop storage keyword is used.
Sql> CREATE TABLE TT (
2 tid number (4)
3);
Sql> INSERT INTO TT values (1);
1 rows have been created.
Sql> INSERT INTO TT values (2);
1 rows have been created.
Sql> INSERT INTO TT values (3);
1 rows have been created.
Sql> INSERT INTO TT values (4);
1 rows have been created.
Sql> INSERT INTO TT values (5);
1 rows have been created.
Sql> TRUNCATE TABLE TT reuse storage;
The table is truncated.
Sql> select * from TT;
Row not selected
Truncate usage in Oracle
