总体说来，compress table的原理和compress index原理大致相同，compress table是压缩某表的同一字段下的相同的行，compress index是压缩复合索引中同一字段（一般是第一、二个字段）的相同行。
compress表可以通过user_tables.COMPRESSION查看是否是压缩表：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 |
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时间差距：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
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的表大小：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
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.非压缩表和压缩表之间的转换：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
--改成压缩表 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度：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
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：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 |
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> |
一条评论
不错不错,我已经不做DBA了