Oracle---关于truncate与 delete 区别

下面会用到的语句:

      set timing on;  //显示sql语句的执行时间

      analyze table t compute statistics; //分析统计

       select table_name,blocks from user_tables where table_name = 'T'; //查询T表所占的块数量

 

1.新建一个t表.

create table t(
    id   int,
    name varchar2(30)
);

2.创建一个存储过程插入1W条数据.

begin
    for i in 1..10000 loop 
       insert into t values(i,dbms_random.string('A',30));
    end loop;
end;

3.来count(*)一下

select count(*) from t;

COUNT(*)
-------------------
10000

4.比较

 (1)使用delete清空表   属于DML语言支持 rollback

delete from t;

然后分析,查询一下 t 表段名和块的数量

//分析T表的执行并统计
analyze table t compute statistics;
//查询表空间占用的块数量
select segment_name,blocks from dba_segments where segment_name=upper('t');
SEGMENT_NAME     BLOCKS
------------    ----------
 T                 64

//这里进行rollback是为使用truncate清空数据做准备.
rollback;

  使用DELETE删除表中数据:发现T表依旧占用着 64个块

(2)使用truncate清空表    属于DDL,不支持rollback

truncate table t;

  分析:

//分析T表执行并统计
analyze table t compute statistics;
//查询T表占用的块数量
select segment_name,blocks from dba_segments where segment_name=upper('t');
SEGMENT_NAME     BLOCKS
------------    ----------
 T                 8

 使用truncate清空表中数据,发现:T表的块被清理剩下8个块了

 delete from t1;

已删除10000行。

已用时间:  00: 00: 00.04


truncate table t1;

表被截断。

已用时间:  00: 00: 00.01

truncate的用时<delete的用时    这个数值可能有点不直观,当数据量比较大的时候,就很直观了,下面演示一下删除10W条数据

比较10W条数据, delete和truncate的速度.

  

 create table t(id int,name varchar2(30));

表已创建。

已用时间:  00: 00: 00.00

 //插入10W条数据
 begin
    for i in 1..100000 loop
       insert into t values(i,dbms_random.string('A',30));
     end loop;
 end;

PL/SQL 过程已成功完成。
已用时间:  00: 00: 05.79


//使用delete删除
delete  from t;
已删除100000行。
已用时间:  00: 00: 00.35

rollback;

回退已完成。
已用时间:  00: 00: 00.62

commit;
提交完成。
已用时间:  00: 00: 00.00

//使用truncate删除
truncate table t;
表被截断。
已用时间:  00: 00: 00.01

结论:可以看到

           delete删除10W条数据用时   00:00:00.35

           truncate删除10W条数据用时:  00:00:00.01

 

Oracle中truncate和delete的区别

一是truncate速度快，无法回滚，因为truncate不是dml语句；

二是truncate能降低HWM, 而delete 无法降低HWM，因此无法表在用delete删除，表的大小没有改变！

HWM (High Water Mark):高水位标记, delete删除数据,不会减少表所占用的块的数量,而truncate删除表数据,会回收该表中不存放数据的块.

 

 

 

附:

从10g开始，oracle开始提供Shrink的命令，假如我们的表空间中支持自动段空间管理 (ASSM),
 就可以使用这个特性缩小段，即降低HWM。这里需要强调一点，
 10g的这个新特性，仅对ASSM表空间有效，否则会报 ORA-10635: Invalid segment or tablespace type。
 
 SQL> create tablespace ts_auto datafile 'd:\ts_auto.dbf' size 100m extent management local segment space management auto;
 
 alter table table_name enable row movement ;
 
语法：
alter table <table_name> shrink space [ <null> | compact | cascade ];
alter table <table_name> shrink space compcat;
收缩表，相当于把块中数据打结实了，但会保持 high water mark;
alter table <tablespace_name> shrink space;
收缩表，降低 high water mark;
alter table <tablespace_name> shrink space cascade;
收缩表，降低 high water mark，并且相关索引也要收缩一下下。