ITPub博客

首页 > Linux操作系统 > Linux操作系统 > [20130831]Compressed partitions are not compressed tables.txt

[20130831]Compressed partitions are not compressed tables.txt

原创 Linux操作系统 作者:lfree 时间:2013-09-02 11:55:06 0 删除 编辑
[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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2600
  • 访问量
    6374035