【DB笔试面试476】DELETE、DROP和TRUNCATE的区别是什么？

2024-02-25 20:01:39

♣ 题目部分

DELETE、DROP和TRUNCATE的区别是什么？

♣ 答案部分

DELETE、DROP和TRUNCATE的异同点如下表所示：

表 3-9 DELETE、DROP和TRUNCATE的区别

相同点	1、TRUNCATE和不带WHERE子句的DELETE及DROP都会删除表内的所有数据 2、DROP和TRUNCATE都是DDL语句，执行后会自动提交 3、表上的索引大小会自动进行维护
不同点	分类	DROP	TRUNCATE	DELETE
	是否删除表结构	删除表结构及其表上的约束，且依赖于该表的存储过程和函数等将变为INVALID状态	只删除数据不删除表的定义、约束、触发器和索引
	SQL命令类型	DDL语句，隐式提交，不能对TRUNCATE和DROP使用ROLLBACK命令		DML语句，事务提交（COMMIT）之后才生效，可以使用ROLLBACK语句撤销未提交的事务
	删除的数据是否放入回滚段（ROLLBACK SEGMENT）	否	否	是
	高水位是否下降	是	是，在宏观上表现为TRUNCATE操作后，表的大小变为初始化的大小	否，在宏观上表现为DELETE后表的大小并不会因此而改变，所以，在对整个表进行全表扫描时，经过TRUNCATE操作后的表比DELETE操作后的表要快得多
	日志的产生	少量日志	少量日志	大量日志
	是否可以通过闪回查询来找回数据	否	否	是
	是否可以对视图进行操作	是	否	是
	级联删除	不能DROP一个带有ENABLE外键的表	不能TRUNCATE一个带有ENABLE外键的表，会报错ORA-02266	可以DELETE一个带有ENABLE外键的表
	执行速度	一般来说，DROP>TRUNCATE>DELETE，DROP和TRUNCATE由于是在底层修改了数据字典，所以，无论是大表还是小表执行都非常快，而DELETE是需要读取数据到Undo，所以，对于大表进行DELETE全表操作将会非常慢
	安全性	DROP和TRUNCATE在无备份的情况下需谨慎
	使用方面	想删除部分数据行只能用DELETE且带上WHERE子句；想删除表数据及其结构则使用DROP；想保留表结构而将所有数据删除则使用TRUNCATE
	恢复方法	使用回收站恢复，闪回数据库，RMAN备份、DUL工具等	闪回数据库，RMAN备份、DUL工具等	闪回查询、闪回事务、闪回版本、闪回数据库等

DELETE和TRUNCATE都可以用来删除表中所有的记录。但二者的不同之处主要体现在以下几个方面的内容：

（1）TRUNCATE是一个DDL语句，DELETE是DML语句。

（2）TRUNCATE将被隐式提交，不能对TRUNCATE使用ROLLBACK命令，而对DML语句可以执行ROLLBACK命令来撤销未提交的事务。

（3）TRUNCATE由于是在底层修改了数据字典，所以在各种表上无论是大表的还是小表操作都非常快。DELETE操作需要写日志，而TRUNCATE不需要写日志，所以对于大表进行DELETE全表操作非常慢。

（4）TRUNCATE将重置表的高水位线，但DELETE不会重置表的高水位线，在宏观上表现为TRUNCATE操作后，表的大小变为初始化的大小，而DELETE后表的大小并不会因此而改变，所以在对整个表进行全表扫描时，经过TRUNCATE操作后的表比DELETE操作后的表要快得多，另外需要注意的是，无论TRUNCATE还是DELETE操作，表上的索引都会自动维护。

（5）TRUNCATE操作后的表不能通过闪回特性来找回，但DELETE后的表可以通过闪回特性来找回数据。

（6）TRUNCATE不能加条件删除数据；DELETE可以加条件删除。

（7）不能TRUNCATE一个带有ENABLE外键的表，会报错ORA-02266。可以DELETE一个带有ENABLE外键的表。