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才能壓縮。

oracle11gR2 table compress一點測試

相關文章