详解Oracle DELETE和TRUNCATE 的区别

yao000

　　语法
　　delete from aa
　　truncate table aa
　　区别
　　1.delete from后面可以写条件，truncate不可以。
　　2.delete from记录是一条条删的，所删除的每行记录都会进日志，而truncate一次性删掉整个页，因此日至里面只记录页释放，简言之，delete from更新日志，truncate基本不，所用的事务日志空间较少。
　　3.delete from删空表后，会保留一个空的页，truncate在表中不会留有任何页。
　　4.当使用行锁执行 DELETE 语句时，将锁定表中各行以便删除。truncate始终锁定表和页，而不是锁定各行。
　　5.如果有identity产生的自增id列，delete from后仍然从上次的数开始增加，即种子不变，而truncate后，种子会恢复初始。
　　6.truncate不会触发delete的触发器，因为truncate操作不记录各个行删除。
　　总结
　　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,尤其没有备份的时候.否则哭都来不及。
　　6.使用上,想删除部分数据行用delete,注意带上where子句. 回滚段要足够大.     想删除表,当然用drop
　　想保留表而将所有数据删除. 如果和事务无关,用truncate即可. 如果和事务有关,或者想触发trigger,还是用delete
　　如果是整理表内部的碎片,可以用truncate跟上reuse stroage,再重新导入/插入数据。
　　转载地址：http://www.cnblogs.com/simplefrog/archive/2012/07/30/2615169.html
　　----------------------------------------------------------------------------------------
　　truncate,delete区别
　　truncate table命令将快速删除数据表中的任何记录，但保留数据表结构。这种快速删除和delete from 数据表的删除全部数据表记录不相同，delete命令删除的数据将存储在系统回滚段中，需要的时候，数据能够回滚恢复，而truncate命令删除的数据是不能够恢复的。
　　truncate操作 同没有where条件的delete操作十分相似
　　1、无论truncate大表还是小表速度都非常快。delete要产生回滚信息来满足回滚需求,而truncate是不产生的。
　　2、truncate是DDL语句进行隐式提交，不能进行回滚操作
　　3、truncate重新设定表和索引的HWM（高水标记），由于全表扫描和索引快速扫描都要读取所有的数据块知道HWM为止,所以全表扫描的性能不会因为delete而提高，但是经过truncate操作后速度会很快
　　4、truncate不触发任何delete触发器
　　5、不能赋给某个用户truncate其它用户表的权限。如果需要trucate其它用户表的权限必须对该用户赋DROP ANY TABLE权限
　　6、当表被truncate后，这个表和索引所占用的空间会恢复到初始大小，而delete操作不会减少表或索引所占用的空间。
　　7、不能truncate一个带有外键的表，如果要删除首先要取消外键，然后再删除。
　　truncate与delete的区别
　　truncate table命令将快速删除数据表中的所有记录，但保留数据表结构。这种快速删除与delete from 数据表的删除全部数据表记录不一样，delete命令删除的数据将存储在系统回滚段中，需要的时候，数据可以回滚恢复，而truncate命令删除的数据是不可以恢复的
　　可以做一个测试
　　建一个带有自增字段的表，加入100万数据
　　然后分别用TRUNCATE和DELETE删除全部数据
　　然后再向表里插入一条数据
　　最直观是：
　　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
　　数据删除 delete、drop、truncate
　　相同点:
　　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 tablename，delete from tablename，truncate table tablename
　　总结：
　　1、delete和truncate只删除表中数据，而drop删除表
　　2、速度上drop>; truncate >; delete
　　3、drop和truncate无备份，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;
　　注意:这里说的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,再重新导入/插入数据
　　6：delete是DML语句，不会自动提交。
　　drop，truncate都是DDL语句，执行后会自动提交
　　7：truncate 只能对TABLE
　　delete 可以是table,view,synonym
　　8：TRUNCATE TABLE 的对象必须是本模式下的，或者有drop any table的权限， 而DELETE 则是对象必须是本模式下的，或被授予 DELETE ON SCHEMA.TABLE 或DELETE ANY TABLE的权限 。
　　# delete/truncate 只删除数据不删除表，索引的结构。
　　drop 将删除表的结构被依赖的 index/constrain/trigger，依赖于该表的 procedure/function 将保留，但是变为 invalid 状态。
　　#
　　delete 是 dml，写 rollback segement，可回滚，速度慢，事务提交之后才生效。在 9i 满足 undo_retention 条件下可使用 flashback。一次性大批量数据的 delete 可能导致回滚段急剧扩展从而影响到数据库，慎用。触发 trigger。
　　truncate/drop 是 ddl，隐式提交，不写 rollback segment，不能回滚，速度快。9i 不能使用 flashback。不触发 trigger。
　　#
　　delete 不影响表所占用的 extent，HWM 保持原位置不动，即使删除的是最靠近 HWM 的数据。delete 其实也可以释放空间，但是不降低 HWM，delete 后 block 的空闲空间达到 pct_used，就可以重用。
　　truncate 缺省情况下将空间（表和索引）释放到 minextents 个 extent，除非使用 reuse storage。truncate 会将高水线复位（回到最开始）。
　　drop 将表所占用的空间全部释放，segment 不存在，无所谓 HWM 的概念。
　　#
　　truncate/drop 的对象必须是本模式下的，或者被授予 drop any table 的权限，但 drop any table 权限不能 truncate/drop sys 的表。
　　delete 的对象必须是本模式下的，或者被授予 delete on SCHEMA.table 或 delete any table 的权限，但 delete any table 权限不能 delete sys 的表
　　转载地址:http://blog.sina.com.cn/s/blog_9d4799c70101625z.html