Oracle_中truncate和delete的区别

Oracle中truncate和delete的区别删除表中的数据的方法有delete,truncate,它们都是删除表中的数据,而不能删除表结构,delete可以删除整个表的数据也可以删除表中某一条或N条满足条件的数据,而truncate只能删除整个表的数据,一般我们把delete操作收作删除表,而truncate操作叫作截断表.truncate操作与delete操作对比操作回滚高水线空间效率Truncate不能下降回收快delete可以不变不回收慢下面分别用实例查看它们的不同1.回滚首先要明白两点1.在oracle中数据删除后还能回滚是因为它把原始数据放到了undo表空间,2.DML语句使用undo表空间,DDL语句不使用undo,而delete是DML语句,truncate是DDL语句,别外DDL语句是隐式提交.所以truncate操用不能回滚,而delete操作可以.两种操作对比(首先新建一个表,并插入数据)SQLcreatetablet2(3inumber4);Tablecreated.SQLinsertintotvalues(10);SQLcommit;Commitcomplete.SQLselect*fromt;I----------10Delete删除,然后回滚SQLdeletefromt;1rowdeleted.SQLselect*fromt;norowsselected#删除后回滚SQLrollback;Rollbackcomplete.SQLselect*fromt;I----------10Truncate截断表,然后回滚.SQLtruncatetablet;Tabletruncated.SQLrollback;Rollbackcomplete.SQLselect*fromt;norowsselected可见delete删除表还可以回滚,而truncate截断表就不能回滚了.(前提是delete操作没有提交)2.高水线所有的Oracle表都有一个容纳数据的上限（很象一个水库历史最高的水位），我们把这个上限称为“highwatermark”或HWM。这个HWM是一个标记(专门有一个数据块用来记录高水标记等)，用来说明已经有多少数据块分配给这个表.HWM通常增长的幅度为一次5个数据块.delete语句不影响表所占用的数据块,高水线(highwatermark)保持原位置不动truncate语句缺省情况下空间释放,除非使用reusestorage;truncate会将高水线复位下面对两种操作对比SQLanalyzetabletestimatestatistics;Tableanalyzed.SQLselectsegment_name,blocksfromdba_segmentswheresegment_name=upper('t');SEGMENT_NAMEBLOCKS----------------------------------------T24SQLselecttable_name,blocks,empty_blocksfromuser_tableswheretable_name=upper('t');TABLE_NAMEBLOCKSEMPTY_BLOCKS----------------------------------------------------T203USER_TABLES.BLOCKS列代表该表中曾经使用过得数据库块的数目，即水线。注意：USER_TABLES.BLOCKSEMPTY_BLOCKS(20+3=23)比DBA_SEGMENTS.BLOCKS少一个数据库块，这是因为有一个数据库块被保留用作表头。DBA_SEGMENTS.BLOCKS表示分配给这个表的所有的数据库块的数目。USER_TABLES.BLOCKS表示已经使用过的数据库块的数目(水线)。Delete删除表,SQLdeletefromt;10000rowsdeletedSQLcommit;Commitcomplete.SQLanalyzetabletestimatestatistics;Tableanalyzed.SQLselecttable_name,blocks,empty_blocksfromuser_tableswheretable_name=upper('t');TABLE_NAMEBLOCKSEMPTY_BLOCKS--------------------------------------------------------------------------------------------------------T203Truncate截断表SQLtruncatetablet;Tabletruncated.SQLanalyzetabletestimatestatistics;Tableanalyzed.SQLselecttable_name,blocks,empty_blocksfromuser_tableswheretable_name=upper('t');TABLE_NAMEBLOCKSEMPTY_BLOCKS------------------------------------------------------------------------------------------------T07可见,delete表,BLOCK(高水线)不变,而truncate表BLOCKS(高水线)变为0现在我们也看到blocks+empty_blocks=7,也就是oracle分配区时默认一次7+1(表头)=8个blocks;高水线的作用:HWM对数据库的操作有如下影响：a)全表扫描通常要读出直到HWM标记的所有的属于该表数据库块，即使该表中没有任何数据。b)即使HWM以下有空闲的数据库块，键入在插入数据时使用了append关键字，则在插入时使用HWM以上的数据块，此时HWM会自动增大。因此高水线是oracle优化时一个重要的参数3.空间既然高水线用来说明已经有多少数据块分配给这个表，那么高水线也可理解为表的空间占用。即使delete将表中的数据全部删除，HWM还是为原值，所以还有那么多的空间分配给这个表，即它的空间还没有回收，而truncate表后高水线变为0，那现在它就表示没有分配空间，即它的空间被回收了。4．效率要想查看delete,truncate那个效率更高，先构建一个大表，然后查看它们分别对些表删除所需的时间。有个相当形象的比喻：领导给你两本书让你扔掉,delete就是你守在复印机前,把书一页页撕下来复印一份,再一页页扔到垃圾桶里,truncate就是直接把两本书扔到垃圾桶里，那个快那个慢不言而喻。先在表中插入100000条记录，并打开时间SQLsettimingon;SQLbegin2foriin1..100000loop3insertintotvalues('10');4commit;5endloop;6end;7/PL/SQLproceduresuccessfullycompleted.Elapsed:00:01:12.50Delete删除表SQLdeletefromt;100000rowsdeleted.Elapsed:00:00:20.09Truncate截断表#先把表回滚SQLrollback;Rollbackcomplete.Elapsed:00:00:17.36SQLselectcount(*)fromt;COUNT(*)-------------------100000Elapsed:00:00:00.01SQLtruncatetablet;Tabletruncated.Elapsed:00:00:00.20可见删除同一个大小的表，delete用了20.09秒，而truncate只用了0.2秒.