Delete 和 Truncate的区别

 

You can delete all rows of a table or all rows in a group of clustered tables so that the
table (or cluster) still exists, but is completely empty. For example, consider a table that
contains monthly data, and at the end of each month, you need to empty it (delete all
rows) after archiving its data.

To delete all rows from a table, you have the following options:
■ Use the DELETE statement.
■ Use the DROP and CREATE statements.
■ Use the TRUNCATE statement.
These options are discussed in the following sectionsTruncating Tables and Clusters
13-6 Oracle Database Administrator’s Guide

Using DELETE
You can delete the rows of a table using the DELETE statement. For example, the
following statement deletes all rows from the emp table:
DELETE FROM emp;
If there are many rows present in a table or cluster when using the DELETE statement,
significant system resources are consumed as the rows are deleted. For example, CPU
time, redo log space, and undo segment space from the table and any associated
indexes require resources. Also, as each row is deleted, triggers can be fired. The space
previously allocated to the resulting empty table or cluster remains associated with
that object. With DELETE you can choose which rows to delete, whereas TRUNCATE
and DROP affect the entire object.

Using DROP and CREATE
You can drop a table and then re-create the table. For example, the following
statements drop and then re-create the emp table:
DROP TABLE emp;
CREATE TABLE emp ( ... );
When dropping and re-creating a table or cluster, all associated indexes, integrity
constraints, and triggers are also dropped, and all objects that depend on the dropped
table or clustered table are invalidated. Also, all grants for the dropped table or
clustered table are dropped.

Using TRUNCATE
You can delete all rows of the table using the TRUNCATE statement. For example, the
following statement truncates the emp table:
TRUNCATE TABLE emp;
Using the TRUNCATE statement provides a fast, efficient method for deleting all rows
from a table or cluster. A TRUNCATE statement does not generate any undo
information and it commits immediately. It is a DDL statement and cannot be rolled
back. A TRUNCATE statement does not affect any structures associated with the table
being truncated (constraints and triggers) or authorizations. A TRUNCATE statement
also specifies whether space currently allocated for the table is returned to the
containing tablespace after truncation.
You can truncate any table or cluster in your own schema. Any user who has the DROP
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.

 

See the Oracle Database
Security Guide for information about auditing.
See Also: Oracle Database SQL Reference for syntax and other
information about the DELETE statementEnabling and Disabling Triggers
Managing Schema Objects 13-7
A hash cluster cannot be truncated, nor can tables within a hash or index cluster 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 statement or drop and re-create the table.
The REUSE STORAGE or DROP STORAGE options of the TRUNCATE statement 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. The storage
parameters for a truncated table, cluster, or associated indexes are not changed as a
result of the truncation.

 

 

下面是个人总结了
1、TRUNCATE速度比delete要快，因为不产生撤销数据(undo inforamtion)，立即提交

2、TRUNCATE是一个DDL命令，将被隐式提交,不能对TRUNCATE使用ROLLBACK命令。Delete是DML命令，可以rollback

3、TRUNCATE将重新设置高水平线和所有的索引。在对整个表和索引进行完全浏览时，经过TRUNCATE操作后的表比DELETE操作后的表要快得多。
4、TRUNCATE不能触发任何DELETE触发器。
5、不能授予任何人清空他人的表的权限。
6、当表被清空后表和表的索引讲重新设置成初始大小，而delete则不能。
7、不能清空父表。