时间戳

首页 > 解决方案 > 删除和截断和表大小

sql - 删除和截断和表大小

问题描述

我尝试了以下查询以了解删除和截断后对表大小的影响。

select * from SALES_HISTORY;
Output:
Product     Month       Sales
sony    22-DEC-17   24000
sony    22-DEC-17   24000
sony    22-DEC-17   24000
sony    22-DEC-17   24000
sony    22-DEC-17   24000
sony    22-DEC-17   24000

Delete from sales_history:
commit;

select segment_name,segment_type,bytes/1024/1024 MB
from dba_segments
where segment_type='TABLE' and segment_name='SALES_HISTORY';

Output:
segment_name    segment_type   MB
SALES_HISTORY   TABLE         0.0625

Truncate table SALES_HISTORY;

之后我再次尝试查找表的大小，但它看起来与下面相同，如果截断所有行，它不应该为零

Output:
segment_name    segment_type   MB
SALES_HISTORY   TABLE         0.0625

我需要了解删除和截断后对表和水印大小的影响。

标签： sqloraclesql-deletetruncate

解决方案

有几个选项truncate，以下是这些选项如何delete影响存储和高水位线：

  • delete留下分配给表的所有空间。高水位线保持不变
  • truncate table ... reuse storage留下分配给表的所有空间。这会将高水位线重置为第一个范围
  • truncate table ... drop storage（默认）释放表上方的所有空间minextents。并因此重置高水位线
  • truncate table ... drop all storage释放表中的所有空间

您可以通过以下方式验证这一点dbms_space.unused_space：

create table t as 
  select level id, lpad ( 'x', 1000, 'x' ) stuff 
  from   dual
  connect by level <= 1000;

create or replace procedure show_size as

   out_total_blocks               integer;
   out_total_bytes                integer;
   out_unused_blocks              integer;
   out_unused_bytes               integer;
   out_last_used_extent_file_id   integer;
   out_last_used_extent_block_id  integer;
   out_last_used_block            integer;

begin

  dbms_space.unused_space(
    segment_owner              =>  user
    ,segment_name              =>  'T'
    ,segment_type              =>  'TABLE'
    ,total_blocks              =>  out_total_blocks
    ,total_bytes               =>  out_total_bytes 
    ,unused_blocks             =>  out_unused_blocks
    ,unused_bytes              =>  out_unused_bytes
    ,last_used_extent_file_id  =>  out_last_used_extent_file_id
    ,last_used_extent_block_id =>  out_last_used_extent_block_id
    ,last_used_block           =>  out_last_used_block
  );

  dbms_output.put_line ( 'Total Blocks:    ' ||  out_total_blocks || ' (blocks in the segment)');
  dbms_output.put_line ( 'Total Bytes:     ' ||  out_total_bytes || ' (segment size in bytes)');
  dbms_output.put_line ( 'Unused Blocks:   ' ||  out_unused_blocks || ' (empty blocks)');
  dbms_output.put_line ( 'Unused Bytes:    ' ||  out_unused_bytes || ' (size of empty space in bytes)');
  dbms_output.put_line ( 'Last Used Block: ' ||  out_last_used_block || ' (high water mark)');
end;
/ 

exec show_size;

Total Blocks:    256 (blocks in the segment)
Total Bytes:     2097152 (segment size in bytes)
Unused Blocks:   101 (empty blocks)
Unused Bytes:    827392 (size of empty space in bytes)
Last Used Block: 27 (high water mark)

delete t;
commit;

exec show_size;

Total Blocks:    256 (blocks in the segment)
Total Bytes:     2097152 (segment size in bytes)
Unused Blocks:   101 (empty blocks)
Unused Bytes:    827392 (size of empty space in bytes)
Last Used Block: 27 (high water mark)

truncate table t
  reuse storage;

exec show_size;

Total Blocks:    256 (blocks in the segment)
Total Bytes:     2097152 (segment size in bytes)
Unused Blocks:   253 (empty blocks)
Unused Bytes:    2072576 (size of empty space in bytes)
Last Used Block: 3 (high water mark)

truncate table t
  drop storage;

exec show_size;

Total Blocks:    8 (blocks in the segment)
Total Bytes:     65536 (segment size in bytes)
Unused Blocks:   5 (empty blocks)
Unused Bytes:    40960 (size of empty space in bytes)
Last Used Block: 3 (high water mark)

truncate table t
  drop all storage;

exec show_size;

Total Blocks:    0 (blocks in the segment)
Total Bytes:     0 (segment size in bytes)
Unused Blocks:   0 (empty blocks)
Unused Bytes:    0 (size of empty space in bytes)
Last Used Block: 0 (high water mark)

推荐阅读