||Oracle Tips by Burleson
The TRUNCATE TABLE Command
There is also a way to avoid the use of
rollback, thus making deletions much faster: use the TRUNCATE
command. One good feature of this command is that it can be used to
reclaim the space used by the data that was in the table. As noted
in the introduction to this section, TRUNCATE is a DDL command; once
issued, the data is gone. A TRUNCATE cannot be rolled back. The
format for this command follows.
See Code Depot
The DROP|REUSE STORAGE option allows you to
shrink the table back to its high-water mark or leave the table at
its current size. Both DROP and REUSE qualifiers also apply to
whatever index space is regained.
For tables, PRESERVE or PURGE SNAPSHOT
options allow control over a table’s snapshot logs as well.
Again, the TRUNCATE command is faster than
the DELETE command because it is a DDL command and generates no
rollback data. When using TRUNCATE on a clustered table, the data
must be removed from the entire cluster, not just the one table. Any
referential integrity constraints on a table must be disabled before
it can be truncated. Like a table DROP, a truncation is not
recoverable. If a table is truncated, you cannot roll back if you
made a mistake. Use TRUNCATE carefully.