SQL中的TRUNCATE和DELETE之间有什么区别 [英] What's the difference between TRUNCATE and DELETE in SQL
问题描述
我回答一个关于DROP和TRUNCATE之间的区别的问题，写了一个这个问题的答案，但我认为这是一个可惜不分享，所以我会发布自己的答案我自己的问题。是甚至伦理？
编辑：如果您的答案是平台特定的，请注明。 / div>
这里有一个差异列表。我突出了Oracle的特定功能，希望社区可以添加其他供应商的具体差异。
< h1>常规概述
如果要快速删除表中的所有行，并且确实希望这样做，对表有外键，则TRUNCATE可能比DELETE快。
必须考虑各种系统特定的问题，如下所述。 / p>
语句类型
是DML，Truncate是DDL
提交和回滚
供应商变量
SQL *服务器
截断可以回滚。
PostgreSQL
截断可以回滚。
Oracle
由于TRUNCATE是DDL，因此涉及两个提交，和一个语句执行后。
但是，请参阅下面的Flashback。
空间回收
删除不能恢复空间，恢复空间
Oracle
如果使用REUSE STORAGE子句，段不被解除分配，如果要对表重新加载数据，则这可以略微更有效。高水位标记已重置。
行范围
删除只能删除一些行。 Truncate删除所有行。
Oracle
单独的分区可以单独截断，因此可以部分删除所有表的数据。
对象类型
删除可应用于集群中的表和表。截断仅适用于表或整个集群。 （可以是Oracle特定的）
数据对象身份
Oracle
删除不会影响数据对象ID，但truncate会分配一个新的数据对象id 除非自从创建以来从未对表进行过插入即使被回滚的单个插入也会导致在截断后分配一个新的数据对象标识。
Flashback（Oracle）
闪回可在删除之间使用，
但是，从11gR2开始，FLASHBACK ARCHIVE功能允许这样做，除了Express Edition
p> 在Oracle中使用FLASHBACKhttp://docs.oracle.com/cd/E11882_01/appdev.112/e41502/ adfns_flashback.htm＃ADFNS638
特权
变量
Oracle
在表上授予其他用户或角色，但是truncate不能不使用DROP ANY TABLE grant。
重做/撤消
删除会生成少量重做和大量撤销。
索引
Oracle
截断操作会将不可用的索引重新使用。不删除。
外键
当启用的外键引用表时，不能应用truncate。
表锁定
Oracle
Truncate需要独占表锁，delete需要共享表锁。因此，禁用表锁是一种防止对表执行truncate操作的方法。
/ h1>
DML触发器不会触发截断。
Oracle
可以使用DDL触发器。
执行
Oracle
不能通过数据库链接发出截断。 p>
标识栏
SQL * Server
截断重置IDENTITY列类型的序列，不删除。
I wrote up an answer to this question by mistake in response to a question about the difference between DROP and TRUNCATE, but I thought that it's a shame not to share so I'll post my own answer to my own question ... is that even ethical? :)
Edit: If your answer is platform specific can you please indicate that.
Here's a list of differences. I've highlighted Oracle-specific features, and hopefully the community can add in other vendors' specific difference also. Differences that are common to most vendors can go directly below the headings, with differences highlighted below.
General Overview
If you want to quickly delete all of the rows from a table, and you're really sure that you want to do it, and you do not have foreign keys against the tables, then a TRUNCATE is probably going to be faster than a DELETE.
Various system-specific issues have to be considered, as detailed below.
Statement type
Delete is DML, Truncate is DDL
Commit and Rollback
Variable by vendor
SQL*Server
Truncate can be rolled back.
PostgreSQL
Truncate can be rolled back.
Oracle
Because a TRUNCATE is DDL it involves two commits, one before and one after the statement execution. Truncate can therefore not be rolled back, and a failure in the truncate process will have issued a commit anyway.
However, see Flashback below.
Space reclamation
Delete does not recover space, Truncate recovers space
Oracle
If you use the REUSE STORAGE clause then the data segments are not de-allocated, which can be marginally more efficient if the table is to be reloaded with data. The high water mark is reset.
Row scope
Delete can remove only some rows. Truncate removes all rows.
Oracle
When a table is partitioned, the individual partitions can be truncated in isolation, thus a partial removal of all the table's data is possible.
Object types
Delete can be applied to tables and tables inside a cluster. Truncate applies only to tables or the entire cluster. (May be Oracle specific)
Data Object Identity
Oracle
Delete does not affect the data object id, but truncate assigns a new data object id unless there has never been an insert against the table since its creation Even a single insert that is rolled back will cause a new data object id to be assigned upon truncation.
Flashback (Oracle)
Flashback works across deletes, but a truncate prevents flashback to states prior to the operation.
However, from 11gR2 the FLASHBACK ARCHIVE feature allows this, except in Express Edition
Use of FLASHBACK in Oracle http://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_flashback.htm#ADFNS638
Privileges
Variable
Oracle
Delete can be granted on a table to another user or role, but truncate cannot be without using a DROP ANY TABLE grant.
Redo/Undo
Delete generates a small amount of redo and a large amount of undo. Truncate generates a negligible amount of each.
Indexes
Oracle
A truncate operation renders unusable indexes usable again. Delete does not.
Foreign Keys
A truncate cannot be applied when an enabled foreign key references the table. Treatment with delete depends on the configuration of the foreign keys.
Table Locking
Oracle
Truncate requires an exclusive table lock, delete requires a shared table lock. Hence disabling table locks is a way of preventing truncate operations on a table.
Triggers
DML triggers do not fire on a truncate.
Oracle
DDL triggers are available.
Remote Execution
Oracle
Truncate cannot be issued over a database link.
Identity Columns
SQL*Server
Truncate resets the sequence for IDENTITY column types, delete does not.
这篇关于SQL中的TRUNCATE和DELETE之间有什么区别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
