4压缩前如何得到表的压缩比？
压缩比定义
在要不要对一个表压缩时，很多DBA很迷惑，因为不知道这个表压缩后是不是会节约空间。通俗的说来就是，如果压缩完的表与没压缩时空间占用一样大就没有必要折腾了。如果在压缩前能知道一个表的压缩比就好了，Carl Dudley在一篇文章写一了个计算压缩比的函数，压缩比可以这样的定义：
压缩比=压缩前表中的block的数量/压缩后表中block的数量
[@more@]计算压缩比函数
如下的函数以抽样的方式给出了计算压缩比的方法：
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; |
计算压缩比测试
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如何使用段压缩
创建一张压缩表，后续直接装载插入的数据会被压缩
语法：
CREATE TABLE eg： SQL> create table T1 (col number) compress 2 / Table created. |
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 |
备份、容灾方面的压缩增强
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. |
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. |
压缩索引段
索引中的数据列重复项比较多的时候，可以考虑进行索引压缩，一般说来复合索引更适合索引压缩，复合索引前辍字段的重复列越多则压缩比越大。
语法： --创建 create index --变更 Alter index 其中n是可选的，即压缩索引的前n列，如果不指定则压缩所有的列。 |
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博客 ” ，链接：https://blog.itpub.net/22630256/viewspace-1032924/ ，如需转载，请注明出处，否则将追究法律责任。
