oracle compress壓縮小記

1,壓縮的概念
    1,僅用於heap-organized表
    2,尤其適用於資料倉儲,此類情形的insert和update操作很小量
    3,在11G測試,壓縮表可以EXP和IMP
   
2,壓縮的語法
    1,整個表級,可以在物件表或關係表物理屬性之中指定
    2,對於範圍分割槽表,可在每個分割槽描述中指定
    3,對於列表分割槽表,可以在每個列表分割槽描述中指定
    4,對於NESTED 表的儲存表型別,同樣可以在NESTED 表的列儲存子句中指定
3,壓縮表的一些限制:
    1,如果表的列超過255個列,不支援
    2,lob段不支付,經測,是否啟用壓縮,LOB佔用的大小未變
    3,IOT表不支援,OVERFLOW SEGMENT或OVERFLOW SEGMENT的分割槽也不支援;或IOT表的任何一個對映表段
    4,外部表不支援或集表不支援
    5,HASH分割槽或HASH和列表子分割槽不支援.這些分割槽的壓縮模式屬性要繼承於表空間,表,表分割槽的屬性
   
4,壓縮測試
   1,壓縮對於佔用空間大小的測試
  
   --建立測試表
   SQL> create table t_compression(a int);

表已建立。

  SQL> select compression,compress_for from user_tables where table_name='T_COMPRE
SSION';

COMPRESSION      COMPRESS_FOR
---------------- ------------------------
DISABLED

--插入資料
SQL> insert into t_compression select level from dual connect  by level<5e5;

已建立499999行。

SQL> commit;

提交完成。

---查詢非壓縮模式的表佔用的段大小為27MB
SQL> select segment_name,bytes/1024/1024 mb from user_segments where segment_nam
e='T_COMPRESSION';

SEGMENT_NAME
--------------------------------------------------------------------------------

        MB
----------
T_COMPRESSION
        27
       
       
SQL> truncate table t_compression;

表被截斷。

---資料已經被清空截斷,如下查詢發現仍有空間分配給它,這個問題在另一文中再測試
SQL> select segment_name,bytes/1024/1024 mb from user_segments where segment_nam
e='T_COMPRESSION';

SEGMENT_NAME
--------------------------------------------------------------------------------

        MB
----------
T_COMPRESSION
     .0625
       
---開啟表壓縮模式
SQL> alter table t_compression compress;

表已更改。

---查詢表是否壓縮,壓縮的級別
SQL> select compression,compress_for from user_tables where table_name='T_COMPRE
SSION';

COMPRESSION      COMPRESS_FOR
---------------- ------------------------
ENABLED          BASIC

SQL> insert into t_compression select level from dual connect  by level<5e5;

已建立499999行。

SQL> commit;

提交完成。

--啟用壓縮模式,僅佔用原來1/3小的空間
SQL> select segment_name,bytes/1024/1024 mb from user_segments where segment_nam
e='T_COMPRESSION';

SEGMENT_NAME
--------------------------------------------------------------------------------

        MB
----------
T_COMPRESSION
         7

  2,壓縮對於時間的影響
---壓縮模式下
23:29:08 SQL> insert into t_compression select level from dual connect  by level
<5e5;

已建立499999行。

已用時間:  00: 00: 00.47
23:29:13 SQL> commit;

提交完成。

已用時間:  00: 00: 00.01
23:29:19 SQL> truncate table t_compression;

表被截斷。

 

--------非壓縮模式,自知可知壓縮模式仍會更多耗用時間
已用時間:  00: 00: 00.23
23:29:29 SQL> alter table t_compression nocompress;

表已更改。

已用時間:  00: 00: 00.01
23:29:42 SQL> insert into t_compression select level from dual connect  by level
<5e5;

已建立499999行。

已用時間:  00: 00: 00.53
23:29:47 SQL> commit;
     
       
       
   4,壓縮對於DML的影響
      1,redo size的區別
      2,cpu time及db time的區別
      3,執行計劃方面,邏輯讀(一致性讀和當前讀的區別)
     

4.2.1 Table Compression
 
Heap-organized tables can be stored in a compressed format that is transparent for any kind of application. Table compression was designed primarily for read-only environments and can cause processing overhead for DML operations in some cases. However, it increases performance for many read operations, especially when your system is I/O bound.
 
Compressed data in a database block is self-contained which means that all the information needed to re-create the uncompressed data in a block is available within that block. A block will also be kept compressed in the buffer cache. Table compression not only reduces the disk storage but also the memory usage, specifically the buffer cache requirements. Performance improvements are accomplished by reducing the amount of necessary I/O operations for accessing a table and by increasing the probability of buffer cache hits.
 

4.2.1.1 Estimating the Compression factor
 
Table compression works by eliminating column value repetitions within individual blocks. Duplicate values in all the rows and columns in a block are stored once at the beginning of the block, in what is called a symbol table for that block. All occurrences of such values are replaced with a short reference to the symbol table. The compression is higher in blocks that have more repeated values.
 
Before compressing large tables you should estimate the expected compression factor. The compression factor is defined as the number of blocks necessary to store the information in an uncompressed form. divided by the number of blocks necessary for a compressed storage. The compression factor can be estimated by sampling a small number of representative data blocks of the table to be compressed and comparing the average number of records for each block for the uncompressed and compressed case. Experience shows that approximately 1000 data blocks provides a very accurate estimation of the compression factor. Note that the more blocks you are sampling, the more accurate the result become.
 

4.2.1.2 Tuning to Achieve a Better Compression Ratio
 
Oracle achieves a good compression factor in many cases with no special tuning. As a database administrator or application developer, you can try to tune the compression factor by reorganizing the records when the compression actually takes place. Tuning can improve the compression factor slightly in some cases and very substantially in other cases.
 
To improve the compression factor you have to increase the likelihood of value repetitions within a database block. The compression factor that can be achieved depends on the cardinality of a specific column or column pairs (representing the likelihood of column value repetitions) and on the average row length of those columns. Oracle table compression not only compresses duplicate values of a single column but tries to use multi-column value pairs whenever possible. Without a very detailed understanding of the data distribution it is very difficult to predict the most optimal order.