I had a partitioned table and wanted to compress it in basic mode. For every partition I did alter table move partition compress, computed statistics and the number of blocks after compression is much bigger than before compression. Why is that? Below is the code I run for my test. I would appreciate explanation, I expected smaller size. Is there anything I misinterpret or overlooked? Oracle 11.2.0.4.0
Thank you
create table x partition by hash (col) ( partition x1, partition x2, partition x3, partition x4 ) as
select level col from dual connect by level <= 100000;
begin
dbms_stats.gather_table_stats('oracle', 'x', degree => 4);
end;
select table_name, sum(blocks)
from user_tab_partitions
where table_name in ('X')
group by table_name;
| TABLE_NAME | SUM(BLOCKS) |
| X | 385 |
alter table x move partition x1 compress parallel 4;
alter table x move partition x2 compress parallel 4;
alter table x move partition x3 compress parallel 4;
alter table x move partition x4 compress parallel 4;
begin
dbms_stats.gather_table_stats('oracle', 'x', degree => 4);
end;
select table_name, sum(blocks)
from user_tab_partitions
where table_name in ('X')
group by table_name;
| TABLE_NAME | SUM(BLOCKS) |
| X | 16384 |