The truncate command is a powerful tool that can be used to remove all the rows in a table. It is important to note, however, that the structure of the table and its columns, constraints, indexes, etc. are maintained. To delete the table definition in addition to its data, the DROP TABLE statement must be used.
Truncate can be used on tables organized by indexes and temporary tables. When truncating a temporary table, only rows created during the current session are removed. The truncated table statement will invalidate the pointer in sysindexes so that data is instantly deleted even from indexes without any reconstruction. This means that the indexes will still exist but without any data pages.
It is also important to note that truncate does not remove columns, indexes, constraints, or schema. If a regular or indexed table contains LOB columns, all LOB data and LOB index segments are truncated. Deleting rows with the TRUNCATE statement can be faster than deleting all rows with the DELETE statement, especially if the table has numerous triggers, indexes, and other dependencies. When you truncate a table, Oracle Database automatically removes all data from the table's indexes and any materialized view direct path INSERT information maintained in association with the table.
If you have specified more than one free list for the object to be truncated, the REUSE STORAGE clause also removes any assignment of free lists to instances and resets the maximum watermark at the beginning of the first extension. It is generally not necessary to re-index after TRUNCATE, and if you do, it's much better to drop the indexes, load the data, and recreate the indexes at the end. You can get somewhat more compact and efficient indexes by deleting the indexes, truncating them, inserting the data, and recreating the indexes. To truncate a table or cluster, the table or cluster must be in the schema or you must have the DROP ANY TABLE system privilege.
A session is detached from the temporary table when the table is truncated, so storage is automatically discarded. The TRUNCATE TABLE statement is a DDL command, so it includes an implicit COMMIT, so there is no way to issue a ROLLBACK if you decide you don't want to delete the rows.
Leave Reply