oracle中的truncate与delelte

liuwang126

浏览: 184107 次
性别:
来自: 南京

最近访客更多访客>>

wj196

edanda

gumuzhiyu

jierlu

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

Oracle 数据结构 SQL Windows 浏览器

delete: Use the DELETE statement to remove rows from a table, a partitioned table, a view's base table, or a view's partitioned base table.

truncate:Use the TRUNCATE statement to remove all rows from a table or cluster. By default, Oracle also deallocates all space used by the removed rows except that specified by the MINEXTENTS storage parameter and sets the NEXT storage parameter to the size of the last extent removed from the segment by the truncation process.

truncate的功能可以拆分为:dropping + re-creating a table，但truncate的效率要高。因为truncate无须考虑表索引、约束、触发器等的重建。

另外truncate执行后，无法回滚（roll back）

HWM（high water mark，最高水位线）：The high water mark is divides a segment into used blocks and free blocks Blocks below the high water mark (used blocks) have at least once contained data. This data might have been deleted. Since Oracle knows that blocks beyond the high water mark don't have data, it only reads blocks up to the high water mark in a full table scan（FTS）.在oracle10g中增加了调整HWM的命令：alter table xxx shrink space。

以下网上达人总结的truncate与delete区别：

1、在功能上，truncate是清空一个表的内容，它相当于delete from table_name。

2、delete是dml操作，truncate是ddl操作；因此，用delete删除整个表的数据时，会产生大量的roolback，占用很多的rollback segments，而truncate不会。

3、在内存中，用delete删除数据，表空间中其被删除数据的表占用的空间还在，便于以后的使用，另外它是?假相?的删除，相当于windows中用delete删除数据是把数据放到回收站中，还可以恢复，当然如果这个时候重新启动系统（OS或者RDBMS），它也就不能恢复了！
而用truncate清除数据，内存中表空间中其被删除数据的表占用的空间会被立即释放，相当于windows中用shift+delete删除数据，不能够恢复！

4、truncate 调整high water mark 而delete不；truncate之后，TABLE的HWM退回到 INITIAL和NEXT的位置（默认）delete 则不可以。

5、truncate 只能对TABLE，delete 可以是table,view,synonym。

6、TRUNCATE TABLE 的对象必须是本模式下的，或者有drop any table的权限而 DELETE 则是对象必须是本模式下的，或被授予 DELETE ON SCHEMA.TABLE 或DELETE ANY TABLE的权限。

7、在外层中，truncate或者delete后，其占用的空间都将释放。

8、truncate和delete只删除数据，而drop则删除整个表（结构和数据）。

在删除大数据量时(一个表中大部分数据时),

可以采用以下步骤：

1、先将不需要删除的数据复制到一个临时表中

2、trunc table 表

3、将不需要删除的数据复制回来。

1,?DELETE??(删除数据表里记录的语句) ??
??
DELETE?FROM表名?WHERE?条件; ??
??
注意：删除记录并不能释放ORACLE里被占用的数据块表空间.?它只把那些被删除的数据块标成unused. ??
??
如果确实要删除一个大表里的全部记录,?可以用?TRUNCATE?命令,?它可以释放占用的数据块表空间 ??
TRUNCATE?TABLE?表名;? ??
此操作不可回退.??

1, DELETE  (删除数据表里记录的语句)



DELETE FROM表名 WHERE 条件;



注意：删除记录并不能释放ORACLE里被占用的数据块表空间. 它只把那些被删除的数据块标成unused.



如果确实要删除一个大表里的全部记录, 可以用 TRUNCATE 命令, 它可以释放占用的数据块表空间

TRUNCATE TABLE 表名; 

此操作不可回退.

Java代码

http://www.chinaunix.net?作者:oraix??发表于：2004-11-28?23:02:53?? ??
??
注意:这里说的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,再重新导入/插入数据?? ??
???

1.delete产生rollback，如果删除大数据量的表速度会很慢，同时会占用很多的rollback segments .truncate 是DDL操作，不产生rollback，速度快一些.
　　
　　Truncate table does not generate rollback information and redo records so it is much faster than delete.
　　
　　In default, it deallocates all space except the space allocated by MINEXTENTS unless you specify REUSE STORAGE clause.
　　
2.不从tablespace中腾出空间,需要
　　
　　ALTER TABLESPACE AAA COALESCE; 才有空间
　　
3.truncate 调整high water mark 而delete不.truncate之后，TABLE的HWM退回到 INITIAL和NEXT的位置（默认）
　　
　　delete 则不可以。
　　
4.truncate 只能对TABLE
　　
　　delete 可以是table,view,synonym
　　
5.TRUNCATE TABLE 的对象必须是本模式下的，或者有drop any table的权限而 DELETE 则是对象必须是本模式下的，或被授予 DELETE ON SCHEMA.TABLE 或DELETE ANY TABLE的权限

二、 truncate是DDL語言.
delete是DML語言

DDL語言是自動提交的.
命令完成就不可回滾.

truncate的速度也比delete要快得多. 所以，delete 后要commit work，而truncate就不需要啦。

三、 truncate 会把 highwatermark 回归至 0 ... 当下一次再插入新资料时就会快一些啦。

所以一般都是在 temp table 上使用的，不过要注意就是 truncate 不能在 pl/sql 上使用，要用 dynamic SQL 才可以。

四、

当你不再需要该表时，用 drop;
当你仍要保留该表，但要删除所有记录时，用 truncate;
当你要删除部分记录时（always with a WHERE clause), 用 delete.

五、

1.TRUNCATE TABLE

Index也會刪掉不是指drop index

sys@DEMO> create index

1、TRUNCATE在各种表上无论是大的还是小的都非常快。如果有ROLLBACK命令Delete将被撤销，而TRUNCATE则不会被撤销。

　　2、TRUNCATE是一个DDL语言，向其他所有的DDL语言一样，他将被隐式提交，不能对TRUNCATE使用ROLLBACK命令。

　　3、TRUNCATE将重新设置高水平线和所有的索引。在对整个表和索引进行完全浏览时，经过TRUNCATE操作后的表比Delete操作后的表要快得多。

　　4、TRUNCATE不能触发任何Delete触发器。

　　5、不能授予任何人清空他人的表的权限。

　　6、当表被清空后表和表的索引讲重新设置成初始大小，而delete则不能。

　　7、不能清空父表。

　　TRUNCATE TABLE (schema)table_name Drop(REUSE) STORAGE

　　在默认是 Drop STORAGE 当使用Drop STORAGE时将缩短表和表索引，将表收缩到最小范围，并重新设置NEXT参数。REUSE STORAGE不会缩短表或者调整NEXT参数

　　在特殊情况下使用 REUSE STORAGE

　　一个实际应用的典型例子：你用sqlldr加载一个1000万记录的数据表，但是加载了多一半的时候你发现有问题，这个时候你想清空表重新加载。那么最好 reuse storage ，这样再次加载就不需要再次寻找空闲空间了。