part2

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

4.1压缩比定义

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

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

[@more@]

4.2计算压缩比函数

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

CREATEORREPLACEFUNCTIONCOMPRESSION_RATIO(TABNAMEVARCHAR2) RETURNNUMBERIS PCT NUMBER:= 0.000099;-- sample percentage BLKCNT NUMBER:= 0;-- original block count (should be < 10K) BLKCNTCNUMBER;-- compressed block count BEGIN EXECUTEIMMEDIATE' CREATE TABLE temp_uncompressed PCTFREE 0 AS SELECT * FROM ' || TABNAME || ' WHERE ROWNUM < 1'; WHILE((PCT < 100)AND(BLKCNT < 1000))LOOP --取样1000个块 EXECUTEIMMEDIATE'TRUNCATE TABLE temp_uncompressed'; EXECUTEIMMEDIATE'INSERT INTO temp_uncompressed SELECT * FROM ' || TABNAME || ' SAMPLE BLOCK (' || PCT || ',10)'; EXECUTEIMMEDIATE'SELECT COUNT(DISTINCT(dbms_rowid.rowid_block_number(rowid))) FROM temp_uncompressed' INTOBLKCNT; PCT := PCT * 10; ENDLOOP; EXECUTEIMMEDIATE'CREATE TABLE temp_compressed COMPRESS AS SELECT * FROM temp_uncompressed'; EXECUTEIMMEDIATE'SELECT COUNT(DISTINCT(dbms_rowid.rowid_block_number(rowid))) FROM temp_compressed' INTOBLKCNTC; EXECUTEIMMEDIATE'DROP TABLE temp_compressed'; EXECUTEIMMEDIATE'DROP TABLE temp_uncompressed'; RETURN(BLKCNT / BLKCNTC); END;

4.3计算压缩比测试

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

4.3.1为方便调用建一过程

CREATEORREPLACEPROCEDURECOMPRESS_TEST(P_COMPVARCHAR2)IS COMP_RATIONUMBER; 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>connectscott/tiger Connected. SQL>executecompress_test('BIG_TABLE'); Compression factorfortableBIG_TABLEis2.23 PL/SQLproceduresuccessfully completed. SQL>

5如何使用段压缩

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

语法：

CREATE TABLE eg： SQL> create table T1 (col number) compress 2 / Table created.

5.2压缩已有一张非分区表

Alter table 只对表后续的数据有效，并无压缩原有的数据，压缩原有的数据需要执行move compress操作。

语法：

7 11g压缩方面的增强ORACLE

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

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

上面已经提到，11g之前的版本只能对批量装载操作（比如sql*loader直接路径装载，CTAS，insert加append提示等）涉及的数据进行压缩，普通的DML操作的数据是无法压缩的。在11g中表压缩有三个选项，1COMPRESS2COMPRESS FOR DIRECT_LOAD OPERATIONS3COMPRESS 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}'

ALTER TABLE eg ： SQL> alter table T1 move compress; Table altered.

5.3压缩一张已有的分区表

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

SQL>CREATETABLErange_sales ( prod_id NUMBER(6), cust_id NUMBER, time_id DATE ) PARTITIONBYRANGE(time_id) ( PARTITIONSALES_Q1_2007VALUESLESSTHAN(TO_DATE('01-APR-2007','DD-MON-YYYY')), PARTITIONSALES_Q2_2007VALUESLESSTHAN(TO_DATE('01-JUL-2007','DD-MON-YYYY')), PARTITIONSALES_Q3_2007VALUESLESSTHAN(TO_DATE('01-OCT-2007','DD-MON-YYYY')), PARTITIONSALES_Q4_2007VALUESLESSTHAN(TO_DATE('01-JAN-2008','DD-MON-YYYY')) ) ; -- Create/Recreate indexes createindexIDX_RANGE_SALES_PROD_IDonRANGE_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 --变更 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. SQL>

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

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

转载于:http://blog.itpub.net/22630256/viewspace-1032924/