oracle table compress,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才能压缩。