Oracle 收缩分区表

askTom 2017-12-12

640

问题描述

嗨，

收缩表不会对分区表进行碎片分解。

已执行: alter table test_table_1收缩空间

并检查下面查询之前和之后浪费的空间，但值相同。

select 
   table_name,round((blocks*8),2) "size (kb)" , 
   round((num_rows*avg_row_len/1024),2) "actual_data (kb)",
   (round((blocks*8),2) - round((num_rows*avg_row_len/1024),2)) "WASTED SPACE (kb)"
from 
   dba_tables
where 
   (round((blocks*8),2) > round((num_rows*avg_row_len/1024),2))
order by 4 desc;

请让我知道如何解决这个问题？

谢谢，
Sesub

专家解答

所以有两个问题:

-缩小后，您是否在表上重新收集了统计数据？
-您确定可以浪费空间吗？

因为对我来说一切都很好:

create table t ( 
  x not null, y not null
) partition by range (x) (
  partition p0 values less than (500),
  partition p1 values less than (1000)
) enable row movement as 
  select rownum, lpad('x', 1000, 'x') from dual
  connect by level < 1000;
  
exec dbms_stats.gather_table_stats(user, 't');

select table_name,
      round( ( blocks * 8 ),2 ) "size (kb)",
      round( ( num_rows * avg_row_len / 1024 ),2 ) "actual_data (kb)",
      ( round( ( blocks * 8 ),2 ) - round( ( num_rows * avg_row_len / 1024 ),2 ) ) "WASTED SPACE (kb)"
from user_tables
where table_name = 'T'
order by 4 desc;

TABLE_NAME   size (kb)   actual_data (kb)   WASTED SPACE (kb)   
T                   1264             980.46              283.54 

delete t where mod(x, 2) = 0;
commit;

exec dbms_stats.gather_table_stats(user, 't');

select table_name,
      round( ( blocks * 8 ),2 ) "size (kb)",
      round( ( num_rows * avg_row_len / 1024 ),2 ) "actual_data (kb)",
      ( round( ( blocks * 8 ),2 ) - round( ( num_rows * avg_row_len / 1024 ),2 ) ) "WASTED SPACE (kb)"
from user_tables
where table_name = 'T'
order by 4 desc;

TABLE_NAME   size (kb)   actual_data (kb)   WASTED SPACE (kb)   
T                   1264             490.72              773.28 

alter table t shrink space;

select table_name,
      round( ( blocks * 8 ),2 ) "size (kb)",
      round( ( num_rows * avg_row_len / 1024 ),2 ) "actual_data (kb)",
      ( round( ( blocks * 8 ),2 ) - round( ( num_rows * avg_row_len / 1024 ),2 ) ) "WASTED SPACE (kb)"
from user_tables
where table_name = 'T'
order by 4 desc;

TABLE_NAME   size (kb)   actual_data (kb)   WASTED SPACE (kb)   
T                   1264             490.72              773.28 

exec dbms_stats.gather_table_stats(user, 't');

select table_name,
      round( ( blocks * 8 ),2 ) "size (kb)",
      round( ( num_rows * avg_row_len / 1024 ),2 ) "actual_data (kb)",
      ( round( ( blocks * 8 ),2 ) - round( ( num_rows * avg_row_len / 1024 ),2 ) ) "WASTED SPACE (kb)"
from user_tables
where table_name = 'T'
order by 4 desc;

TABLE_NAME   size (kb)   actual_data (kb)   WASTED SPACE (kb)   
T                    592             490.72              101.28

请注意，浪费的空间只有在重新统计后才会下降。

你到底在做什么？向我们展示您的完整测试用例!(创建表插入收缩)

oracle asktom

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

Oracle 收缩分区表

问题描述

专家解答

评论