测试一

我们建立一个表test1并向表中插入数据使表有一定的大小。

SQL> create table test1 as select * from dba_objects;

表已创建。

SQL> insert into test1 select * from test1;

已创建68624行。

SQL> insert into test1 select * from test1;

已创建137248行。

SQL> insert into test1 select * from test1;

已创建274496行。

[@more@]

SQL> commit;

提交完成。

我们查看下test1这个表占用的空间

SQL> col tablename format a10

SQL> select owner,segment_name tablename , bytes/1024/1024 from dba_segments wh

ere segment_name='TEST1';

OWNER TABLENAME BYTES/1024/1024

------------------------------ ---------- ---------------

ROY TEST1 64

我们可以看到这个表使用了64M的空间

我们删除掉test1中所有的记录

SQL> delete from test1;

已删除548992行。

SQL> commit;

提交完成。

查看删除掉所有记录后test1表占用空间大小

SQL> select owner,segment_name tablename , bytes/1024/1024 from dba_segments wh

ere segment_name='TEST1';

OWNER TABLENAME BYTES/1024/1024

------------------------------ ---------- ---------------

ROY TEST1 64

我们惊奇的发现大小仍然为64M

下面我们截断这个表

SQL> truncate table test1;

表被截断。

SQL> select owner,segment_name tablename , bytes/1024/1024 from dba_segments wh

ere segment_name='TEST1';

OWNER TABLENAME BYTES/1024/1024

------------------------------ ---------- ---------------

ROY TEST1 .0625

这时空间已经只有0.0625M了

测试二

SQL> select count(*) from test2;

COUNT(*)

----------

2195968

我们可以看到test2这个表中有200多万条记录。

我们创建表test3让其拥有与test2同样的记录。

SQL> create table test3 as select * from test2;

表已创建。

下面我们使用delete删除掉test2的所有记录。

SQL> delete from test2;

已删除2195968行。

SQL> commit;

提交完成。

经过漫长的等待，我们删除了test2表中的所有记录。

下面我们使用truncate截断test3

SQL> truncate table test3;

表被截断。

几乎一瞬间完成。

下面我们分别查询两个表。

SQL> set timing on

SQL> SET AUTOTRACE ON STATISTICS

SQL> select count(*) from test2;

COUNT(*)

----------

0

已用时间: 00: 00: 05.17

统计信息

----------------------------------------------------------

0 recursive calls

0 db block gets

64534 consistent gets

32409 physical reads

0 redo size

418 bytes sent via SQL*Net to client

416 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

已用时间: 00: 00: 07.36

SQL> select count(*) from test3;

COUNT(*)

----------

0

已用时间: 00: 00: 00.00

统计信息

----------------------------------------------------------

0 recursive calls

0 db block gets

3 consistent gets

0 physical reads

0 redo size

418 bytes sent via SQL*Net to client

416 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

已用时间: 00: 00: 00.00

查询时间上一幕了然，显示结果均为0条，但时间缺大相径庭。

总结：

测试一中我们可以清楚的看到，delete掉表中所有数据的后，表的大小并没有发生改变，而truncate表后表的大小才减少，测试二中明明两个表均没有数据count(*)=0，查询速度却相差很多。产生以上结果的原因在于oracle内部对表处理机制，高水位线 HWM（high-water mark），随着数据的插入，表的体积增大，HWM会升高，但当你使用delete删除记录时，是无法降低HWM的那么这个表所占用的空间也不会缩小，所以也就出现了我们delete掉所有记录，oracle仍然会占用空间与表拥有最多记录时占用的空间相同的情况，而使用truncate截断表中所有数据，则会将表的HWM重置为0并清除了表在磁盘上的空间，那么在测试二中查询时更证明了这HWM的存在，truncate后的表test3已经置‘0’，test2表则进行了上万次的IO及物理度。

还有一个概念在自动段空间管理ASSM中叫做低HWM。有兴趣的同学可以再看看这类文章。

ITPub博客

关于HWM，delete与truncate

最新文章