整理下delete,drop和truncate的区别
一、相同点:
truncate和不带where自居的delete,以及drop都会删除表内的数据
二、不同点:
1、truncate和delete只删除数据不删除表的结构(定义),而drop语句将删除表的结构被依赖的约束(constrain),触发器(trigger),索引(index);依赖于该表的存储过程/函数将保留,但是变为invalid状态。
2、delete命令是DML,删除的数据将存储在系统回滚段中,需要的时候,数据可以回滚恢复。
而truncate,drop命令是DDL,删除的数据是操作立即生效,原数据不放到rollback segment中,不能回滚,数据不可以回滚恢复。
3、delete命令,不会自动提交事务,操作会触发trigger;而truncate,drop命令,执行后会自动提交事务,操作不触发trigger。
4、速度:一般来说:drop > truncate > delete
因为delete语句不影响表所占用的extent,高水位(high watermark)保持原位置不动;而drop语句将表所占用的空间全部释放,truncate语句缺省情况下将空间释放到minextents个extent,除非使用reuse storage;否则truncate会将高水位复位,因为默认情况下truncate table = truncate table drop storage;
PS:使用select语句查询数据时,数据库会扫描高水位线以下的数据块,因为高水位线没有变化,所以扫描的时间不会减少,所以才会出现使用delete删除数据以后,查询的速度还是和delete以前一样。
以下这个例子操作,就是为了提供删除之后的查询速度:
问题:有一个拥有1亿条数据的表,只需要保留其中的5条,其他删除,如何做?
答:这就需要用truncate table来搞定了,如下:
select 5条数据 into #临时表 from 1亿条数据的牛X表;
truncate table 1亿条数据的牛X表; --让它牛X ,不到10毫秒干掉它。
insert 1亿条数据的牛X表 select * from #临时表;
drop table #临时表;
5、安全性:小心使用drop和truncate,尤其没有备份的时候,否则哭都来不及使用上;
想删除部分数据行用delete,注意带上where子句,回滚段要足够大;
想删除表,当然用drop;
想保留表而将所有数据删除,如果和事务无关,用truncate即可;
如果和事务有关,或者想触发trigger,还是用delete;
如果是整理表内部的碎片,可以用truncate跟上reuse stroage,再重新导入/插入数据。
6、truncate只能对table,delete可以是table,view,synonym;
truncate table的对象必须是本模式下的,或者有drop any table的权限;
而delete则是对象必须是本模式下的,或被授予delete on schema.table 或 delete any table的权限。
7、不能truncate一个带有外键的表,如果要删除首先要取消外键,然后删除;
truncate table删除表中的所有行,但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值,请改用delete。
如果要删除表定义及其数据,请使用drop table语句。对于froeing key约束引用的表,不能使用truncate table,而应使用不带where子句的delete语句。由于truncate table不记录在日志中,所有它不能激活触发器。