我們知道，壓縮表可以使同樣的資料佔用更少的空間，它對空間的使用、效能的提高都有積極的影響。下面簡單探討一下oracle的壓縮表。

--建立測試表
SQL> create table t_compress1(a number,b number,c number) compress pctfree 0;

Table created

SQL> create table t_compress2(a number,b number,c number) compress pctfree 0;

Table created

SQL> create table t_compress3(a number,b number,c number) compress pctfree 0;

Table created

SQL> create table t_compress4(a number,b number,c number) compress pctfree 0;

Table created

SQL> create table t_uncompress(a number,b number,c number) pctfree 0;

Table created

SQL> create table t_compress5(a number,b number,c number) compress pctfree 0;

Table created

--生成測試資料

SQL> insert /*+ append */ into t_uncompress select rownum,mod(rownum,10000),mod(rownum,3) from all_objects,all_objects where rownum<300000;

299999 rows inserted

SQL> commit;

--以下內容是dump表上的一個block得到的trace檔案摘取出來的
flag=-------- 這個表示block沒有被壓縮
ntab=1 --這個表示block有一個表的資料
nrow=483 --這個表示block上有483條記錄

--直接路徑insert
SQL> insert /*+ append */ into t_compress1 select * from t_uncompress order by a;

299999 rows inserted

SQL> commit;

Commit complete

--以下內容是dump表上的一個block得到的trace檔案摘取出來的
flag=-0------這個表示block經過壓縮
ntab=2 --這個表示block上存在2張表（其中一個為符號表，保留block中的重複資料的單個複製）
nrow=538 --這個表示block上有538條記錄

SQL> insert /*+ append */ into t_compress2 select * from t_uncompress order by b;

299999 rows inserted

SQL> commit;

Commit complete

--以下內容是dump表上的一個block得到的trace檔案摘取出來的
flag=-0------表示block經過壓縮
ntab=2 --這個表示block上存在2張表的資料
nrow=705 --這個表示當前block上有705條記錄

SQL> insert /*+ append */ into t_compress3 select * from t_uncompress order by c;

299999 rows inserted

SQL> commit;

Commit complete

--以下內容是dump表上的一個block得到的trace檔案摘取出來的
flag=-0------表示block經過壓縮
ntab=2--這個表示block上存在2張表的資料
nrow=503 --這個表示當前block上有503條記錄

SQL> insert /*+ append */ into t_compress5 select * from t_uncompress;

299999 rows inserted

SQL> commit;

--以下內容是dump表上的一個block得到的trace檔案摘取出來的
flag=-0------表示block經過壓縮
ntab=2 --這個表示block上存在2張表的資料
nrow=538 --這個表示當前block上有538條記錄

--普通方式insert
SQL> insert into t_compress4 select * from t_uncompress order by c;

299999 rows inserted

SQL> commit;

Commit complete

--以下內容是dump表上的一個block得到的trace檔案摘取出來的
flag=--------表示block沒有壓縮
ntab=1 --這個表示block上存在1張表的資料
nrow=475 --這個表示當前block上有475條記錄

SQL> select table_name,round((blocks-EMPTY_BLOCKS)/(select blocks-EMPTY_BLOCKS from user_tables where table_name='T_UNCOMPRESS')*100,2) pct from user_tables where table_name like '%T_COMPRESS%';

TABLE_NAME PCT
------------------------------ ----------
T_COMPRESS1 100
T_COMPRESS2 76.71
T_COMPRESS3 92.01
T_COMPRESS4 100.33
T_COMPRESS5 80.03

可以看出，order by的列（也就是壓縮列）不同會導致壓縮率的不同，其中根據B列壓縮的壓縮率最高。
我們看一下資料分佈情況：

SQL> select column_name,NUM_NULLS,NUM_DISTINCT ,AVG_COL_LEN from user_tab_columns where table_name='T_UNCOMPRESS';

COLUMN_NAME NUM_NULLS NUM_DISTINCT AVG_COL_LEN
-------------------- ---------- ------------ -----------
A 0 299999 4
B 0 10000 3
C 0 3 2

從以上資訊可以得到結論：
1、以選擇性較低、長度較大的列作為壓縮列會得到較高的壓縮率。
如果列是選擇性很高，則根據該列壓縮沒有意義，甚至可能壓縮後佔用空間比不壓縮更高。
如果不指定order by，oracle會自動選擇一個合適的列作為壓縮列，但壓縮率不一定是最高的。
2、普通的DML不會壓縮資料。實際上，只有以下情況才會對資料進行壓縮
o insert /*+ append */
o create table as select
o direct path loads
o moves

下面簡單看看壓縮錶帶來的效能方面的好處：
suk@SUK> select count(1) from t_uncompress;

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
624 consistent gets
0 physical reads
0 redo size
378 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

suk@SUK> select count(1) from t_compress2;

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
490 consistent gets
0 physical reads
0 redo size
378 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

可見，壓縮表比沒有壓縮的表在全表掃描時，邏輯讀明顯減少了。
這是由於對錶進行了壓縮，表段佔用的空間也小了。這些block被讀到buffer時仍然是壓縮的狀態，所以這樣可以減少資料庫讀取的IO，
也可以認為壓縮表相當於擴充了SGA，因為同樣的資料用了更少的記憶體。
當然，壓縮表會會比非壓縮表消耗多一點CPU資源，但是這個消耗是在可以接受的範圍內的。
注意：壓縮表只能用在靜態表或者資料修改非常少的表中，否則會產生很大的負面影響：
1) 頻繁的DML會使表空間使用率暴漲
因為普通的DML不會對資料進行壓縮。insert與delete操作與非壓縮表一樣，但是update操作會使資料從壓縮狀態變更非壓縮狀態。
資料一經解壓，佔用的空間必然會增加。
2) 頻繁的DML會產生很多的行遷移
同上一條原因一樣，update操作會使資料從壓縮狀態變更非壓縮狀態，佔用空間增加，必然會引起行遷移。
3) 頻繁的DML會使CPU使用率增高
因為比普通的DML對了一個"解壓"的過程

oracle壓縮表(一)

相關文章