sql - SQL 中的 TRUNCATE 和 DELETE 有什么区别

Question

TRUNCATE有什么区别和 DELETE在 SQL 中？

如果您的答案是特定于平台的，请指出。

Answer 1

这是差异列表。我已经强调了 Oracle 特定的特性，希望社区也可以添加其他供应商的特定差异。大多数供应商常见的差异可以直接放在标题下方，差异在下方突出显示。

总体概述

如果您想快速删除表中的所有行，并且您确实确定要这样做，并且您没有针对表的外键，那么 TRUNCATE 可能会比 DELETE 更快.

必须考虑各种特定于系统的问题，详情如下。

语句类型

删除是 DML，截断是 DDL ( What is DDL and DML? )

提交和回滚

因供应商而异

SQL*Server

截断可以回滚。

PostgreSQL

截断可以回滚。

甲骨文

因为 TRUNCATE 是 DDL，所以它涉及两次提交，一次在语句执行之前，一次在语句执行之后。因此截断不能回滚，截断过程中的失败无论如何都会发出提交。

但是，请参阅下面的闪回。

空间开垦

删除不恢复空间，截断恢复空间

甲骨文

如果您使用 REUSE STORAGE 子句，则不会取消分配数据段，如果要使用数据重新加载表，这会稍微提高效率。高水位标记被重置。

行范围

删除可用于删除所有行或仅删除行的子集。截断删除所有行。

甲骨文

对表进行分区时，可以单独截断各个分区，因此可以部分删除表的所有数据。

对象类型

删除可以应用于表和集群内的表。截断仅适用于表或整个集群。 (可能是特定于 Oracle 的)

数据对象标识

甲骨文

删除不会影响数据对象 id，但 truncate 会分配一个新的数据对象 id，除非自创建以来从未对表进行过插入即使是回滚的单个插入也会导致在截断时分配新的数据对象 id .

闪回 (Oracle)

闪回可跨删除工作，但截断可防止闪回到操作之前的状态。

但是，从 11gR2 开始，FLASHBACK ARCHIVE 功能允许这样做，Express Edition 除外

Use of FLASHBACK in Oracle
http://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_flashback.htm#ADFNS638

特权

多变的

甲骨文

可以将表上的删除授予另一个用户或角色，但不能在不使用 DROP ANY TABLE 授予的情况下进行截断。

重做/撤销

删除会生成少量重做和大量撤消。 Truncate 生成的每个值都可以忽略不计。

索引

甲骨文

截断操作使不可用的索引再次可用。删除没有。

外键

当启用的外键引用表时，不能应用截断。删除处理取决于外键的配置。

表锁定

甲骨文

截断需要独占表锁，删除需要共享表锁。因此，禁用表锁是一种防止对表进行截断操作的方法。

触发器

DML 触发器不会在截断时触发。

甲骨文

DDL 触发器可用。

远程执行

甲骨文

不能通过数据库链接发出截断。

标识列

SQL*Server

截断重置 IDENTITY 列类型的序列，删除不会。

结果集

在大多数实现中，DELETE语句可以将被删除的行返回给客户端。

例如在 Oracle PL/SQL 子程序中，您可以:

DELETE FROM employees_temp
WHERE       employee_id = 299 
RETURNING   first_name,
            last_name
INTO        emp_first_name,
            emp_last_name;