To delete all rows from a table, you have three options:
1. Using the DELETE command
DELETE FROM emp;
2. Using the DROP and CREATE commands
DROP TABLE emp;
CREATE TABLE emp ( . . . );
3. Using TRUNCATE
TRUNCATE TABLE emp;
You can truncate any table or cluster in the user's associated schema. Also, any user that has the DELETE ANY TABLE system privilege can truncate a table or cluster in any schema.
Before truncating a table or clustered table containing a parent key, all referencing foreign keys in different tables must be disabled. A self-referential constraint does not have to be disabled.
As a TRUNCATE statement deletes rows from a table, triggers associated with the table are not fired. Also, a TRUNCATE statement does not generate any audit information corresponding to DELETE statements if auditing is enabled. Instead, a single audit record is generated for the TRUNCATE statement being issued.
A hash cluster cannot be truncated. Also, tables within a hash or index cluster cannot be individually truncated; truncation of an index cluster deletes all rows from all tables in the cluster. If all the rows must be deleted from an individual clustered table, use the DELETE command or drop and re-create the table.
The REUSE STORAGE or DROP STORAGE options of the TRUNCATE command control whether space currently allocated for a table or cluster is returned to the containing tablespace after truncation. The default option, DROP STORAGE, reduces the number of extents allocated to the resulting table to the original setting for MINEXTENTS. Freed extents are then returned to the system and can be used by other objects.
Alternatively, the REUSE STORAGE option specifies that all space currently allocated for the table or cluster remains allocated to it. For example, the following statement truncates the EMP_DEPT cluster, leaving all extents previously allocated for the cluster available for subsequent inserts and deletes:
TRUNCATE CLUSTER emp_dept REUSE STORAGE;
The REUSE or DROP STORAGE option also applies to any associated indexes. When a table or cluster is truncated, all associated indexes are also truncated. Also note that the storage parameters for a truncated table, cluster, or associated indexes are not changed as a result of the truncation.
See Also: See Chapter 21 for information about auditing.