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博客

oracle11gR2 table compress一点测试

最新文章