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才能压缩。