ORACLE的数据段压缩技术

ORACLE的数据段压缩技术

1.     引言

作为DBA，空间的紧张是常遇到的一个问题，有时一个大的表就会占用几十GB及至上百GB的空间，存储历史数据的表常常都是这样，特别是数据仓库型应用（如经营分析应用）的数据库这样的问题就更加突出。如今，在计算机技术中数据压缩技术已经十分广泛和成熟了。在windows系统中有winrar、winzip,7-zip等等这样的软件，UNIX操作系统中相应的也有compress、gzip这样的压缩命令及软件。这些压缩工具的使用使我们节约了很多的空间，在ORACLE9i之前的版本我有时在想，ORACLE什么时候也有类似的功能可以节约空间，提高单位空间的使用价值。期待之余，ORACLE从9i开始羞羞答答的引入表段级压缩；10g在部分压缩功能略有增强，例如DataPump源数据的压缩、Rman备份的压缩等，并无本质的改观；Oracle11g才大张旗鼓的推出相应Compression组件，支持全方面的压缩。本文将立足于ORACLE9/10g这样的实际应用最多的版本探讨一下ORACLE的压缩技术，并简单介绍一下ORACLE11g的压缩方面的新特征。

2.     数据段压缩实现原理

2.1    原理简单描述

ORACLE的数据段压缩技术可以理解为“数据块”级压缩技术，也就是说是针对Block级别的数据压缩。它是在Block中引入记号表（symbol表），Block中的重复数据在symbol中用一个项（指针）表示，即块中相同的ROW只存储一条，从而节约了空间。

2.2    压缩数据块的存储结构

2.2.1       建一张包含28672行数据的表T_28672

SQL> show user

USER is "SCOTT"

SQL>

SQL> select count(*) from t_28672;

COUNT(*)

----------

28672

2.2.2       T_28672压缩前与压缩后段统计

压缩前：

SQL> select owner,segment_name,file_id,block_id,blocks 

  2  from dba_extents

  3  where wner='SCOTT'          

  4   and segment_name='T_28672'

  5  order by block_id;

OWNER SEGMENT_NAME           FILE_ID  BLOCK_ID    BLOCKS

----- -------------------- ---------- ---------- ----------

SCOTT T_28672                      4      3729         8

SCOTT T_28672                      4      3737         8

SCOTT T_28672                      4      3745         8

SCOTT T_28672                      4      3753         8

SCOTT T_28672                      4      3761         8

SCOTT T_28672                      4      3769         8

SCOTT T_28672                      4      3777         8

SCOTT T_28672                      4      3785         8

SCOTT T_28672                      4      3793         8

SCOTT T_28672                      4      3801         8

SCOTT T_28672                      4      3809         8

SCOTT T_28672                      4      3817         8

SCOTT T_28672                      4      3825         8

SCOTT T_28672                      4      3833         8

SCOTT T_28672                      4      3841         8

SCOTT T_28672                      4      3849         8

SCOTT T_28672                      4      3977       128

17 rows selected.

执行表压缩：

SQL> alter table T_28672 move compress;

Table altered.

压缩后：

SQL> select owner,segment_name,file_id,block_id,blocks

 2   from dba_extents

 3   where wner='SCOTT'

 4   and segment_name='T_28672'

 5   order by block_id;

OWNER SEGMENT_NAME           FILE_ID  BLOCK_ID    BLOCKS

----- -------------------- ---------- ---------- ----------

SCOTT T_28672                      4      3857         8

SCOTT T_28672                      4      3865         8

SCOTT T_28672                      4      3873         8

SCOTT T_28672                      4      3881         8

SCOTT T_28672                      4      3889         8

SCOTT T_28672                      4      3897         8

6 rows selected.

简单总结：

从压缩前后段的数量上看，压缩还是相当节约空间的，17个区压缩成6个区，2倍的压缩比。

2.2.3       dump出file 4 block 3860块

SQL> alter system dump datafile 4 block 3860;

System altered.

2.2.4       结构分析

需要说明的是，如下的内容为完整.trc的节选，有关于block dump内容更详细的解释可以参照“DSI402e Data Types and Block Structures”，本文只关注与压缩有关的部分。

2.2.4.1    data header部分

此部分是总结的部分，记录了块中有多少行的数据，列存储的顺序，记录表中记录的条数及重复记录的条数等信息。

data_block_dump,data header at 0x1103ff07c

===============

tsiz: 0x1f80

hsiz: 0x5b0

pbl: 0x1103ff07c

bdba: 0x01000f14

    76543210

flag=-0     *flag为0表示是压缩块

ntab=2     *ntab表述数据块中表的数量，普通表的数据块中这个值是1，而压缩表大于1

nrow=700  *nrow表示该数据块中的记录数，此项表明该块中含700条记录

frre=-1

fsbo=0x5b0

fseo=0x106a

avsp=0x2a

tosp=0x2a

            r0_9ir2=0x0                *9ir2表示的是这项压缩功能起始的版本

            mec_kdbh9ir2=0xe

                         76543210

            shcf_kdbh9ir2=----------

                     76543210

            flag_9ir2=--R---OC

                        fcls_9ir2[9]={ 0 32768 32768 32768 32768 32768 32768 32768 32768 }

                        perm_9ir2[8]={ 6 7 2 3 5 4 1 0 }*表示实际存储字段顺序也就是说，后面物理存储的顺序COL6,COL7,COL2...

                       

0x30:pti[0]         nrow=24                       offs=0      *记号表中非重复的记录中有24条

0x34:pti[1]         nrow=676         offs=24     *重复的记录有676行

 

0x38:pri[0]        offs=0x1f14

... ...

... ...

0x5ac:pri[698]   offs=0x106f

0x5ae:pri[699]    offs=0x106a

2.2.4.2    非重复的各条记录

共24行的数据，部分数据省略掉

block_row_dump:

tab 0, row 0, @0x1f14

tl: 31 fb: --H-FL-- lb: 0x0 cc: 8

*fb->flag byte H->header F->first L->last

*cc->column count(列数量)

col 0: [ 2] c1 15          *列长度为2,类型为c115

col 1: *NULL*

col 2: [ 5] 434c45 52 4b

col 3: [ 3] c2 50 03

col 4: [ 2] c2 09

col 5: [ 7] 77 b40c11 01 01 01

col 6: [ 3] c24a46

col 7: [ 5] 53 4d 49 54 48

bindmp: 00 30 08 17 ff 0e cb c2 50 03 ca c2 09 cf 77 b40c11 01 01 01 cb c24a46 cd 53 4d 49 54 48

                 *0030表示为0x0030转为十进制为48，表示这一压缩行供给48行使用

                 *08表示列的数量，即为8列

tab 0, row 1, @0x1ef5

tl: 31 fb: --H-FL-- lb: 0x0 cc: 8

col 0: [ 2] c1 15

col 1: *NULL*

col 2: [ 5] 434c45 52 4b

col 3: [ 3] c2 4e 59

col 4: [ 2] c20c

col 5: [ 7] 77 b7 010c01 01 01

col 6: [ 3] c24f4d

col 7: [ 5] 41 44 41 4d 53

bindmp: 00 30 08 17 ff 0e cb c2 4e 59 ca c20ccf 77 b7 010c01 01 01 cb c24f4d cd 41 44 41 4d 53

... ...

... ...

tab 0, row 23, @0x1f7b

tl: 5 fb: --H-FL-- lb: 0x0 cc: 1

col 0: [ 2] c1 15

2.2.4.3    重复的各条记录

相同的记录会有同样的指针，例如在本例中，row1与row 15，23，49指向的都是同一个位置。

bindmp: 00 05 ca c1 15      *指向记号表中记录的指针,相同的记录同样的指针

tab 1, row 0, @0x1d99

tl: 5 fb: --H-FL-- lb: 0x0 cc: 8

col 0: [ 2] c1 15

col 1: *NULL*

col 2: [ 5] 434c45 52 4b

col 3: [ 3] c2 50 03

col 4: [ 2] c2 09

col 5: [ 7] 77 b40c11 01 01 01

col 6: [ 3] c24a46

col 7: [ 5] 53 4d 49 54 48

bindmp:2c00 01 08 00

tab 1, row 1, @0x1d94

tl: 5 fb: --H-FL-- lb: 0x0 cc: 8

col 0: [ 2] c11f

col 1: [ 2] c2 04

col 2: [ 8] 53 414c45 53 4d 41 4e

col 3: [ 3] c2 4d 63

col 4: [ 2] c2 11

col 5: [ 7] 77 b5 02 14 01 01 01

col 6: [ 3] c2 4b 64

col 7: [ 5] 414c4c45 4e

bindmp:2c00 01 08 07

... ...

... ...

tab 1, row 675, @0x106a

tl: 5 fb: --H-FL-- lb: 0x0 cc: 8

col 0: [ 2] c1 0b

col 1: *NULL*

col 2: [ 5] 434c45 52 4b

col 3: [ 3] c2 4e 53

col 4: [ 2] c2 0e

col 5: [ 7] 77 b6 01 17 01 01 01

col 6: [ 3] c2 50 23

col 7: [ 6] 4d 494c4c45 52

bindmp:2c00 01 08 0b

end_of_block_dump

End dump data blocks tsn: 4 file#: 4 minblk 3860 maxblk 3860

3.     段压缩的优缺点、限制及性能

3.1    优点

节省存储空间：这也是ORACLE实现段数据压缩的目的所在，随着数据的海量，历史数据的不断的堆积，段压缩技术在节省空间的同时也节省了单位历史数据的存储成本。

性能会有提升：段压缩针对的都应该是历史数据，这样的数据的特点是海量、不变更一般只用于查询，这里所说的性能的提升是在CPU不是瓶颈的前提下，且是查询性能的提升。因为表段被压缩后其所占的数据块会大量的减少，单次的IO会读取更多的block，也因此会减少对buffer cache的占用。段压缩技术会提升IO的性能，如今CPU不断的加速，从双核到四核不断的加速，IBM p6的CPU也已经问世，也因此时间换空间的优化技术也越来越流行。

3.2    缺点及限制

会增加些额外的CPU负担：上面已经提到，ORACLE段压缩技术是时间换空间的一种体现，存储空间的减少势必会导致建立压缩段和查询数据时压缩及解压缩过程CPU的消耗。

表压缩不支持超过255列

以BasicFile（相对于11g推出的SecureFiles而言）方式存储的LOB型数据段在表压缩时是不会被压缩的，11g推出的SecureFiles支持LOB的压缩，这方面可以参照11g的联机文档。

ORACLE11g之前的版本只会在批量装载时对数据进行压缩：虽然在Oracle9i中引入了段压缩技术，但只能对批量装载操作（比如sql*loader直接路径装载，CTAS，insert加append提示等）涉及的数据进行压缩，普通的DML操作的数据是无法压缩的，这应该是对于写操作的压缩难题没有解决的缘故。

索引组织表及含有overflow段的表或分区表是不支持表压缩的。

不能显示的(explicitly)为hash分区及hash和list子分区定设定压缩选项，这些分区的压缩选项可以从表空间、表或分区的压缩属性继承过来。

外部表和是cluster组成部分的表是不支持压缩的。

段压缩技术适用于数据仓库这样的应用，一张表压缩后最好避免DML类型的操作，特别是update的操作，update操作会导致行迁移的发生，使得压缩表可能比原表占用的空间还大，所以切忌对压缩表做update操作，这样的技术应该用在合适的地方。

3.3    性能

很多人担心表压缩后性能会有所降低，3.1中已经谈到了查询的性能会有所提升。关于这一点wanghai做了充份的测试，测试表明不论是全表扫描还是通过索引回表扫描压缩表的性能都不会比非压缩表差。至于DML，这是不推荐的，如果非要执行这样的操作，非批量装载操作insert操作之外的普通的insert操作的数据是不进行压缩的，因此压缩表的与常规表的insert性能是一样的。Update操作压缩表时会引发迁移，在浪费空间的同时也会牺牲性能。

4.     压缩前如何得到表的压缩比？

4.1    压缩比定义

在要不要对一个表压缩时，很多DBA很迷惑，因为不知道这个表压缩后是不是会节约空间。通俗的说来就是，如果压缩完的表与没压缩时空间占用一样大就没有必要折腾了。如果在压缩前能知道一个表的压缩比就好了，Carl Dudley在一篇文章写一了个计算压缩比的函数，压缩比可以这样的定义：

压缩比=压缩前表中的block的数量/压缩后表中block的数量

4.2    计算压缩比函数

如下的函数以抽样的方式给出了计算压缩比的方法：

CREATE OR REPLACE FUNCTION COMPRESSION_RATIO(TABNAME VARCHAR2)

 RETURN NUMBER IS

 PCT    NUMBER:= 0.000099;      -- sample percentage

 BLKCNT NUMBER:= 0;        -- original block count (should be < 10K)

 BLKCNTC NUMBER;       -- compressed block count

BEGIN

 EXECUTE IMMEDIATE 'CREATE TABLE temp_uncompressed PCTFREE 0

                       AS SELECT * FROM ' || TABNAME || ' WHERE ROWNUM < 1';

 WHILE((PCT < 100)AND(BLKCNT < 1000)) LOOP

--取样1000个块

   EXECUTE IMMEDIATE 'TRUNCATE TABLE temp_uncompressed';

   EXECUTE IMMEDIATE 'INSERT INTO temp_uncompressed SELECT * FROM ' ||

                          TABNAME || ' SAMPLE BLOCK (' || PCT || ',10)';

   EXECUTE IMMEDIATE

'SELECT COUNT(DISTINCT(dbms_rowid.rowid_block_number(rowid)))

        FROM temp_uncompressed' INTO BLKCNT;

   PCT:=PCT*10;

 END LOOP;

 EXECUTE IMMEDIATE 'CREATE TABLE temp_compressed COMPRESS

                        AS SELECT * FROM temp_uncompressed';

 EXECUTE IMMEDIATE

'SELECT COUNT(DISTINCT(dbms_rowid.rowid_block_number(rowid)))

         FROM temp_compressed' INTO BLKCNTC;

 EXECUTE IMMEDIATE 'DROP TABLE temp_compressed';

 EXECUTE IMMEDIATE 'DROP TABLE temp_uncompressed';

 RETURN (BLKCNT/BLKCNTC);

END;

4.3    计算压缩比测试

SCOTT用户下有一张表为BIG_TABLE，如下以这张表做一下测试一下：

4.3.1       为方便调用建一过程

create or replace procedure COMPRESS_TEST(P_COMP VARCHAR2) is

 COMP_RATIO  NUMBER;

begin

 COMP_RATIO := ROUND(COMPRESSION_RATIO(P_COMP),2);

 DBMS_OUTPUT.PUT_LINE('Compression factor for table '||P_COMP||' is '||COMP_RATIO);

end COMPRESS_TEST;

4.3.2       调用测试

SQL>connect scott/tiger

Connected.

SQL>execute compress_test('BIG_TABLE');

Compression factorfortableBIG_TABLEis2.23

PL/SQLproceduresuccessfully completed.

5.     如何使用段压缩

5.1    创建一张压缩表，后续直接装载插入的数据会被压缩

语法：

CREATE TABLE COMPRESS;

eg：

SQL> create table T1 (col number) compress;

Table created.

5.2    压缩已有一张非分区表

ALTER TABLE COMPRESS;

只对表后续的数据有效，并无压缩原有的数据，压缩原有的数据需要执行MOVE COMPRESS操作。

语法：

ALTER TABLE move … COMPRESS;

eg ：

SQL> alter table T1 move compress;

Table altered.

5.3    压缩一张已有的分区表

Scott下有一张分区表，创建脚本如下：

SQL>CREATE TABLE range_sales

( prod_id       NUMBER(6),

 cust_id       NUMBER,

 time_id       DATE

)

PARTITION BY RANGE (time_id)

(PARTITION SALES_Q1_2007

VALUES LESS THAN (TO_DATE('01-APR-2007','DD-MON-YYYY')),

PARTITION SALES_Q2_2007

VALUES LESS THAN (TO_DATE('01-JUL-2007','DD-MON-YYYY')),

 PARTITION SALES_Q3_2007

VALUES LESS THAN (TO_DATE('01-OCT-2007','DD-MON-YYYY')),

 PARTITION SALES_Q4_2007

VALUES LESS THAN (TO_DATE('01-JAN-2008','DD-MON-YYYY'))

);

--创建索引

Create index IDX_RANGE_SALES_PROD_ID on RANGE_SALES (PROD_ID) LOCAL;

注意：分区表是不能整表做move compress操作的，需要一个分区一个分区的做。

分区表整表压缩会报错：

SQL> alter table range_sales move compress;

alter table range_sales move compress

           *

ERROR at line 1:

ORA-14511: cannot perform. operation on a partitioned object

可以单个分区做move compress操作，做move compress后因rowid发生了改变，需要把索引rebuild一下。

SQL> alter table range_sales move partition SALES_Q1_2007 compress;

Table altered.

查看一下索引的状态：

SQL> SELECT index_name,partition_name,status FROM User_Ind_Partitions WHERE status=’ UNUSABLE’;

INDEX_NAME               PARTITION_NAME STATUS

------------------------- --------------- ----------------

IDX_RANGE_SALES_PROD_ID  SALES_Q1_2007  UNUSABLE

 

--Rebuild一下UNUSABLE状态的索引

SQL> alter index IDX_RANGE_SALES_PROD_ID rebuild partition SALES_Q1_2007;

Index altered.

5.4    压缩索引段

索引中的数据列重复项比较多的时候，可以考虑进行索引压缩，一般说来复合索引更适合索引压缩，复合索引前辍字段的重复列越多则压缩比越大。

语法：

--创建

create index on (col1,col2 ….coln) compress [n];

--变更

Alter index rebuild compress [n];

--其中n是可选的，即压缩索引的前n列，如果不指定则压缩所有的列。

这里需要特殊说明的是分区索引的压缩，分区索引的单个分区是不能通过rebuild compress方式进行压缩的，需要drop掉整个索引，然后创建时加compress选项。

SQL> alter index IDX_RANGE_SALES_PROD_ID rebuild partition SALES_Q1_2007 compress;

alter index IDX_RANGE_SALES_PROD_ID rebuild partition SALES_Q1_2007 compress

*

ERROR at line 1:

ORA-28659: COMPRESS must be specified at object level first

 

SQL> drop index IDX_RANGE_SALES_PROD_ID;

Index dropped.

 

SQL> create index IDX_RANGE_SALES_PROD_ID on RANGE_SALES (PROD_ID) LOCALCOMPRESS;

Index created.

6.     加大压缩比的方法

常常会有人问，怎样才能加大一个表的压缩比，节约更多的空间呢？明析了ORACLE段压缩技术的原理，回答这样的问题是很容易的。如果一个块中相同的数据越多，压缩比也就越大，因此对表中的数据进行排序后压缩可以达到这样的目的。问题又来了，表中的列如果很多，选取哪一更进行排序呢？在实际的测试中总结起来，一个列的distinct值的数据与表压缩后占的block相近时，此时压缩比是最优的，选取哪一列在实际工作中是需要测试的。

还以表T_28672为例，含28672条数据，已压缩，其中EMPNO有14个不同的值，COMM列含有4个不同的值。

SQL> create table T_28672_EMPNO compress

 2   as select * from T_28672 order by EMPNO;

Table created.

 

SQL> create table T_28672_COMM compress

 2   as select * from T_28672 order by COMM;

Table created.

 

SQL> execute show_space('T_28672');

Unformatted Blocks .....................              0

FS1 Blocks (0-25) ......................               0

FS2 Blocks (25-50) .....................              0

FS3 Blocks (50-75) .....................              0

FS4 Blocks (75-100).....................              0

Full Blocks ............................             43

Total Blocks............................             48

Total Bytes.............................        393,216

Total MBytes............................              0

Unused Blocks...........................              0

Unused Bytes............................              0

Last Used Ext FileId....................              4

Last Used Ext BlockId...................          3,897

Last Used Block.........................              8

PL/SQL procedure successfully completed.

 

SQL> execute show_space('T_28672_EMPNO);

ERROR:

ORA-01756: quoted string not properly terminated

 

SQL> execute show_space('T_28672_EMPNO');

Unformatted Blocks .....................              0

FS1 Blocks (0-25) ......................              0

FS2 Blocks (25-50) .....................              0

FS3 Blocks (50-75) .....................              0

FS4 Blocks (75-100).....................              0

Full Blocks ............................             40

Total Blocks............................             48

Total Bytes.............................        393,216

Total MBytes............................              0

Unused Blocks...........................              3

Unused Bytes............................         24,576

Last Used Ext FileId....................              4

Last Used Ext BlockId...................          3,769

Last Used Block.........................              5

PL/SQL procedure successfully completed.

 

SQL> execute show_space('T_28672_COMM');

Unformatted Blocks .....................              0

FS1 Blocks (0-25) ......................              0

FS2 Blocks (25-50) .....................              0

FS3 Blocks (50-75) .....................              0

FS4 Blocks (75-100).....................              0

Full Blocks ............................             41

Total Blocks............................             48

Total Bytes.............................        393,216

Total MBytes............................              0

Unused Blocks...........................              2

Unused Bytes............................         16,384

Last Used Ext FileId....................              4

Last Used Ext BlockId...................          3,817

Last Used Block.........................              6

PL/SQL procedure successfully completed.

 

从测试中可见表压缩后占用48个块，以有14个不同的值的empno列(最接近48)还是最节约空间的，Unused Bytes项最大，值为：24,576。

7.     ORACLE11g压缩方面的增强

ORACLE公司在11g版本中关于compress做了好多的增强和改善，可以看出ORACLE公司在这方面的信心和决心，可以预见，compress会更加广泛的应用在数据库的应用中。11g关于压缩方面的增强简单的总结如下：

7.1    支持压缩表普通insert数据的压缩

上面已经提到，11g之前的版本只能对批量装载操作（比如sql*loader直接路径装载，CTAS，insert加append提示等）涉及的数据进行压缩，普通的DML操作的数据是无法压缩的。在11g中表压缩有三个选项，1COMPRESS2COMPRESS FOR DIRECT_LOAD OPERATIONS 3 COMPRESS FOR ALL OPERATIONS，当使用COMPRESS FOR ALL OPERATIONS时，ORACLE会对表后续所有的DML操作进行压缩。

7.2    SecureFile

SecureFile用于存储非结构化数据，能够完成用此方式存储的非结构化数据的压缩、加密、优化等功能。LOB类型的数据以此方式存储且压缩时会更大的节约空间。例：

SQL> CREATE TABLE T_BLOB

 2 (

 3   IMAGE_ID  NUMBER,

 4   FILE_NAME VARCHAR2(30),

 5   IMAGE_DATA BLOB

 6 )

 7 LOB (IMAGE_DATA) STORE AS SECUREFILE ( DEDUPLICATE LOB CACHE NOLOGGING )

 8 TABLESPACE USERS;

Table created

7.3    备份、容灾方面的压缩增强

       Rman压缩备份算法的增强，这本应该也是Rman该具有的功能，直到10g的时候才出现，通过RMAN>CONFIGURE COMPRESSION ALGORITHM ;命令进行设置，10g采用的压缩算法是BZIP2，11g采用了更先进的ZLIB算法，提高了压缩的处理速度。特别是利用网络备份，如果网络带宽还有问题时，Rman的压缩备份可以很大的减少网络传输的备份数据量，提升备份的性能。

       DG压缩传送日志，有很多公司的异地容灾通过DG来完成，如果数据库的事务特别的繁忙，产生的日志量就会很多，再加之主中心与容灾中心的带宽有问题时，日志的传送的及时性就会很有问题。在11g之前的版本，这一工作一般是由DBA与程序员合作一起完成的，日志被先压缩掉，然后再传送->解压缩->apply掉。11g中的这个新功能使DBA又变得轻松多了。设置方式：

       LOG_ARCHIVE_DEST_n='SERVICE=...COMPRESSION={ENABLE|DISABLE}'

8.     参考文档：

1 eygle ：http://www.eygle.com/archives/2006/06/oracle9ir2_nf_table_compress.html

2 otn   ：http://www.oracle.com/technology/oramag/oracle/04-mar/o24tech_data.html

3 wanghai：http://wzwanghai.spaces.live.com/blog/cns!56626E237AFBD116!206.entry

4 biti   ：http://www.itpub.net/showthread.php?threadid=197403

5 fuyuncat：http://www.hellodba.com/Doc/data_compress.htm

 

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

转载于:http://blog.itpub.net/12615085/viewspace-705551/