» 小议compress表 OracleBlog.cn -天堂向左 DBA向右

小议compress表

· 作者小荷||【转载时请务必以超链接形式标明文章原始出处和作者信息】
· 永久链接: http://www.oracleblog.cn/study-note/study-compress-table/

总体说来，compress table的原理和compress index原理大致相同，compress table是压缩某表的同一字段下的相同的行，compress index是压缩复合索引中同一字段（一般是第一、二个字段）的相同行。

compress表可以通过user_tables.COMPRESSION查看是否是压缩表：

SQL> create table t_nocompress (a varchar2(20),b number,c number);

Table created.

SQL>
SQL>
SQL> create table t_compress (a varchar2(20),b number,c number) compress;

Table created.

SQL>
SQL> create table tab_for_insert (a varchar2(20),b number,c number);

Table created.

SQL> insert into tab_for_insert select 'aa'||rownum,rownum,rownum from dba_objects;

88138 rows created.

SQL> insert into tab_for_insert select 'bb'||rownum,rownum,rownum from dba_objects;

88138 rows created.

SQL> insert into tab_for_insert select 'cc'||rownum,rownum,rownum from dba_objects;

88138 rows created.

SQL> /

88138 rows created.

SQL> commit;

Commit complete.

SQL>
SQL> update tab_for_insert set b=mod
2
SQL>
SQL> update tab_for_insert set b=mod(b,10);

352552 rows updated.

SQL> update tab_for_insert set c=mod(c,5);

352552 rows updated.

SQL> commit;

Commit complete.

SQL>
SQL>
SQL>
SQL> insert into TAB_FOR_INSERT select * from TAB_FOR_INSERT;

352552 rows created.

SQL> /

705104 rows created.

SQL> /

1410208 rows created.

SQL> /

2820416 rows created.

SQL> /

5640832 rows created.

SQL> /

11281664 rows created.

SQL> commit;

Commit complete.

SQL> select table_name,COMPRESSION from user_tables;

TABLE_NAME COMPRESS
------------------------------ --------
TAB_FOR_INSERT DISABLED
T_COMPRESS ENABLED
T_NOCOMPRESS DISABLED

SQL>

1.compress表和一般表的体积的比较以及insert时间差距：

SQL> insert /*+ append */ into T_NOCOMPRESS select * from TAB_FOR_INSERT;

22563328 rows created.

Elapsed: 00:00:45.50
SQL>
SQL>
SQL> insert /*+ append */ into T_COMPRESS select * from TAB_FOR_INSERT;

22563328 rows created.

Elapsed: 00:02:54.41

SQL> commit;

Commit complete.

SQL> select segment_name,bytes from user_segments where segment_name in ('T_COMPRESS','T_NOCOMPRESS') ;

SEGMENT_NAME BYTES
------------------------------ ----------
T_NOCOMPRESS 475004928
T_COMPRESS 349175808

2.比较用order和不用order的表大小：

SQL> insert /*+ append */ into T_COMPRESS select * from TAB_FOR_INSERT order by c,a,b;

22563328 rows created.

Elapsed: 00:04:17.49

SQL> select segment_name,bytes from user_segments where segment_name in ('T_COMPRESS');

SEGMENT_NAME BYTES
------------------------------ ----------
T_COMPRESS 258998272

Elapsed: 00:00:06.12

--而没用order by之前的大小是：
SEGMENT_NAME BYTES
------------------------------ ----------
T_COMPRESS 349175808

3.非压缩表和压缩表之间的转换：

--改成压缩表
SQL> alter table T_NOCOMPRESS compress;

Table altered.

Elapsed: 00:00:00.02
--没做move之前的大小
SQL> select segment_name,bytes from user_segments where segment_name in ('T_NOCOMPRESS');

SEGMENT_NAME BYTES
------------------------------ ----------
T_NOCOMPRESS 475004928

Elapsed: 00:00:04.04
SQL>
SQL>
SQL> alter table T_NOCOMPRESS move;

Table altered.

Elapsed: 00:02:41.51
SQL>

--move之后的大小
SQL>
SQL> select segment_name,bytes from user_segments where segment_name in ('T_NOCOMPRESS');

SEGMENT_NAME BYTES
------------------------------ ----------
T_NOCOMPRESS 349175808

Elapsed: 00:00:04.25

4.也可以像压缩复合索引一样，指定compress度：

SQL> select segment_name,bytes from user_segments where segment_name in ('T_NOCOMPRESS');

SEGMENT_NAME BYTES
------------------------------ ----------
T_NOCOMPRESS 427819008

Elapsed: 00:00:06.53
SQL>
SQL>
SQL>
SQL> alter table T_NOCOMPRESS compress 2;

Table altered.

Elapsed: 00:00:00.03
SQL>
SQL> alter table T_NOCOMPRESS move;

Table altered.

Elapsed: 00:02:39.96
SQL> select segment_name,bytes from user_segments where segment_name in ('T_NOCOMPRESS');

SEGMENT_NAME BYTES
------------------------------ ----------
T_NOCOMPRESS 349175808

5.数据库9206中有bug，不能add/drop columns：

SQL> alter table T_COMPRESS add d number;
alter table T_COMPRESS add d number
   *
ERROR at line 1:
ORA-22856: cannot add columns to object tables

Elapsed: 00:00:00.03
SQL>
--该bug见Doc ID: Note:217292.1 ，或bug 2421054

可以用以下方法绕过：
1.alter table nocompress
2.move
注意上述2个步骤，执行完第一步后，继续执行第二步才能完成；如果在执行第一步后，尝试add column，仍报错，再move之后，将同样也不能add column：
SQL> alter table T_COMPRESS nocompress;

Table altered.

Elapsed: 00:00:00.02

SQL>
SQL> alter table T_COMPRESS add z number;
alter table T_COMPRESS add z number
   *
ERROR at line 1:
ORA-22856: cannot add columns to object tables

Elapsed: 00:00:00.01
SQL>
SQL>
SQL> alter table T_COMPRESS move;

Table altered.

Elapsed: 00:00:39.91
SQL>
SQL>
SQL> alter table T_COMPRESS add z number;
alter table T_COMPRESS add z number
   *
ERROR at line 1:
ORA-22856: cannot add columns to object tables

--必须采用执行完以下2步后再add：
SQL> alter table T_COMPRESS nocompress;

Table altered.

Elapsed: 00:00:00.03
SQL> alter table T_COMPRESS move;

Table altered.

Elapsed: 00:00:39.52
SQL> alter table T_COMPRESS add z number;

Table altered.

Elapsed: 00:00:00.03
SQL>

· 【文章发布信息】发表于： 2008-08-30 @ 01:30:48 · ||分类： Study note

1 条评论 »

ld 于 2008-10-07 @ 09:25:14 留言：

不错不错,我已经不做DBA了

RSS 为此帖反馈评论 · 反向跟踪网站

OracleBlog.cn -天堂向左 DBA向右