[20130831]Compressed partitions are not compressed tables.txt

http://connormcdonald.wordpress.com/2013/08/26/compressed-partitions-are-not-compressed-tables/

重复测试：

SCOTT@test01p> @ver

BANNER                                                                               CON_ID

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

Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0

create table T ( x date, y int, z varchar2(50) )
PARTITION BY RANGE (x) INTERVAL (INTERVAL '60' DAY)
  (PARTITION p1 VALUES LESS THAN (TO_DATE('01/01/2012','dd/mm/yyyy')  )
 )
/

insert /*+ APPEND */ into T
select sysdate - 1000+rownum/100, trunc(rownum/1000), rpad('x',50)
from dual
connect by level <= 100000;

commit;

exec dbms_stats.gather_table_stats('','T',estimate_percent=>null);

select partition_name,  compression, num_rows / nullif(blocks,0) rows_per_blk
from dba_tab_partitions
where table_name = 'T'
order by partition_position;

PARTITION_NAME                 COMPRESS ROWS_PER_BLK

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

P1                             DISABLED   101.841558

SYS_P421                       DISABLED           80

SYS_P422                       DISABLED           80

SYS_P423                       DISABLED           80

SYS_P424                       DISABLED           80

SYS_P425                       DISABLED           80

SYS_P426                       DISABLED           80

SYS_P427                       DISABLED           80

SYS_P428                       DISABLED           80

SYS_P429                       DISABLED           80

SYS_P430                       DISABLED           80

SYS_P431                       DISABLED   30.4230769

12 rows selected.

begin
for i in (
select partition_name,  compression
from dba_tab_partitions
where table_name = 'T' )
loop
  execute immediate 'alter table t modify partition '||i.partition_name||' compress';
  execute immediate 'alter table t move partition '||i.partition_name;
end loop;
end;
/


exec dbms_stats.gather_table_stats('','T',estimate_percent=>null)

select partition_name,  compression, num_rows / nullif(blocks,0) rows_per_blk
from dba_tab_partitions
where table_name = 'T'
order by partition_position;

PARTITION_NAME                 COMPRESS ROWS_PER_BLK

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

P1                             ENABLED    445.556818

SYS_P421                       ENABLED    206.896552

SYS_P422                       ENABLED    206.896552

SYS_P423                       ENABLED    206.896552

SYS_P424                       ENABLED    206.896552

SYS_P425                       ENABLED    206.896552

SYS_P426                       ENABLED    206.896552

SYS_P427                       ENABLED    206.896552

SYS_P428                       ENABLED    206.896552

SYS_P429                       ENABLED    206.896552

SYS_P430                       ENABLED    206.896552

SYS_P431                       ENABLED         39.55

12 rows selected.

--可以发现选择压缩后，每块占用的记录数明显增加。

col x new_value p1

select partition_name from user_tab_partitions where partition_position = 4;

PARTITION_NAME

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

SYS_P423

col x new_value p2

select partition_name from user_tab_partitions where partition_position = 5;

PARTITION_NAME

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

SYS_P424

alter table T merge partitions &&p1, &&p2 into partition NEW_PAR;

exec dbms_stats.gather_table_stats('','T',estimate_percent=>null);

select partition_name,  compression, num_rows / nullif(blocks,0) rows_per_blk
from dba_tab_partitions
where table_name = 'T';

PARTITION_NAME                 COMPRESS ROWS_PER_BLK

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

NEW_PAR                        DISABLED   91.6030534

SYS_P425                       ENABLED    206.896552

SYS_P428                       ENABLED    206.896552

P1                             ENABLED    445.556818

SYS_P427                       ENABLED    206.896552

SYS_P422                       ENABLED    206.896552

SYS_P429                       ENABLED    206.896552

SYS_P430                       ENABLED    206.896552

SYS_P421                       ENABLED    206.896552

SYS_P426                       ENABLED    206.896552

SYS_P431                       ENABLED         39.55

11 rows selected.

--可以发现merger后，分区变成非压缩格式。

--再次重复实验。建表是加入了compress参数

drop table T purge;

create table T ( x date, y int, z varchar2(50) )
PARTITION BY RANGE (x) INTERVAL (INTERVAL '60' DAY)
  (PARTITION p1 VALUES LESS THAN (TO_DATE('01/01/2012','dd/mm/yyyy')  )
 )
  compress
/

insert /*+ APPEND */ into T
select sysdate - 1000+rownum/100, trunc(rownum/1000), rpad('x',50)
from dual
connect by level <= 100000;

commit;

exec dbms_stats.gather_table_stats('','T',estimate_percent=>null);

select partition_name,  compression, num_rows / nullif(blocks,0) rows_per_blk
from dba_tab_partitions
where table_name = 'T'
order by partition_position;

PARTITION_NAME                 COMPRESS ROWS_PER_BLK

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

P1                             ENABLED    445.556818

SYS_P441                       ENABLED    206.896552

SYS_P442                       ENABLED    206.896552

SYS_P443                       ENABLED    206.896552

SYS_P444                       ENABLED    206.896552

SYS_P445                       ENABLED    206.896552

SYS_P446                       ENABLED    206.896552

SYS_P447                       ENABLED    206.896552

SYS_P448                       ENABLED    206.896552

SYS_P449                       ENABLED    206.896552

SYS_P450                       ENABLED    206.896552

SYS_P451                       ENABLED         39.55

12 rows selected.

col x new_value p1

select partition_name from user_tab_partitions where partition_position = 4;

PARTITION_NAME

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

SYS_P443

col x new_value p2

select partition_name from user_tab_partitions where partition_position = 5;

PARTITION_NAME

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

SYS_P444

SCOTT@test01p> alter table T merge partitions SYS_P443,SYS_P444 into partition NEW_PAR;
Table altered.

select partition_name,  compression, num_rows / nullif(blocks,0) rows_per_blk
from dba_tab_partitions
where table_name = 'T'
order by partition_position;

PARTITION_NAME                 COMPRESS ROWS_PER_BLK

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

SYS_P442                       ENABLED    206.896552

P1                             ENABLED    445.556818

SYS_P448                       ENABLED    206.896552

SYS_P446                       ENABLED    206.896552

SYS_P450                       ENABLED    206.896552

NEW_PAR                        ENABLED           300

SYS_P445                       ENABLED    206.896552

SYS_P441                       ENABLED    206.896552

SYS_P447                       ENABLED    206.896552

SYS_P451                       ENABLED         39.55

SYS_P449                       ENABLED    206.896552

11 rows selected.

--当然可以在merger时指定compress参数。

SQL> alter table T merge partitions &&p1, &&p2 into partition NEW_PAR compress;
Table altered.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-772031/，如需转载，请注明出处，否则将追究法律责任。