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> |
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22630256/viewspace-1032924/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/22630256/viewspace-1032924/