意义:帮助处理磁盘容量问题,在oracle 9i中引入了表压缩的特性,它可以极大的节省磁盘空间和减少对buffercache内存的使用.并在某些情况下能提高查询性能.
原理：表的压缩是通过删除数据库表中重复数据来实现节省空间
压缩是在数据块级别上进行的
压缩在数据块级生效，当数据表定义为压缩时，数据库在每个数据块上保留空间存储重复数据的单个拷贝，保留空间被称为符号表（symboltable）。此后在具体行上不必再存储这些重复数据，只需要存放指向符号表相应数据的指针，存储空间因此得以节省。
测试：
SQL> conn hxy/hxy
Connected.
创建一个普通表并插入数据：
SQL> create tabletest (
2 avarchar(20),
3 bvarchar(20));
Table created.
SQL> begin
2 fori in 1 .. 10000 loop
3 insert into test values('hxy','test');
4 endloop;
5 end;
6 /
PL/SQL proceduresuccessfully completed.
创建压缩表
SQL> create tabletest_com compress as select * from test;
Table created.
SQL> selecttable_name,COMPRESSION from user_tables where table_name like 'TEST%';
TABLE_NAME COMPRESS
--------------------------------------
TEST DISABLED
TEST_COM ENABLED
SQL> analyze table test compute statistics;
Table analyzed.
SQL> analyzetable test_com compute statistics;
Table analyzed.
看一下两个表空间的使用情况
SQL> selecttable_name,blocks,EMPTY_BLOCKS from user_tables where table_name like 'TEST%';
TABLE_NAME BLOCKS EMPTY_BLOCKS
---------------------------------------- ------------
TEST 20 4
TEST_COM 18 6
SQL> select(20-4)/(18-6) from dual;
(20-4)/(18-6)
-------------
1.33333333
blocks为这个表总共使用的数据库数，empty_blocks为在表中未使用的数据块。相减则为使用过的数据库数
接下来转储一下数据块
SQL> selectsegment_name,file_id,block_id,blocks from dba_extents wheresegment_name='TEST_COM';
SEGMENT_NAME FILE_ID BLOCK_ID BLOCKS
------------------------------ ---------- ----------
TEST_COM 10 33 8
TEST_COM 10 41 8
TEST_COM 10 49 8
SQL> alter systemdump datafile 10 block 40;
System altered.
获取trace文件
SQL> SELECT d.VALUE
2 || '/'
3 || LOWER (RTRIM (i.INSTANCE, CHR (0)))
4 || '_ora_'
5 || p.spid
6 || '.trc' trace_file_name
7 FROM (SELECT p.spid
8 FROM v$mystat m, v$session s, v$process p
WHERE m.statistic# = 1 AND s.SID =m.SID AND p.addr = s.paddr) p,
9 10 (SELECT t.INSTANCE
11 FROM v$thread t, v$parameter v
12 WHERE v.NAME = 'thread'
13 AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE))) i,
14 (SELECT VALUE
15 FROM v$parameter
16 WHERE NAME = 'user_dump_dest') d
17 /
TRACE_FILE_NAME
--------------------------------------------------------------------------------
/oracle10/admin/db10/udump/db10_ora_31146.trc
查看块儿头信息：
data_block_dump,dataheader at 0x1105f207c
===============
tsiz: 0x1f80
hsiz: 0x5d6
pbl: 0x1105f207c
bdba: 0x02800028
76543210
flag=-0------
ntab=2 -----说明有两张表
nrow=729
frre=-1
fsbo=0x5d6
fseo=0x113c
avsp=0x6
tosp=0x6
r0_9ir2=0x0
mec_kdbh9ir2=0x1
76543210
shcf_kdbh9ir2=----------
76543210
flag_9ir2=--R----C
fcls_9ir2[3]={ 0 32768 32768 }
0x1c:pti[0] nrow=1 offs=0
0x20:pti[1] nrow=728 offs=1
0x24:pri[0] offs=0x1f74
0x26:pri[1] offs=0x1f6f
0x28:pri[2] offs=0x1f6a
0x2a:pri[3] offs=0x1f65
0x2c:pri[4] offs=0x1f60
block_row_dump:
tab 0,row 0, @0x1f74
tl: 12fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 3] 68 78 79 tab 0只有一行记录，就是我们说的符号表。
col 1: [ 4] 74 65 73 74
bindmp: 02 d8 02 cb 68 78 79 cc 74 65 73 74
下面的数据才是真实的记录：
tab 1, row 0,@0x1f6f
tl: 5 fb: --H-FL--lb: 0x0 cc: 2
col 0: [ 3] 68 78 79
col 1: [ 4] 74 65 73 74
bindmp: 2c 00 01 02 00
tab 1, row 1,@0x1f6a
tl: 5 fb: --H-FL--lb: 0x0 cc: 2
col 0: [ 3] 68 78 79
col 1: [ 4] 74 65 73 74
bindmp: 2c 00 01 02 00 ----这里的bindmp就是指针。
tab 1, row 2,@0x1f65
tl: 5 fb: --H-FL--lb: 0x0 cc: 2
col 0: [ 3] 68 78 79
col 1: [ 4] 74 65 73 74
bindmp: 2c 00 01 02 00 因为所有的记录是重复的，所以指向的位置的相同的
tab 1, row 3,@0x1f60
tl: 5 fb: --H-FL--lb: 0x0 cc: 2
col 0: [ 3] 68 78 79
压缩表虽然节省了空间，但是存储的缩减必然导致查询和存储压缩和解压缩对CPU的消耗。但是I/O得以节省
下面对上面的两个表进行全表扫描：
SQL> setautotrace on
SQL> selectcount(*) from test;
COUNT(*)
----------
10000
Execution Plan
----------------------------------------------------------
Plan hash value:3467505462
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| TEST | 10000 | 6 (0)| 00:00:01 |
-------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
23 consistent gets
0 physical reads
0 redo size
515 bytes sent via SQL*Net to client
469 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> selectcount(*) from test_com;
COUNT(*)
----------
10000
Execution Plan
----------------------------------------------------------
Plan hash value:2835758134
-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| TEST_COM | 10000 | 6 (0)| 00:00:01 |
-----------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
17 consistent gets
0 physical reads
0 redo size
515 bytes sent via SQL*Net to client
469 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
压缩表的一致性读降低。
压缩表是为数据仓库设计的特性，所以并不适合OLTP系统，在发生更新时，压缩表会因行链接而迅速扩展空间使用。
请看简单测试:
SQL> updatetest_com set b='oracle' where rownum<10;
9 rows updated.
SQL> commit;
Commit complete.
SQL> selecttable_name,blocks,EMPTY_BLOCKS from user_tables where table_name like 'TEST%';
TABLE_NAME BLOCKS EMPTY_BLOCKS
---------------------------------------- ------------
TEST 20 4
TEST_COM 18 6
SQL> analyzetable test_com compute statistics;
Table analyzed.
SQL> selecttable_name,blocks,EMPTY_BLOCKS from user_tables where table_name like 'TEST%';
TABLE_NAME BLOCKS EMPTY_BLOCKS
---------------------------------------- ------------
TEST 20 4
TEST_COM 24 0
419
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
