ITPub博客

首页 > Linux操作系统 > Linux操作系统 > truncate,delete区别[转]

truncate,delete区别[转]

原创 Linux操作系统 作者:frog-qs 时间:2011-02-25 10:31:31 0 删除 编辑

truncate,delete区别

truncate table命令将快速删除数据表中的任何记录,但保留数据表结构。这种快速删除和delete from 数据表的删除全部数据表记录不相同,delete命令删除的数据将存储在系统回滚段中,需要的时候,数据能够回滚恢复,而truncate命令删除的数据是不能够恢复的。

truncate操作 同没有where条件的delete操作十分相似
1
、无论truncate大表还是小表速度都非常快。delete要产生回滚信息来满足回滚需求,truncate是不产生的。
2
truncateDDL语句进行隐式提交,不能进行回滚操作
3
truncate重新设定表和索引的HWM(高水标记),由于全表扫描和索引快速扫描都要读取所有的数据块知道HWM为止,所以全表扫描的性能不会因为delete而提高,但是经过truncate操作后速度会很快
4
truncate不触发任何delete触发器
5
、不能赋给某个用户truncate其它用户表的权限。如果需要trucate其它用户表的权限必须对该用户赋DROP ANY TABLE权限
6
、当表被truncate后,这个表和索引所占用的空间会恢复到初始大小,而delete操作不会减少表或索引所占用的空间。
7
、不能truncate一个带有外键的表,如果要删除首先要取消外键,然后再删除。

 

truncatedelete的区别

truncate table命令将快速删除数据表中的所有记录,但保留数据表结构。这种快速删除与delete from 数据表的删除全部数据表记录不一样,delete命令删除的数据将存储在系统回滚段中,需要的时候,数据可以回滚恢复,而truncate命令删除的数据是不可以恢复的

可以做一个测试


建一个带有自增字段的表,加入100万数据

然后分别用TRUNCATEDELETE删除全部数据

然后再向表里插入一条数据


最直观是:

1.TRUNCATE TABLE
是非常快的

2.TRUNCATE
之后的自增字段从头开始计数了,而DELETE的仍保留原来的最大数值


………………………………………………………………………………………………
注意:这里说的delete是指不带where子句的delete语句

相同点

truncate
和不带where子句的delete, 以及drop都会删除表内的数据


不同点
:
1. truncate
delete只删除数据不删除表的结构(定义
)
    drop
语句将删除表的结构被依赖的约束(constrain),触发器(trigger),索引(index); 依赖于该表的存储过程/函数将保留,但是变为invalid状态
.
2.delete
语句是dml,这个操作会放到rollback segement,事务提交之后才生效;如果有相应的trigger,执行的时候将被触发
.
   truncate,drop
ddl, 操作立即生效,原数据不放到rollback segment,不能回滚. 操作不触发
trigger.
3.delete
语句不影响表所占用的extent, 高水线(high watermark)保持原位置不动

 
显然drop语句将表所占用的空间全部释放

  truncate
语句缺省情况下见空间释放到 minextents extent,除非使用reuse storage;   truncate会将高水线复位(回到最开始
).
4.
速度,一般来说
: drop>; truncate >; delete
5.
安全性:小心使用drop truncate,尤其没有备份的时候.否则哭都来不及

使用上,想删除部分数据行用delete,注意带上where子句. 回滚段要足够大
.
想删除表,当然用
drop
想保留表而将所有数据删除. 如果和事务无关,truncate即可. 如果和事务有关,或者想触发trigger,还是用
delete.
如果是整理表内部的碎片,可以用truncate跟上reuse stroage,再重新导入/插入数据


语句实例:

truncate table wap_cms_cp_user

 

数据删除 deletedroptruncate

相同点:
truncate
和不带where子句的delete, 以及drop都会删除表内的数据 

不同点

1. truncate
delete只删除数据不删除表的结构(定义
    drop
语句将删除表的结构被依赖的约束(constrain),触发器(trigger),索引(index); 依赖于该表的存储过程/函数将保留,但是变为invalid状态


2.delete
语句是dml,这个操作会放到rollback segement,事务提交之后才生效;如果有相应的trigger,执行的时候将被触发

   truncate,drop
ddl, 操作立即生效,原数据不放到rollback segment,不能回滚. 操作不触发
trigger. 
3.delete
语句不影响表所占用的extent, 高水线(high watermark)保持原位置不动
 
 
显然drop语句将表所占用的空间全部释放
 
  truncate
语句缺省情况下见空间释放到 minextents extent,除非使用reuse storage;   truncate会将高水线复位(回到最开始
). 

4.
速度,一般来说
: drop>; truncate >; delete

5.
安全性:小心使用drop truncate,尤其没有备份的时候
.
使用上,想删除部分数据行用delete,注意带上where子句. 回滚段要足够大

想删除表,当然用
drop 
想保留表而将所有数据删除. 如果和事务无关,truncate即可. 如果和事务有关,或者想触发trigger,还是用
delete. 
如果是整理表内部的碎片,可以用truncate跟上reuse stroage,再重新导入/插入数据


6.
使用方法:drop table tablenamedelete from tablename
truncate table tablename

总结:


1
deletetruncate只删除表中数据,而drop删除表

2
、速度上
drop>; truncate >; delete
3
droptruncate无备份,delete有备份可回滚

 

关于oracle使用delete删除的问题

问题:

oracle,使用delete删除数据以后,数据库的存储容量不会减少,而且使用delete删除某个表的数据以后,查询这张表的速度和删除之前一样,不会发生变化。

原因:

因为oralce有一个HWM高水位,它是oracle的一个表使用空间最高水位线。当插入了数据以后,高水位线就会上涨,但是如果你采用delete语句删除数据的话,数据虽然被删除了,但是高水位线却没有降低,还是你刚才删除数据以前那么高的水位。除非使用truncate删除数据。那么,这条高水位线在日常的增删操作中只会上涨,不会下跌,所以数据库容量也只会上升,不会下降。而使用select语句查询数据时,数据库会扫描高水位线以下的数据块,因为高水位线没有变化,所以扫描的时间不会减少,所以才会出现使用delete删除数据以后,查询的速度还是和delete以前一样。

 

解决方案:

1.首先导出表,然后truncate这张表,最后导入这张表。

2.在存储空间当中移动表,但是由于rowid会被打乱,所以需要重建索引.

3.如果是oracle 10g.可是直接更新表的高水位线。

对应的SQL

9i中:
create table aa_bak as select * from aa where record_time > sysdate - 10;
truncate table aa;
insert into aa select * from aa_bak;
drop table aa_bak;

10g 版本
alter tablename enable row movement;
alter tablename shrink space;

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22779291/viewspace-687912/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-10-27

  • 博文量
    132
  • 访问量
    244003