相同点:
- truncate和不带where子句的delete及drop都会删除表内的数据。
- drop和truncate都是DDL语句(数据库定义语言),执行后会自动提交。
不同点:
- truncate和delete只删除数据不删除表的结构(定义)。
drop语句将删除表的结构被依赖的约束(constrain)、触发器(trigger)、索引(index);依赖于该表的存储过程/函数将保留,但是变为invalid状态。 - delete是数据库操作语言(DML),这个操作会放到rollback segement中,事务提交后才生效;如果有相应的trigger,执行的将被触发。
truncate和drop是数据库定义语言(DDL),操作立即生效,原数据不放到rollback segement中,不能回滚,操作不触发trigger。 - delete是DML语句,不会自动提交。drop/truncate是DDL语句,执行后会自动提交。
- 速度,一般来说drop>truncate>delete
- 安全性:小心使用drop和truncate,特别是在没有备份的情况下。
删除部分数据,delete,与where语句结合使用。
删除表,drop,直接drop掉整个表。
想保留表而删除所有数据,如果和事物无关,用truncate即可,如果和事物有关,或者想触发trigger,还是用delete。如果整理表内部的碎片,可以用truncate跟上reuse stroage,再重新导入或插入数据。 - delete语句不影响表所占用的extent,高水线保持原位置不动
drop语句将表所占用的空间全部释放 - TRUNCATE TABLE在功能上与不带where子句的DELETE语句相同,均删除表中的所有行。但是TRUNCATE TABLE比DELETE速度快,且使用的系统和事务日志资源少。delete语句每次删除一行,并在事务日志中为所删除的每行记录一项。truncate table通过释放存储表数据所用的数据页来删除数据,并且只在事务日志中记录页的释放。
- TRUNCATE TABLE删除表中的所有行,但表结构及其列,约束,索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值,用delete,如要删除表定义及其数据,使用DROP TABLE语句。
- 对于由FOREIFN KEY约束引用的表,不能使用TRUNCATE TABLE,应使用不带where子句的DELETE语句。由于TRUNCATE TABLE不记录在日志中,所以它不能激活触发器。
- TRUNCATE TABLE不能用于参与了索引视图的表。