从底向上第五篇--了解表的压缩属性

从底向上第一篇--了解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而言，因为无法做直接路径加载，将会导致压缩表在导出、导入后以正常表来进行存储。

关于更深入的了解压缩块的格式，老熊有个很好的文章，请移步 http://www.laoxiong.net/dissect_compressed_block_part1.html

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/19423/viewspace-1032602/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/19423/viewspace-1032602/