Truncating is an all-or-nothing process, meaning that it cannot be used with a WHERE clause. The TRUNCATE TABLE statement in Oracle is used to delete all records from a table quickly and efficiently, while preserving the table structure. It performs the same function as a DELETE statement without a WHERE clause. Oracle 12c introduced the CASCADE clause of the TRUNCATE TABLE command, which allows you to recursively truncate tables in a hierarchy.
This requires that all referential integrity constraints involved be defined using the ON DELETE CASCADE clause. Although this can be done using Delete from table_name where conditions are used, truncating is faster than Delete Query. The STORAGE clause allows you to choose to delete or reuse the storage freed by truncated rows and associated indexes, if any. Oracle Database changes the NEXT storage parameter of the table to be the size of the last extent removed from the segment in the truncation process.
Truncating removes empty space and reduces database size, whereas deleting doesn't and leaves the GUID. For a table with a small number of rows, the DELETE statement works well, but using the TRUNCATE statement is a more efficient way to delete data, especially if the table contains a large number of rows. The MATERIALIZED VIEW LOG clause specifies whether a materialized view record defined in the table should be preserved or purged when the table is truncated. In Oracle, truncating a table is a quick way to delete records from a table if you don't have to worry about rollback.
One of the reasons is that when the table is truncated, it doesn't affect any of the table's indexes, triggers, or dependencies.
Leave Reply