從底向上第五篇--瞭解表的壓縮屬性

從底向上第一篇--瞭解DML操作
從底向上第二篇--瞭解行遷移
從底向上第三篇--瞭解index的compress
從底向上第四篇--瞭解行連結
從底向上第五篇--瞭解表的壓縮屬性


在從底向上第三篇--瞭解index的compress中,我們看到了對於索引而言，使用compress屬性是如何改變索引的儲存方式的。
這裡，對錶的compress及compress的適用情況做一下測試。

SQL> conn test/test
已連線。
SQL> create table t compress as select * from dba_tables where 1=0; --以compress關鍵字建立一個空表

表已建立。

SQL> insert into t select * from dba_tables; -- 插入資料

已建立3003行。

SQL> commit;

提交完成。

按照正常邏輯，我們已經指定了compress關鍵字，那麼，表應該是壓縮了的。我們看一下。

SQL> select segment_name,blocks,header_file,header_block from dba_segments where
segment_name='T';

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

BLOCKS HEADER_FILE HEADER_BLOCK
---------- ----------- ------------
T
96 4 794


SQL> alter system dump datafile 4 block 795;

系統已更改。[@more@]data_block_dump,data header at 0x20566064
===============
tsiz: 0x1f98
hsiz: 0x5a
pbl: 0x20566064
76543210
flag=-------- -- 沒有壓縮塊標誌
ntab=1 -- 塊內只有一個表
nrow=36 -- 這個塊放了36行資料
frre=-1
fsbo=0x5a
fseo=0x6d
avsp=0x13
tosp=0x13
0xe:pti[0] nrow=36 offs=0
0x12:pri[0] offs=0x1df4
…
block_row_dump:

SQL> select count(*) from t;

COUNT(*)
----------
3002

執行計劃
----------------------------------------------------------
Plan hash value: 2966233522

-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T | 2977 | 30 (0)| 00:00:01 |
-------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement


統計資訊
----------------------------------------------------------
0 recursive calls
0 db block gets
98 consistent gets
0 physical reads
0 redo size
420 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

Ok，上面的例子裡，簡單的插入並不能啟動compress，那麼應該怎樣做，才能使表進行壓縮呢？

SQL> conn test/test
已連線。
SQL> truncate table t;

表被截斷。

SQL> insert /*+ append */ into t select * from dba_tables; -- 使用直接路徑插入

已建立3003行。

SQL> commit;

提交完成。

SQL> select header_file,header_block from dba_segments where segment_name='T';

HEADER_FILE HEADER_BLOCK
----------- ------------
4 538

SQL> select segment_name,blocks from dba_segments where segment_name='T';

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

BLOCKS
----------
T
16
對於這個表，壓縮比率是多少呢？16/96 大概16.7%的樣子。

SQL> alter system dump datafile 4 block 539;

系統已更改。

data_block_dump,data header at 0xd2d827c
===============
tsiz: 0x1f80
hsiz: 0x38e
pbl: 0x0d2d827c
76543210
flag=-0------ -- 標誌O表示這個塊是壓縮塊
ntab=2 -- 塊內有兩個表，為什麼是兩個表呢？其實，一個是字典表，一個是資料表（字典的引用）
nrow=384 -- 塊記憶體儲了384行資料
frre=-1
fsbo=0x38e
fseo=0x441
avsp=0xb3
tosp=0xb3
-- 下面是壓縮塊獨有的內容：
r0_9ir2=0x0
mec_kdbh9ir2=0x37
76543210
shcf_kdbh9ir2=----------
76543210
flag_9ir2=--R---OC
fcls_9ir2[30]={ 0 32768 32768 32768 32768 32768 32768 32768 32768 32768 32768 32768 32768 32768 32768 32768 32768 32768 32768 32768 32768 32768 32768 32768 32768 32768 32768 32768 32768 32768 }
perm_9ir2[51]={ 27 50 26 44 0 1 39 42 40 28 43 41 2 3 4 29 30 5 6 48 45 31 32 33 46 34 35 7 8 9 10 49 47 11 12 13 14 15 16 36 37 17 18 19 20 38 21 22 23 24 25 }
0x86:pti[0] nrow=144 offs=0
0x8a:pti[1] nrow=240 offs=144
0x8e:pri[0] offs=0x1b21
…
block_row_dump:
tab 0, row 141, @0x1ea2 -- tab0 字典表
tl: 10 fb: --H-FL-- lb: 0x0 cc: 1
col 0: [ 7] 78 6e 03 15 12 0f 29
bindmp: 00 02 cf 78 6e 03 15 12 0f 29 -- bindmp，原始資料匯出，真正儲存的是這個
…
tab 1, row 0, @0x1a50 --tab1 資料表
tl: 22 fb: --H-FL-- lb: 0x0 cc: 51 --壓縮後行的大小是22
-- 以下col都是恢復（解壓縮）出來的內容
col 0: *NULL*
col 1: [ 5] 56 41 4c 49 44
col 2: [ 2] c1 02
col 3: [ 6] c5 16 30 31 25 2e
col 4: *NULL*
col 5: [ 3] 59 45 53
col 6: [ 1] 4e
col 7: [10] 20 20 20 20 20 20 20 20 20 31
col 8: [10] 20 20 20 20 20 20 20 20 20 31
col 9: [ 5] 20 20 20 20 4e
…
col 50: [10] 54 59 50 45 5f 4d 49 53 43 24
-- 以下是行實際儲存資料
bindmp: 2c 00 06 1d 03 ca c1 2f 44 6b 6b d2 54 59 50 45 5f 4d 49 53 43 24

SQL> select count(*) from t;

COUNT(*)
----------
3002


執行計劃
----------------------------------------------------------
Plan hash value: 2966233522

-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T | 3002 | 6 (0)| 00:00:01 |
-------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement


統計資訊
----------------------------------------------------------
0 recursive calls
0 db block gets
15 consistent gets
0 physical reads
0 redo size
420 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

由於表資料進行了壓縮，資料塊大小由96降為16，全表掃描的Cost也由30降到了6,。

Ok，既然資料已經進行了壓縮，那麼，我們在這個已經壓縮的表上再次正常insert資料，看看會不會壓縮？

SQL> insert into t select * from dba_tables;

已建立3003行。

SQL> commit;

提交完成。

SQL> select segment_name,blocks from dba_segments where segment_name='T';

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

BLOCKS
----------
T
112


SQL> alter system dump datafile 4 block 555;

系統已更改。

data_block_dump,data header at 0x2056c064
===============
tsiz: 0x1f98
hsiz: 0x5a
pbl: 0x2056c064
76543210
flag=-------- -- 不是壓縮塊
ntab=1
nrow=36
frre=-1
fsbo=0x5a
fseo=0xac
avsp=0x52
tosp=0x52
0xe:pti[0] nrow=36 offs=0
0x12:pri[0] offs=0x1b55
…
block_row_dump:

即使表中已存在的內容是壓縮的，後續未採用直接路徑insert的資料依然不能壓縮。

SQL> conn test/test
已連線。
SQL> drop table t;

表已刪除。

SQL> create table t compress as select * from dba_tables;

表已建立。

SQL> select segment_name,blocks from dba_segments where segment_name='T';

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

BLOCKS
----------
T
16

CTAS可以壓縮表資料。

綜上，對於壓縮表而言，使用範圍要比壓縮索引小的多，僅僅適用於CTAS或直接路徑載入插入。

並且，一般而言，壓縮表由於降低了表的Block數量，直接降低了邏輯讀，雖然壓縮、解壓縮過程會使用額外的CPU資源，但往往是利大於弊。個人認為，對於系統中常用的碼錶（不容易修改，並且資料量不會太大）壓縮可以作為降低資源使用的一種方法。

值得注意的是，對於exp/imp而言，因為無法做直接路徑載入，將會導致壓縮表在匯出、匯入後以正常表來進行儲存。

關於更深入的瞭解壓縮塊的格式，老熊有個很好的文章，請移步