SQL

droptruncatedelete区别

delete执行删除的过程是每次从表中删除一行,并且同时将该行的删除操作作为事务记录在日志(redo 和 undo 表空间)中保存,以便进行进行回滚和重做操作。truncate则一次性地从表中删除所有的数据,并且不会把删除操作记录记入日志保存,因此无法通过 rollback 恢复。并且在删除的过程中不会激活与表有关的删除触发器。执行速度快。

truncate后,这个表和索引所占用的空间会恢复到初始大小,delete操作不会减少表或索引所占用的空间,drop语句会将表所占用的空间全释放掉。

执行速度:drop>truncate>delete

truncate只能应用于 table,而delete可以是 table 或者 view。

truncate与不带 where 条件的delete只删除数据,不会删除表结构,而drop会删除表的结构以及被依赖的约束(constrain)、触发器(trigger)和索引(index),但依赖于该表的存储过程/函数将被保留,不过其状态会变为:invalid

delete语句为 DML(Data Manipulation Language),这个操作会被放到 rollback segment中,事务提交后才生效。如果有相应的 tigger,执行的时候将被触发。

truncatedrop是 DLL(Data Definition Language),会隐式提交,操作立即生效,原数据不放到 rollback segment中,不能回滚,不会触发触发器。

delete语句每次删除一行,并在事务日志中为所删除的每行记录一项。truncate通过释放存储表数据所用的数据页来删除数据,并且只在事务日志中记录页的释放。

truncate新行标识所用的计数值重置为该列的种子。如果想保留标识计数值,请改用 delete。如果要删除表定义及其数据,请使用 dropTABLE 语句。

delete语句不影响表所占用的extent,高水线(high watermark)保持原位置不变。

truncate会删除表中所有记录,并且将重新设置高水线和所有的索引,缺省情况下将空间释放到minextents个extent,除非使用reuse storage,。不会记录日志,所以执行速度很快,但不能通过rollback撤消操作(如果一不小心把一个表truncate掉,也是可以恢复的,只是不能通过rollback来恢复)。

对于外键(foreign key)约束引用的表,不能使用 truncatetable,而应使用不带 where 子句的 delete语句。

总结

如果想删除部分数据用delete + where子句,回滚段要足够大

如果想删除表,用drop

如果想保留表而将所有数据删除,如果和事务无关,用truncate即可

如果和事务有关,或者想触发trigger,还是用delete

如果是整理表内部的碎片,可以用truncate跟上reuse stroage,再重新导入/插入数据

qin

取消

感谢您的支持,我会继续努力的!

扫码支持
扫码支持
扫码打赏

打开支付宝扫一扫,即可进行扫码打赏哦