oracle11gR2 table compress一点测试

11gR2中,有两种压缩,一种是普通的压缩compress,另一种是compress for oltp.
普通的compress对后面的insert进来的数据似乎压缩不大
compress for oltp对后面insert进来的数据压缩比例比较明显
SQL> select * from v$version where rownum<2;

BANNER
-------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production


SQL> create table tt as select * from all_objects;

Table created.

Elapsed: 00:00:01.02
SQL> create table tt_cm compress as select * from all_objects;

Table created.

Elapsed: 00:00:00.79
SQL> create table tt_oltp compress for oltp as select * from all_objects;

Table created.

Elapsed: 00:00:00.93
SQL> exec dbms_stats.gather_table_stats('test','tt');

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.80
SQL> exec dbms_stats.gather_table_stats('test','tt_cm');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.76
SQL> exec dbms_stats.gather_table_stats('test','tt_oltp');

PL/SQL procedure successfully completed.


SQL> select table_name,pct_free,num_rows,blocks,empty_blocks,avg_space,avg_row_len,compression,compress_for from user_t
bles;

TABLE_NAME   PCT_FREE   NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE AVG_ROW_LEN COMPRESSION      COMPRESS_F
---------- ---------- ---------- ---------- ------------ ---------- ----------- ---------------- ----------
TT                 10      11197        158            0          0          88 DISABLED
TT_CM               0      11198         52            0          0          88 ENABLED          BASIC
TT_OLTP            10      11199         58            0          0          88 ENABLED          OLTP

SQL> select segment_name,bytes,blocks,extents from user_segments order by segment_name;

SEGMENT_NAME              BYTES     BLOCKS    EXTENTS
-------------------- ---------- ---------- ----------
TT                      2097152        256         17
TT_CM                    458752         56          7
TT_OLTP                  524288         64          8





-------=======================================================
SQL> insert into tt select * from all_objects;

11199 rows created.

Elapsed: 00:00:01.62
SQL> commit;

Commit complete.

Elapsed: 00:00:00.00
SQL> insert into tt_cm select * from all_objects;

11199 rows created.

Elapsed: 00:00:01.58
SQL> insert into tt_oltp select * from all_objects;

11199 rows created.

Elapsed: 00:00:04.75
SQL> commit;

Commit complete.

Elapsed: 00:00:00.00
SQL> exec dbms_stats.gather_table_stats('test','tt_oltp');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.28
SQL> exec dbms_stats.gather_table_stats('test','tt_cm');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.20
SQL> exec dbms_stats.gather_table_stats('test','tt');

PL/SQL procedure successfully completed.

SQL> select table_name,pct_free,num_rows,blocks,empty_blocks,avg_space,avg_row_len,compression,compress_for from user_ta
bles
  2  ;

TABLE_NAME   PCT_FREE   NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE AVG_ROW_LEN COMPRESSION      COMPRESS_F
---------- ---------- ---------- ---------- ------------ ---------- ----------- ---------------- ----------
TT                 10      22396        382            0          0          88 DISABLED
TT_CM               0      22397        250            0          0          88 ENABLED          BASIC
TT_OLTP            10      22398        124            0          0          88 ENABLED          OLTP

Elapsed: 00:00:00.03
SQL> select segment_name,bytes,blocks,extents from user_segments order by segment_name;

SEGMENT_NAME              BYTES     BLOCKS    EXTENTS
-------------------- ---------- ---------- ----------
TT                      3145728        384         18
TT_CM                   2097152        256         17
TT_OLTP                 1048576        128         16



-----============================================================
select dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid),count(*) from tt_OLTP
group by dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid)
order by 2
查询tt_cm table得出,后面insert进来的数据,每个数据块里面的存放数据的记录,都少了。
tt_oltp,后面insert进来的数据,都有一定的压缩。


alter table tt_cm_t compress 之后,需要对tt_cm_t进行
alter table tt_cm_t move才能压缩。

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

转载于:http://blog.itpub.net/24237320/viewspace-741741/

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值