ORACLE的数据段压缩技术 part 2

接上篇： ORACLE的数据段压缩技术 part 1[@more@]

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;

4.3.2 调用测试

SQL> connect scott/tiger Connected. SQL> execute compress_test('BIG_TABLE'); Compression factor for table BIG_TABLE is 2.23 PL/SQL procedure successfully completed. SQL>

5 如何使用段压缩

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

语法：

CREATE TABLE ... COMPRESS; eg ： SQL> create table T1 (col number) compress 2 / 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/Recreate indexes 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 SQL> 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) LOCAL COMPRESS; 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. SQL>

从测试中可见表压缩后占用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中表压缩有三个选项，1COMPRESS 2COMPRESS FOR DIRECT_LOAD OPERATIONS3 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 9 / 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/717880/viewspace-999786/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/717880/viewspace-999786/