The truncate command is a powerful tool that can be used to delete all rows from a table. It does not, however, delete columns, indexes, constraints, or the schema. In Oracle databases, deleting data does not automatically reclaim disk space. The database will retain its storage until an administrative action is taken in the table space. This can be a major issue in development environments that require the creation of a new database every 6-8 months and the deletion of the old one.
The truncate command can be used to delete tables organized by indexes and temporary tables. When truncating a temporary table, only rows created during the current session are removed. The TRUNCATE TABLE statement removes all rows from a table, but the structure of the table and its columns, constraints, indexes, etc. remain. To delete the table definition in addition to its data, use the DROP TABLE statement.
The truncate table statement invalidates the pointer in sysindexes so that data is instantly deleted even from indexes without having to rebuild it. It is important to note that if the data is pre-classified in the CI, then it should be in place before any bulk insert; otherwise, create the CI (and NICs) after insertion. Another suggestion is to delete and re-create all indexes if the amount of data added exceeds the amount of existing data by 25%. This may not be relevant if you truncate the table first. When using SSIS for a database update process, it is important to consider which action will be faster: truncating first and then dropping the index or truncating first and then truncating? Or does it matter what you do first?The answer is that it does not matter what order you do these actions in.
ALTER INDEX REBUILD on a truncated and therefore empty table is useless, so you need to modify your plan accordingly. Truncation is also a recursive operation that will delete all child tables, grandchild tables, etc., using the specified options. However, if the minimum extent (together with the initial value) is large enough, this space is NOT freed even after truncation. Truncation is not possible when a foreign key references a table or when tables are used in replication or with indexed views. TRUNCATE TABLE locks the entire table to delete data from a table; therefore, this command also uses less transaction space than DELETE. To truncate a partitioned table, the table and indexes must be aligned (partitioned in the same partition function).
TRUNCATE TABLE is similar to DELETE without a WHERE clause; however, TRUNCATE TABLE is faster and uses fewer system log and transaction resources. If you specify CASCADE, Oracle Database will also truncate all child tables that reference a table with an ON DELETE CASCADE referential constraint enabled. Truncating some tables with around 1.75 million rows can be faster than deleting all rows with DELETE when there are numerous triggers, indexes, and other dependencies. When a truncation is issued in a table, Oracle deallocates all the space used by the deleted rows except for that specified by the MINEXTENTS storage parameter. You can disable all non-clustered indexes, truncate, insert, and then rebuild all non-clustered indexes. Sets the NEXT storage parameter to the size of the last extent removed from the segment by the truncation process. When you truncate a table, Oracle Database automatically removes all data from its indexes and any materialized view direct path INSERT information held in association with it.
In conclusion, using TRUNCATE TABLE can reduce fall rates by freeing up disk space quickly and efficiently.
