《oracle 中truncate和delete的区别》由会员分享，可在线阅读，更多相关《oracle 中truncate和delete的区别（6页珍藏版）》请在金锄头文库上搜索。
1、OracleOracle 中中 truncatetruncate 和和 deletedelete 的区别的区别Oracle 中 truncate 和 delete 的区别删除表中的数据的方法有 delete,truncate,它们都是删除表中的数据,而不能删除表结构,delete 可以删除整个表的数据也可以删除表中某一条或 N 条满足条件的数据,而truncate 只能删除整个表的数据,一般我们把 delete 操作收作删除表,而 truncate 操作叫作截断表.truncate 操作与 delete 操作对比操作回滚高水线空间效率Truncate 不能下降回收快delete可以不变不回收慢
2、下面分别用实例查看它们的不同1.回滚首先要明白两点1.在 oracle 中数据删除后还能回滚是因为它把原始数据放到了undo 表空间,2.DML 语句使用 undo 表空间,DDL 语句不使用 undo,而 delete 是 DML语句,truncate 是 DDL 语句,别外 DDL 语句是隐式提交.所以 truncate 操用不能回滚,而 delete 操作可以.两种操作对比(首先新建一个表,并插入数据)SQL create table t2 (3 i number4 );Table created.SQL insert into t values(10);SQL commit;Commi
3、t complete.SQL select * from t;I-10Delete 删除,然后回滚SQL delete from t;1 row deleted.SQL select * from t;no rows selected#删除后回滚SQL rollback;Rollback complete.SQL select * from t;I-10Truncate 截断表,然后回滚.SQL truncate table t;Table truncated.SQL rollback;Rollback complete.SQL select * from t;no rows selected
4、可见 delete 删除表还可以回滚,而 truncate 截断表就不能回滚了.(前提是 delete 操作没有提交)2.高水线所有的 Oracle 表都有一个容纳数据的上限（很象一个水库历史最高的水位） ，我们把这个上限称为“high water mark”或 HWM。这个HWM 是一个标记(专门有一个数据块用来记录高水标记等)，用来说明已经有多少数据块分配给这个表. HWM 通常增长的幅度为一次 5个数据块.delete 语句不影响表所占用的数据块, 高水线(high watermark)保持原位置不动truncate 语句缺省情况下空间释放,除非使用 reuse storage; tru
5、ncate 会将高水线复位下面对两种操作对比SQL analyze table t estimate statistics;Table analyzed.SQL select segment_name,blocks from dba_segments where segment_name=upper(t);SEGMENT_NAME BLOCKS- -T 24SQL select table_name,blocks,empty_blocks from user_tables where table_name=upper(t);TABLE_NAME BLOCKS EMPTY_BLOCKS- - -
6、T 20 3USER_TABLES.BLOCKS 列代表该表中曾经使用过得数据库块的数目，即水线。注意：USER_TABLES.BLOCKS EMPTY_BLOCKS (20+3=23)比DBA_SEGMENTS.BLOCKS 少一个数据库块，这是因为有一个数据库块被保留用作表头。DBA_SEGMENTS.BLOCKS 表示分配给这个表的所有的数据库块的数目。USER_TABLES.BLOCKS 表示已经使用过的数据库块的数目(水线)。Delete 删除表,SQL delete from t;10000 rows deletedSQL commit;Commit complete.SQL an
7、alyze table t estimate statistics;Table analyzed.SQL select table_name,blocks,empty_blocks from user_tables where table_name=upper(t);TABLE_NAME BLOCKS EMPTY_BLOCKS- - -T 20 3Truncate 截断表SQL truncate table t;Table truncated.SQL analyze table t estimate statistics;Table analyzed.SQL select table_name
8、,blocks,empty_blocks from user_tables where table_name=upper(t);TABLE_NAME BLOCKS EMPTY_BLOCKS- - -T 0 7可见,delete 表,BLOCK(高水线)不变,而 truncate 表 BLOCKS(高水线)变为 0现在我们也看到 blocks+empty_blocks=7,也就是 oracle 分配区时默认一次 7+1(表头)=8 个 blocks;高水线的作用: HWM 对数据库的操作有如下影响：a)全表扫描通常要读出直到 HWM 标记的所有的属于该表数据库块，即使该表中没有任何数据。b)即使
9、 HWM 以下有空闲的数据库块，键入在插入数据时使用了append 关键字，则在插入时使用 HWM 以上的数据块，此时 HWM 会自动增大。因此高水线是 oracle 优化时一个重要的参数3.空间既然高水线用来说明已经有多少数据块分配给这个表，那么高水线也可理解为表的空间占用。即使 delete 将表中的数据全部删除，HWM 还是为原值，所以还有那么多的空间分配给这个表，即它的空间还没有回收，而 truncate 表后高水线变为 0，那现在它就表示没有分配空间，即它的空间被回收了。4效率要想查看 delete,truncate 那个效率更高，先构建一个大表，然后查看它们分别对些表删除所需的时间
10、。有个相当形象的比喻：领导给你两本书让你扔掉,delete 就是你守在复印机前,把书一页页撕下来复印一份,再一页页扔到垃圾桶里,truncate 就是直接把两本书扔到垃圾桶里，那个快那个慢不言而喻。先在表中插入 100000 条记录，并打开时间SQL set timing on;SQL begin2 for i in 1.100000 loop3 insert into t values(10);4 commit;5 end loop;6 end;7 /PL/SQL procedure successfully completed.Elapsed: 00:01:12.50Delete 删除表SQL delete from t;100000 rows deleted.Elapsed: 00:00:20.09Truncate 截断表#先把表回滚SQL rollback;Rollback complete.Elapsed: 00:00:17.36SQL select count(*) from t;COUNT(*)-100000Elapsed: 00:00:00.01SQL truncate table t;Table truncated.Elapsed: 00:00:00.20可见删除同一个大小的表，delete 用了 20.09 秒，而 truncate 只用了 0.2 秒.
