玉面飞龙的BLOG

Oracle9iR2引入compress table功能,作为VLDB的增强。

企业可以将那些占用宝贵磁盘空间且很少访问的历史数据移到廉价的磁盘空间并且进行压缩,且压缩后查询性能也不会下降很多。

alter table table_name move compress ;

这也体现一个设计策略。OLTP上使用分区表存储纪录,定期将历史分区数据exchange出来,TTS到历史数据库进行压缩存储。

一个观察压缩比例的脚本,

select table_name,compression from dba_tables where TABLE_NAME=’TEST’;

set db_file_multiple_read…pct_free 0

alter table table_name move compress tablespace nologging parallel N;

rebuild invalid indexes (we can also compress rebuild)

select table_name,compression from dba_tables where TABLE_NAME=’TEST’;

SQL> create or replace function compression_ratio (tabname varchar2)
2  return number is
3  pct number := 0.000099;
4  blkcnt number := 0;
5  blkcntc number;
6  begin
7  execute immediate ‘ create table TEMP$$FOR_TEST tablespace USERS01 pctfree 0 as select * from ‘||tabname||’ where rownum < 1′;
8  while ((pct < 100) and (blkcnt < 1000)) loop
9  execute immediate ‘truncate table TEMP$$FOR_TEST’;
10  execute immediate ‘insert into TEMP$$FOR_TEST select * from  ‘||tabname||’ sample block (’|| pct ||’,10)’;
11  execute immediate ’select count(distinct(dbms_rowid.rowid_block_number(rowid))) from TEMP$$FOR_TEST’ into blkcnt;
12  pct := pct * 10;
13  end loop;
14  execute immediate ‘alter table TEMP$$FOR_TEST move compress ‘;
15  execute immediate ’select count(distinct(dbms_rowid.rowid_block_number(rowid))) from TEMP$$FOR_TEST’ into blkcntc;
16  execute immediate ‘drop table TEMP$$FOR_TEST’;
17  return (blkcnt/blkcntc);
18  end;
19  /

Function created.

SQL> set serveroutput on
SQL> declare
2    a number;
3    begin
4    a:=compression_ratio(’TEST’);
5    dbms_output.put_line(a);
6    end;
7  /
5.70655270655270655270655270655270655271

PL/SQL procedure successfully completed.