Oracle中的表压缩功能是否适合你现在的数据库呢,表压缩能节省一定的空间,特别是表中的重复值越多,压缩率就越高,表压缩的好处不仅仅是存储空间的节省,另一个重要的影响Oracle直接读取压缩数据块的能力,因为不需要读uncompress的block,在重复值越多的情况下,读compress会降低io,从而提高性能,并且buffer cache因为存储更多的数据更高效,我们先来看一下官文的一个说明:
Oracle’s OLTP Table Compression uses a unique compression algorithm specifically designed towork with OLTP applications. The algorithm works by eliminating duplicatevalues within a database block, even across multiple columns. Compressed blockscontain a structure called a symbol table that maintains compression metadata.When a block is compressed, duplicate values are eliminated by first adding asingle copy of the duplicate value to the symbol table. Each duplicate value isthen replaced by a short reference to the appropriate entry in the symboltable.
Oracle 表压缩使用一个唯一的压缩算法。 该算法用来消除一个database block中的重复值,该重复值甚至可以跨多个列。 被压缩的blocks包含一个叫作symbol table的structure,该structure 用来维护压缩的元素。 当一个block 被压缩时,字段值第一次该被copy到symbol table中,然后每次的重复值都是被一个short reference 代替,该reference 指向symbol table 中对应的entry。
可以清楚的看到compressed的block 比not compressed 的block 多了一个symbol Table。 正式因为该structure的使用,才使数据占用的空间降低很多。
正如上面所说的,table compress对读取没有什么不利的影响,但是对表进行dml操作时,需要做一些附加操作,所以对compressblock不适合进行更新,写操作。
我们可以对表空间,表,索引等设置压缩,也可以在定义对象的时候进行指定是否压缩,也可以使用alter命令压缩,这里先不讨论这些基础的方案,本篇文章我们来看一下table compress是否适合你的数据库,做如下对比实验:
1. 创建test_uncompress表,此表没有压缩
PgSQL
SQL> create table test_uncompress as select * from dba_objects;
Table created.
1
2
3
SQL>createtabletest_uncompressasselect*fromdba_objects;
Tablecreated.
2. 收集test_compress表的统计信息
PgSQL
SQL> BEGIN
2 dbms_stats.gather_table_stats
3 (
4 ownname => 'SCOTT',
5 tabname => 'TEST_UNCOMPRESS',
6 estimate_percent => NULL
7 );
8 END;
9 /
PL/SQL procedure successfully completed.
1
2
3
4
5
6
7
8
9
10
11
SQL>BEGIN
2dbms_stats.gather_table_stats
3(
4ownname=>'SCOTT',
5tabname=>'TEST_UNCOMPRESS',
6estimate_percent=>NULL
7);
8END;
9/
PL/SQLproceduresuccessfullycompleted.
3. 查看此时表的行数,块数量
PgSQL
SQL> SELECT num_rows,blocks FROM dba_tables WHERE owner = 'SCOTT' AND table_name = 'TEST_UNCOMPRESS';
NUM_ROWS BLOCKS
---------- ----------
91561 1570
1
2
3
4
5
SQL>SELECTnum_rows,blocksFROMdba_tablesWHEREowner='SCOTT'ANDtable_name='TEST_UNCOMPRESS';
NUM_ROWSBLOCKS
---------- ----------
915611570
4. 现在更新此表的所有行,同时记录下产生的redo数量
PgSQL
SQL> SELECT m.value
2 FROM v$mystat m, v$statname s
3 WHERE m.statistic# = s.statistic#
4 AND s.name = 'redo size';
VALUE
----------
40080684
SQL> update test_uncompress set object_id = object_id+100;
91561 rows updated.
SQL> SELECT m.value
2 FROM v$mystat m, v$statname s
3 WHERE m.statistic# = s.statistic#
4 AND s.name = 'redo size';
VALUE
----------
49220628
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
SQL>SELECTm.value
2FROMv$mystatm,v$statnames
3WHEREm.statistic#=s.statistic#
4ANDs.name='redo size';
VALUE
----------
40080684
SQL>updatetest_uncompresssetobject_id=object_id+100;
91561rowsupdated.
SQL>SELECTm.value
2FROMv$mystatm,v$statnames
3WHEREm.statistic#=s.statistic#
4ANDs.name='redo size';
VALUE
----------
49220628
5. 再次收集表的统计信息,然后查看表数据块的情况
PgSQL
SQL> BEGIN
2 dbms_stats.gather_table_stats
3 (
4 ownname => 'SCOTT',
5 tabname => 'TEST_UNCOMPRESS',
6 estimate_percent => NULL
7 );
8 END;
9 /
PL/SQL procedure successfully completed.
SQL> SELECT num_rows,blocks FROM dba_tables
2 WHERE owner = 'SCOTT'
3 AND table_name = 'TEST_UNCOMPRESS';
NUM_ROWS BLOCKS
---------- ----------
91561 1570
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SQL>BEGIN
2dbms_stats.gather_table_stats
3(
4ownname=>'SCOTT',
5tabname=>'TEST_UNCOMPRESS',
6estimate_percent=>NULL
7);
8END;
9/
PL/SQLproceduresuccessfullycompleted.
SQL>SELECTnum_rows,blocksFROMdba_tables
2WHEREowner='SCOTT'
3ANDtable_name='TEST_UNCOMPRESS';
NUM_ROWSBLOCKS
---------- ----------
915611570
可以看到在没有压缩表的情况下,表数据块在更新前和更新后都是1570个数据块,没有改变,下面来看一下压缩表的DML情况:
1. 创建test_compress压缩表
PgSQL
SQL> create table test_compress compress as select * from dba_objects;
Table created.
1
2
3
SQL>createtabletest_compresscompressasselect*fromdba_objects;
Tablecreated.
2.收集压缩表的统计信息,查看压缩表中的数据块情况
PgSQL
SQL> BEGIN
2 dbms_stats.gather_table_stats
3 (
4 ownname => 'SCOTT',
5 tabname => 'TEST_COMPRESS',
6 estimate_percent => NULL
7 );
8 END;
9 /
PL/SQL procedure successfully completed.
SQL> SQL> SELECT num_rows,blocks FROM dba_tables
2 WHERE owner = 'SCOTT'
3 AND table_name = 'TEST_COMPRESS';
NUM_ROWS BLOCKS
---------- ----------
91565 371
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
SQL>BEGIN
2dbms_stats.gather_table_stats
3(
4ownname=>'SCOTT',
5tabname=>'TEST_COMPRESS',
6estimate_percent=>NULL
7);
8END;
9/
PL/SQLproceduresuccessfullycompleted.
SQL>SQL>SELECTnum_rows,blocksFROMdba_tables
2WHEREowner='SCOTT'
3ANDtable_name='TEST_COMPRESS';
NUM_ROWSBLOCKS
---------- ----------
91565371
3. 更新压缩表中的所有行,并记录下产生的redo
PgSQL
SQL> SELECT m.value
2 FROM v$mystat m, v$statname s
3 WHERE m.statistic# = s.statistic#
4 AND s.name = 'redo size';
VALUE
----------
65770216
SQL> update test_compress set object_id = object_id+100;
91565 rows updated.
SQL> SELECT m.value
2 FROM v$mystat m, v$statname s
3 WHERE m.statistic# = s.statistic#
4 AND s.name = 'redo size';
VALUE
----------
91411464
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
SQL>SELECTm.value
2FROMv$mystatm,v$statnames
3WHEREm.statistic#=s.statistic#
4ANDs.name='redo size';
VALUE
----------
65770216
SQL>updatetest_compresssetobject_id=object_id+100;
91565rowsupdated.
SQL>SELECTm.value
2FROMv$mystatm,v$statnames
3WHEREm.statistic#=s.statistic#
4ANDs.name='redo size';
VALUE
----------
91411464
4. 重新收集压缩表的统计信息,查看数据块改变情况
PgSQL
SQL> BEGIN
2 dbms_stats.gather_table_stats
3 (
4 ownname => 'SCOTT',
5 tabname => 'TEST_COMPRESS',
6 estimate_percent => NULL
7 );
8 END;
9 /
PL/SQL procedure successfully completed.
SQL> SELECT num_rows,blocks FROM dba_tables
2 WHERE owner = 'SCOTT'
3 AND table_name = 'TEST_COMPRESS';
NUM_ROWS BLOCKS
---------- ----------
91565 384
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SQL>BEGIN
2dbms_stats.gather_table_stats
3(
4ownname=>'SCOTT',
5tabname=>'TEST_COMPRESS',
6estimate_percent=>NULL
7);
8END;
9/
PL/SQLproceduresuccessfullycompleted.
SQL>SELECTnum_rows,blocksFROMdba_tables
2WHEREowner='SCOTT'
3ANDtable_name='TEST_COMPRESS';
NUM_ROWSBLOCKS
---------- ----------
91565384
可以看到压缩表的数据块从开始的371,改变到384,从而证明了在更新,插入压缩表记录的时候,数据块会相应的增加,这也就是为什么压缩表不适合一般的OLTP系统,因为OLTP系统的数据是时常更新的,虽然查询速度可能会增加,但是同时也伴随着更多的空间等消耗。下面我们来看看压缩表和非压缩表的redo变化情况。
操作
非压缩表redo size
压缩表redo size
before update
40080684
65770216
after update
49220628
91411464
redo 变化情况
9139944
25641248
可以看到使用压缩表update话费了25641248 bytes,而非压缩表才使用了9139944 bytes,所以在你的数据库中,要慎重选择是否压缩表适合你的系统,应该是在表空间还是在表上使用压缩功能比较合适,一定要慎重选择。
转载请注明: 版权所有,文章允许转载,但必须以链接方式注明源地址,否则追究法律责任!
最后编辑:2014-06-05作者:Jerry
一个积极向上的小青年,热衷于分享--Focus on DB,BI,ETL